用户相关的自动化脚本

1。User创建语句

SET verify off;
SET termout off;
SET feedback off;
SET echo off;
SET pagesize 0;
SET timeing off;
SET termout on
SELECT 'Creating user build script...' FROM DUAL;
SET termout off;
CREATE TABLE usr_temp( lineno NUMBER,usr_name VARCHAR2(30),text VARCHAR2(80))
/
DECLARE
CURSOR usr_cursor
IS
SELECT username, PASSWORD, default_tablespace, temporary_tablespace, PROFILE
FROM SYS.dba_users
WHERE username != 'SYS' AND username != 'SYSTEM'
ORDER BY username;
CURSOR qta_cursor (c_usr VARCHAR2)
IS
SELECT tablespace_name, max_bytes
FROM SYS.dba_ts_quotas
WHERE username = c_usr;
lv_username SYS.dba_users.username%TYPE;
lv_password SYS.dba_users.PASSWORD%TYPE;
lv_default_tablespace SYS.dba_users.default_tablespace%TYPE;
lv_temporary_tablespace SYS.dba_users.default_tablespace%TYPE;
lv_profile SYS.dba_users.PROFILE%TYPE;
lv_tablespace_name SYS.dba_ts_quotas.tablespace_name%TYPE;
lv_max_bytes SYS.dba_ts_quotas.max_bytes%TYPE;
lv_string VARCHAR2 (80);
lv_lineno NUMBER := 0;
PROCEDURE write_out (p_line INTEGER, p_name VARCHAR2, p_string VARCHAR2)
IS
BEGIN
INSERT INTO usr_temp (lineno, usr_name, text)
VALUES (p_line, p_name, p_string);
END;
BEGIN
OPEN usr_cursor;
LOOP
FETCH usr_cursor
INTO lv_username, lv_password, lv_default_tablespace, lv_temporary_tablespace, lv_profile;
EXIT WHEN usr_cursor%NOTFOUND;
lv_lineno := 1;
lv_string := ('CREATE USER ' || LOWER (lv_username));
write_out (lv_lineno, lv_username, lv_string);
lv_lineno := lv_lineno + 1;
IF lv_password IS NULL
THEN
lv_string := 'IDENTIFIED EXTERNALLY';
ELSE
lv_string := ('IDENTIFIED BY VALUES ''' || lv_password || '''');
END IF;
write_out (lv_lineno, lv_username, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := 'DEFAULT TABLESPACE ' || lv_default_tablespace;
write_out (lv_lineno, lv_username, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := 'TEMPORARY TABLESPACE ' || lv_temporary_tablespace;
write_out (lv_lineno, lv_username, lv_string);
lv_lineno := lv_lineno + 1;
OPEN qta_cursor (lv_username);
LOOP
FETCH qta_cursor
INTO lv_tablespace_name, lv_max_bytes;
EXIT WHEN qta_cursor%NOTFOUND;
lv_lineno := lv_lineno + 1;
IF lv_max_bytes IS NULL
THEN
lv_string := 'QUOTA UNLIMITED ON ' || lv_tablespace_name;
ELSE
lv_string :=
'QUOTA ' || lv_max_bytes || ' ON ' || lv_tablespace_name;
END IF;
write_out (lv_lineno, lv_username, lv_string);
END LOOP;
CLOSE qta_cursor;
lv_string := ('PROFILE ' || lv_profile || ';');
write_out (lv_lineno, lv_username, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := ' ';
write_out (lv_lineno, lv_username, lv_string);
END LOOP;
CLOSE usr_cursor;
END;
/
SPOOL create_users.sql
SET heading off
SET recsep off
COL test format a80 word_wrap
SELECT text
FROM usr_temp
ORDER BY usr_name, lineno;
SPOOL off;
DROP TABLE usr_temp;
EXIT
/
----------------------------------------------------------------------------------------------
2。 User授权语句

undefine user_name
set pagesize 1000
select 'grant '||tt.granted_role||' to '||tt.grantee||';' as SQL_text
from dba_role_privs tt where tt.grantee=(upper('&&user_name'))
union all
select 'grant '||tt.privilege||' to '||tt.grantee||';'
from dba_sys_privs tt where tt.grantee=(upper('&&user_name'))
union all
select 'revoke UNLIMITED TABLESPACE from '||upper('&&user_name')||';' from dual
union all
select 'grant '||tt.privilege||' on '||owner||'.'||table_name||' to '||tt.grantee||';'
from dba_tab_privs tt where tt.grantee=(upper('&&user_name'))
union all
select 'grant '||case
when c='TRUETRUE' then 'SYSDBA, SYSOPER'
when c='TRUEFALSE' then 'SYSDBA'
when c='FALSETRUE' then 'SYSOPER'
end|| ' to '||upper('&&user_name')||';'
from
(select sysdba||sysoper c from v$pwfile_users
where username=upper('&&user_name'))
union all
select 'alter user '||tt.user_name||' quota '||
case
when maxblocks*blocksize<0 then 'unlimited'
else to_char(maxblocks*blocksize)
end||' on '||ts_name||';'
from KU$_TSQUOTA_VIEW tt where tt.user_name=(upper('&&user_name'));
或者
set serveroutput on size 1000000
set verify off
undefine user_name
declare
 v_name varchar2(30) := upper('&user_name');
 no_grant exception;
 pragma exception_init( no_grant, -31608 );
begin
 dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
 dbms_output.enable(1000000);
 dbms_output.put_line(dbms_metadata.get_ddl('USER',v_name));
 begin
   dbms_output.put_line(dbms_metadata.get_granted_ddl('SYSTEM_GRANT',v_name));
 exception
   when no_grant then dbms_output.put_line('-- No system privs granted');
 end;
 begin
   dbms_output.put_line(dbms_metadata.get_granted_ddl('ROLE_GRANT',v_name));
 exception
   when no_grant then dbms_output.put_line('-- No role privs granted');
 end;
 begin
   dbms_output.put_line(dbms_metadata.get_granted_ddl('OBJECT_GRANT',v_name));
 exception
   when no_grant then dbms_output.put_line('-- No object privs granted');
 end;
 begin
  dbms_output.put_line(dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA',v_name));
 exception
   when no_grant then dbms_output.put_line('-- No tablespace quota specified');
 end;
 dbms_output.put_line(dbms_metadata.get_granted_ddl('DEFAULT_ROLE', v_name ));
exception
 when others then
  if SQLCODE = -31603 then dbms_output.put_line('-- User does not exists');
  else raise;
  end if;
end;
/