Oracle 复制后自动移除安全性

每月执行从prod到dev的重复数据库时。我们有一个脚本，该脚本通过dbms包捕获dev上的当前用户权限，该包将DDL生成到复制后要使用的另一个文件。从而保留我们对dev的权限。

然后，我们运行副本，然后在文件中运行DDL。问题是文件中的DDL不会撤销从prod到dev生成的新权限。我们想从某些用户 (不是所有用户) 撤销prod中现在在dev中的特定角色。我们可以为每个特定用户将语句添加到脚本中，但是当添加或删除不需要角色的新用户时，我们必须不断更新脚本。

我们如何在不使用PLSQL (如果可能) 的情况下使用此代码，它会检查用户是否具有角色，如果是，它将生成revoke语句以自动从用户撤销角色？

在开始之前，只需增强捕获脚本即可为生产中的每个现有角色/priv创建revoke语句，并将其存储在文件中。

所以像这样:

SQL> select 'revoke '||granted_role||' from '||grantee from dba_role_privs where grantee = 'MCDONAC'
  2  union all
  3  select 'revoke '||privilege||' from '||grantee from dba_sys_privs where grantee = 'MCDONAC'
  4  union all
  5  select 'revoke '||t.privilege||' on '||decode(o.object_type,'DIRECTORY','DIRECTORY')||' '||
  6          t.owner||'.'||t.table_name||' from '||grantee
  7  from dba_tab_privs t, dba_objects o
  8  where t.grantee = 'MCDONAC'
  9  and o.owner = t.owner
 10  and o.object_name = t.table_name;

'REVOKE'||GRANTED_ROLE||'FROM'||GRANTEE
-------------------------------------------------------------------------------------------------------
revoke DBA from MCDONAC
revoke SELECT ANY DICTIONARY from MCDONAC
revoke EXECUTE ANY PROCEDURE from MCDONAC
revoke SELECT ANY TABLE from MCDONAC
revoke UNLIMITED TABLESPACE from MCDONAC
revoke CREATE TABLE from MCDONAC
revoke EXECUTE on  SYS.DBMS_LOCK from MCDONAC
revoke EXECUTE on  SYS.DBMS_LOCK from MCDONAC
revoke EXECUTE on  SYS.SYS_PLSQL_FAA5F685_2385_1 from MCDONAC
revoke EXECUTE on  SYS.DBMS_CRYPTO from MCDONAC
revoke EXECUTE on  SYS.DBMS_CRYPTO from MCDONAC
revoke EXECUTE on  SYS.DBMS_PROFILER from MCDONAC
revoke EXECUTE on  SYS.DBMS_PROFILER from MCDONAC
revoke EXECUTE on  SYS.SYS_PLSQL_D9B1149D_9_1 from MCDONAC
revoke EXECUTE on  SYS.DBMS_SYSTEM from MCDONAC
revoke EXECUTE on  SYS.DBMS_SYSTEM from MCDONAC
revoke EXECUTE on DIRECTORY SYS.TEMP from MCDONAC
revoke EXECUTE on DIRECTORY SYS.IMAGES_DIR from MCDONAC
revoke EXECUTE on DIRECTORY SYS.ASKTOM from MCDONAC
revoke EXECUTE on DIRECTORY SYS.BIN from MCDONAC
revoke READ on DIRECTORY SYS.TEMP from MCDONAC
revoke READ on DIRECTORY SYS.IMAGES_DIR from MCDONAC
revoke READ on DIRECTORY SYS.ASKTOM from MCDONAC
revoke READ on DIRECTORY SYS.BIN from MCDONAC
revoke WRITE on DIRECTORY SYS.TEMP from MCDONAC
revoke WRITE on DIRECTORY SYS.IMAGES_DIR from MCDONAC
revoke WRITE on DIRECTORY SYS.ASKTOM from MCDONAC
revoke WRITE on DIRECTORY SYS.BIN from MCDONAC

28 rows selected.

复制

后面是您现有的重新授予脚本