oracle 收集用户权限脚本参考
SQL> drop table t_tmp_user_lhr; SQL> create table t_tmp_user_lhr( id number, username varchar2(50), exec_sql varchar2(4000),create_type varchar2(20)); SQL> DROP sequence s_t_tmp_user_lhr; SQL> create sequence s_t_tmp_user_lhr; SQL> begin for cur in (SELECT d.username, d.default_tablespace, d.account_status, 'create user ' || d.username || ' identified by ' || d.username || ' default tablespace ' || d.default_tablespace || ' TEMPORARY TABLESPACE ' || D.temporary_tablespace || ';' CREATE_USER, replace(to_char(DBMS_METADATA.GET_DDL('USER', D.username)), chr(10), '') create_USER1 FROM dba_users d WHERE d.username in ('业务用户名')) loop INSERT INTO t_tmp_user_lhr (id, username, exec_sql, create_type) values (s_t_tmp_user_lhr.nextval, cur.username, cur.CREATE_USER, 'USER'); INSERT INTO t_tmp_user_lhr (id, username, exec_sql, create_type) SELECT s_t_tmp_user_lhr.nextval, cur.username, CASE WHEN D.ADMIN_OPTION = 'YES' THEN 'GRANT ' || d.privilege || ' TO ' || d.GRANTEE || ' WITH GRANT OPTION ;' ELSE 'GRANT ' || d.privilege || ' TO ' || d.GRANTEE || ';' END priv, 'DBA_SYS_PRIVS' FROM dba_sys_privs d WHERE D.GRANTEE = CUR.USERNAME; INSERT INTO t_tmp_user_lhr (id, username, exec_sql, create_type) SELECT s_t_tmp_user_lhr.nextval, cur.username, CASE WHEN D.ADMIN_OPTION = 'YES' THEN 'GRANT ' || d.GRANTED_ROLE || ' TO ' || d.GRANTEE || ' WITH GRANT OPTION;' ELSE 'GRANT ' || d.GRANTED_ROLE || ' TO ' || d.GRANTEE || ';' END priv, 'DBA_ROLE_PRIVS' FROM DBA_ROLE_PRIVS d WHERE D.GRANTEE = CUR.USERNAME; INSERT INTO t_tmp_user_lhr (id, username, exec_sql, create_type) SELECT s_t_tmp_user_lhr.nextval, cur.username, CASE WHEN d.grantable = 'YES' THEN 'GRANT ' || d.privilege || ' ON ' || d.owner || '.' || d.table_name || ' TO ' || d.GRANTEE || ' WITH GRANT OPTION ;' ELSE 'GRANT ' || d.privilege || ' ON ' || d.owner || '.' || d.table_name || ' TO ' || d.GRANTEE || ';' END priv, 'DBA_TAB_PRIVS' FROM DBA_TAB_PRIVS d WHERE D.GRANTEE = CUR.USERNAME; end loop; COMMIT; end; / SQL> SELECT * FROM t_tmp_user_lhr; SQL> SELECT id,username,CREATE_TYPE,EXEC_SQL FROM t_tmp_user_lhr where CREATE_TYPE not in ('USER');
本文来自博客园,作者:蚌壳里夜有多长,转载请注明原文链接:https://www.cnblogs.com/dbahrz/p/17405906.html