SQL QUERY TO BACKUP GRANTS SCRIPT FROM TABLE ORACLE

It is always important to keep backup of grants awarded to an user before any changes done to the object like table drop or table recreate

SQL> select 'grant '||PRIVILEGE||' on '||owner||'.'||table_name|| ' to '||grantee|| ';' from dba_tab_privs where owner='TEST' and table_name='SALES_TAB';

'GRANT'||PRIVILEGE||'ON'||OWNER||'.'||TABLE_NAME||'TO'||GRANTEE||';'
--------------------------------------------------------------------------------
grant DELETE on TEST.SALES_TAB to TEST1;
grant INSERT on TEST.SALES_TAB to TEST1;
grant SELECT on TEST.SALES_TAB to TEST1;
grant UPDATE on TEST.SALES_TAB to TEST1;

Below query is another way to get the grant commands using dbms_metadata package

SQL> select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','TEST1') as ddl_GRANT from dual
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT','TEST1') from dual
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT','TEST1') from dual;

DDL_GRANT
--------------------------------------------------------------------------------

  GRANT SELECT ANY TABLE TO "TEST1"
  GRANT CREATE ANY TABLE TO "TEST1"
  GRANT


  GRANT UPDATE ON "TEST"."SALES_TAB" TO "TEST1"
  GRANT INSERT ON "TEST"."SALES


   GRANT "CONNECT" TO "TEST1"

DDL_GRANT
--------------------------------------------------------------------------------
   GRANT "RESOURCE" TO "TEST1"
   GRANT "DBA" TO

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s