You Asked

When performing a duplicate database from prod to dev monthly. We have a script that captures the current user permissions on dev via the dbms package that generates DDL to another file to be used after the duplication. Thus preserving the permissions we have on dev.

We then run the duplicate and afterwards run the DDL in the file. The issue is the DDL in the file does not revoke the new permissions that were generated from prod to dev. We want to revoke a specific role that was in prod that's now in dev from certain users (not all users). We can add the statements to the script for each specific user but when new users are added or removed who don't need the role, we have to keep updating the script.

How can we code this using without using PLSQL (if possible) where it checks if a user has the role, if so, it will generate the revoke statements to automatically revoke the role from the user?

and Connor said...

Before you start, just augment your capture script to create a revoke statement for every existing role/priv on production and store that in the file too.

So something like:

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.

followed by your existing re-grant script

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Kel Bell, May 19, 2017 - 8:50 pm UTC

Thank you very much. However, I don't quite understand. There are a few hundred users in the database, we just need to revoke one specific role from about 75 users. But we need to identify those users that have that one role, then generate the revoke statement. How can we do that please?

May 19, 2017 - 10:38 pm UTC

You can see that from my answer

DBA_ROLE_PRIVS
- granted_role
- grantee

Kel Bell, May 20, 2017 - 12:26 am UTC

Thank you again. However, in your statement you're entering a user's id. Are you saying I need to create the script you have and manually enter every users id first (as you did with 'MCDONAC')?

May 23, 2017 - 2:00 am UTC

No, that was an *example*...something from which you can *base* your scripts.

It was just the illustrate the *premise*

- revoke existing
- grant new

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs

Automating removal of security after duplicate

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs