We help IT Professionals succeed at work.

Create script

crishna1
crishna1 asked
on
Hi,

I have about 100 roles in a Oracle database and i would like to generate a CREATE script for those roles. Does anyone have such sql, script , please let me know.

This whole things started after i did a datapump export and datapump import and the roles are not getting created and its throwing error like so and so role does not exist , while it tries to run the grants/privileges. so i am thinking to precreate the roles.

Thanks!
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
From:
http://www.orafaq.com/wiki/DBMS_METADATA

SELECT dbms_metadata.get_granted_ddl('ROLE_GRANT',  '&&your_role_name') FROM dual;
SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT','&&your_role_name') FROM dual;
SELECT dbms_metadata.get_granted_ddl('OBJECT_GRANT','&&your_role_name') FROM dual;
Hi

You can use following queries:

select 'create role ' || role || ';' from dba_roles;
select 'grant ' || privilege || ' to ' || grantee || ' ' || decode(admin_option,'YES','with admin option;',';') from dba_sys_privs;
select 'grant ' || privilege || ' on ' || owner || '.' || table_name || ' to ' || grantee || decode(grantable,'YES',' with grant option;',';')
from dba_tab_privs;
select 'grant ' || granted_role || ' to ' || grantee || decode(admin_option,'YES',' with admin option;',';') from dba_role_privs;

If you want to limit the creation script to the roles that you want to recreate please add appropriate where clause as in the example below (replace role1, role2...) with your role names.

select 'create role ' || role || ';' from dba_roles
where role in ('ROLE1','ROLE2');
select 'grant ' || privilege || ' to ' || grantee || ' ' || decode(admin_option,'YES','with admin option;',';') from dba_sys_privs
where grantee in ('ROLE1','ROLE2');
select 'grant ' || privilege || ' on ' || owner || '.' || table_name || ' to ' || grantee || decode(grantable,'YES',' with grant option;',';')
from dba_tab_privs
where grantee in ('ROLE1','ROLE2');
select 'grant ' || granted_role || ' to ' || grantee || decode(admin_option,'YES',' with admin option;',';') from dba_role_privs
where grantee in ('ROLE1','ROLE2');

Best regards,
Jarek

Author

Commented:
i just need, "Create role rolename" script. the data pump import is taking care of the grants.

Author

Commented:
i think i got it with the first statement in your post, select 'create role ' || role || ';' from dba_roles.

many thanks!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.