Question: How do I extract all of my
user grants? I plan to delete and re-add a schema and
I want to save a copy of all of my grants.
Answer: To "punch off" and save all security
grants to users, I recommend using th
dbms_metadata package. As you know, there
are three types of grants:
1)
system grants
2) object grants
3) role grants
Also,
beware that data access many also be controlled by
virtual private database security and "grant execute"
security" commands, so saving all f the grants may not
capture all of the possible security for Oracle.
The
following script will invoke dbms_metadata to save all
grants within a schema.
Please see
the
Oracle script
collection for a complete set of Oracle security
scripts.
To use this
script, just enter the schema owner (username) into the
following code to display the grant statements for that
schema owner:
connect username/password;
select
dbms_metadata.get_granted_ddl('system_grant','')
from dual;
select dbms_metadata.get_granted_ddl('role_grant','')
from dual;
select dbms_metadata.get_granted_ddl('object_grant','')
from dual;
