SQL QUERY TO BACKUP GRANTS SCRIPT FROM TABLE ORACLE

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