CODEWITHSUNDEEP
oracle-databasesecurity
APDIT
APDIT

Reputation: 21

Oracle - how to export security policies

Our Oracle DBA is planning to move a number of schemas to new instances. One thing we've noticed - security policies are not being carried over in the exports. Also- they don't seem to count as 'objects' when querying 'all_objects' or 'user_objects'. Different kind of animal I guess.

Any ideas on how to migrate this stuff smoothly?

Upvotes: 2

Views: 237

Answers (1)

mrjoltcola
mrjoltcola

Reputation: 20842

Personally I use a tool to extract/generate scripts for specific users or roles. Toad can script users, as can other tools. The one I now use is my own, but I cannot promote it here. If you don't have such a tool, there are other options.

Use export or data pump. When you do full system export (using exp or data pump) you will get all of the grants. You can then use the import / impdp utilities to dump all of the DDL for the grants for users of interest. Once you have the dmp file.

impdp system/ full=Y directory=dumpdir dumpfile=full.dmp logfile=dump.txt grants=y

Or for export

imp system/ full=y grants=y file=full.dmp log=dump.txt

This will dump everything to the dump.txt in text form and you can extract the SQL. Not super pretty, but works.

Use DBMS_METADATA to give you the grants for a user or role.

SQL> set long 50000

SQL> select dbms_metadata.get_ddl( 'USER', 'MSMITH' ) from dual;
SQL> select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', 'MSMITH') from dual;
SQL> select dbms_metadata.get_granted_ddl('OBJECT_GRANT', 'MSMITH') from dual;
SQL> select dbms_metadata.get_granted_ddl('ROLE_GRANT', 'MSMITH') from dual;

Or for roles:

SQL> select dbms_metadata.get_ddl( 'ROLE', 'JUNIOR_DBA' ) from dual;
SQL> select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', 'JUNIOR_DBA') from dual;
SQL> select dbms_metadata.get_granted_ddl('OBJECT_GRANT', 'JUNIOR_DBA') from dual;
SQL> select dbms_metadata.get_granted_ddl('ROLE_GRANT', 'JUNIOR_DBA') from dual;

Upvotes: 2

Related Questions