Home » RDBMS Server » Server Administration » Script for Just export roles(and their object privileges) (Script for Just export roles(and their object privileges))
Script for Just export roles(and their object privileges) [message #275455] Sat, 20 October 2007 07:45 Go to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
Hi
I am looking for a script just to export(and import) roles.I created a new database and export/import done.Now all else seems to be fine except the role grants...all the roles seem to be there but the grants and privileges for user are all different.

I now want to get both the databases exactly same and for this what i need to do?Is there a way for exporting roles and their privileges alone? Cant see any option in toad.
If anyone has any idea,pls help

Regards
Aviana

[Updated on: Sat, 20 October 2007 07:46]

Report message to a moderator

Re: Script for Just export roles(and their object privileges) [message #275462 is a reply to message #275455] Sat, 20 October 2007 09:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true);

PL/SQL procedure successfully completed.

SQL> select dbms_metadata.get_ddl('ROLE',role) from dba_roles where rownum<=3;
DBMS_METADATA.GET_DDL('ROLE',ROLE)
----------------------------------------------------------------------------------------------------

   CREATE ROLE "CONNECT";


   CREATE ROLE "RESOURCE";


   CREATE ROLE "DBA";


3 rows selected.

SQL> select dbms_metadata.get_granted_ddl('ROLE_GRANT',username) 
  2  from dba_users where username!='SYS' and rownum<=2;
DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',USERNAME)
----------------------------------------------------------------------------------------------------

   GRANT "DBA" TO "SYSTEM" WITH ADMIN OPTION;

   GRANT "AQ_ADMINISTRATOR_ROLE" TO "SYSTEM" WITH ADMIN OPTION;

   GRANT "MGMT_USER" TO "SYSTEM";


   GRANT "RESOURCE" TO "OUTLN";


2 rows selected.

Regards
Michel
Re: Script for Just export roles(and their object privileges) [message #275463 is a reply to message #275462] Sat, 20 October 2007 09:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And also:
SQL> select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','CONNECT') from dual;
DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','CONNECT')
------------------------------------------------------------------------------

  GRANT CREATE SESSION TO "CONNECT";


1 row selected.

SQL> select dbms_metadata.get_granted_ddl('OBJECT_GRANT','AQ_USER_ROLE') from dual;
DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','AQ_USER_ROLE')
-----------------------------------------------------------------------------------

  GRANT EXECUTE ON "SYS"."DBMS_TRANSFORM" TO "AQ_USER_ROLE";

  GRANT EXECUTE ON "SYS"."DBMS_AQ" TO "AQ_USER_ROLE";

  GRANT EXECUTE ON "SYS"."DBMS_AQIN" TO "AQ_USER_ROLE";

  GRANT EXECUTE ON "SYS"."DBMS_AQJMS_INTERNAL" TO "AQ_USER_ROLE";


1 row selected.

Regards
Michel
Re: Script for Just export roles(and their object privileges) [message #276182 is a reply to message #275463] Wed, 24 October 2007 05:16 Go to previous message
aviana
Messages: 101
Registered: July 2007
Senior Member
thanks Michel
It was indeed really very helpful
Previous Topic: Detection of oracle installation
Next Topic: fail to drop tablespace
Goto Forum:
  


Current Time: Sat Dec 03 08:03:58 CST 2016

Total time taken to generate the page: 0.06964 seconds