如何对oracle中除默认用户帐户外的所有架构及其分配的角色进行对象备份权限

Question

基本上在TEST环境中，如何在数据库从PROD刷新到TEST之前，对权限和进行备份，生成用户拥有的所有对象的grant语句，oracle中的默认值除外。

因此，我们可以在刷新后恢复到这些特权，以避免出现任何被拒绝的权限问题。

并且也在寻找">dbms_metadata.get_dependent_ddl"以及">dbms_metadata.get_granted_ddl"包裹因为它需要大量的时间并生成许多重复的语句。

Answer 1

生成除默认帐户之外的所有模式的Grant Code语句脚本

set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
spool file_name.sql
select 'grant '||granted_role||' to '||grantee||';' from DBA_ROLE_PRIVS where grantee in (SELECT usr.username FROM sys.dba_users usr WHERE usr.created > (SELECT created FROM sys.v_$database))
union all
select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';' from DBA_TAB_PRIVS where grantee in (SELECT usr.username FROM sys.dba_users usr WHERE usr.created > (SELECT created FROM sys.v_$database))
union all
select 'grant '||privilege||' to '||grantee||';' from DBA_SYS_PRIVS where grantee in (SELECT usr.username FROM sys.dba_users usr WHERE usr.created > (SELECT created FROM sys.v_$database))
union all
select 'grant '||privilege||' ('||column_name||') '||' on '||owner||'.'||table_name||' to '||grantee||';' from DBA_COL_PRIVS where grantee in (SELECT usr.username FROM sys.dba_users usr WHERE usr.created > (SELECT created FROM sys.v_$database));
spool off

检查已分配角色的列表。

select granted_role from DBA_ROLE_PRIVS where grantee in (SELECT usr.username FROM sys.dba_users usr WHERE usr.created > (SELECT created FROM sys.v_$database));

生成角色的授予权限语句。

set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
spool file_name.sql
select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';' from DBA_TAB_PRIVS where grantee in (select granted_role from DBA_ROLE_PRIVS where grantee in (SELECT usr.username FROM sys.dba_users usr WHERE usr.created > (SELECT created FROM sys.v_$database)));
spool off