0

i want to copy table privileges dba_sys_privs in table A to new table B. i have create table B. What left now was it's table privileges'. Kindly advice. Thanks

1
  • 1
    You don't "copy" privileges; you GRANT them using the corresponding command.
    – mustaccio
    Commented Jan 31 at 12:16

1 Answer 1

0

There is no built-in privilege copy utility, but it's easy enough to make your own with some simple scripting. Simply build your own DDL by querying the three main privilege views. You can then add whatever filters you need (copy a specific table or user) and replace the grantee or object names in the DDL expression with whatever you want to substitute it with. Take the output of such a query, inspect it to check that it makes sense and is what you expect, and execute it.

Basic queries:

SELECT 'grant '||privilege||' to "'||grantee||'"'||decode(admin_option,'YES',' with admin option')||';' ddl
  FROM dba_sys_privs
/  
SELECT 'grant '||privilege||' on "'||owner||'"."'||table_name||'" to "'||grantee||'"'||decode(grantable,'YES',' with grant option')||';' ddl
  FROM dba_tab_privs
/
SELECT 'grant '||granted_role||' to "'||grantee||'"'||decode(admin_option,'YES',' with admin option')||';' ddl
  FROM dba_role_privs
/

So to get the table-privs on one table and apply them to another:

SELECT 'grant '||privilege||' on NEWOWNER.NEWTABLE to "'||grantee||'"'||decode(grantable,'YES',' with grant option')||';' ddl
  FROM dba_tab_privs
 WHERE owner = 'OLDOWNER'
   AND table_name = 'OLDTABLE';

And execute the results.

This can of course be proceduralized to do the substitution and execution for you (using EXECUTE IMMEDIATE) but simply querying the DDL and running it manually is quick enough for most purposes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.