Oracle Role Definitions + Privileges

Hi, how I can extract all the role names & privileges, is there any way/script or oracle support doc, you are aware of.

Oracle: 11203

Do share, thanks in advance.
Mushfique KhanDirector OperationsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
Role names are stored in DBA_ROLES.

You can extract the privileges from DBA_TAB_PRIVS and DBA_SYS_PRIVS to determine what is granted to the role and then DBA_ROLE_PRIVS to determine what users (or roles) are granted the role.
0
Mushfique KhanDirector OperationsAuthor Commented:
thanks johnsone, but this I'm aware too, looking for some sql or script to generate a file, which can be run on any other db to copy the roles/privileges too.
0
johnsoneSenior Oracle DBACommented:
If you know the syntax, you should be able to use SQL to generate them.

SELECT 'GRANT ' || PRIVILEGE  || ' TO ' || GRANTEEE || ';' FROM DBA_SYS_PRIVS WHERE GRANTEE IN (SELECT ROLE_NAME FROM DBA_ROLES);

You should be able to generate the other commands using the same technique.

Also look at DBMS_METADATA, specifically GET_DDL.  Doc is here -> http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_metada.htm#i1019414  That should give you the commands to regenerate.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mushfique KhanDirector OperationsAuthor Commented:
thanks johnsone, kind of struggling, can you please assist a little more here, how to extract for a specific/one role?

Much appreciated.
0
johnsoneSenior Oracle DBACommented:
What have you tried?
0
slightwv (䄆 Netminder) Commented:
See if this gives you everything you want:
drop role myrole;
create role myrole;

drop table tab1 purge;
create table tab1(col1 char(1));

grant select on tab1 to myrole;

select dbms_metadata.get_ddl('ROLE','MYROLE') from dual;
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', 'MYROLE') from dual;

Open in new window

0
johnsoneSenior Oracle DBACommented:
Well, since we are going to give the answer rather than have someone learn...

If you want to use DBMS_METADATA, then it looks to me like you need 4 commands:
SELECT dbms_metadata.Get_ddl('ROLE', 'ROLE_NAME') 
FROM   dual; 

SELECT dbms_metadata.Get_granted_ddl('OBJECT_GRANT', 'ROLE_NAME') 
FROM   dual; 

SELECT dbms_metadata.Get_granted_ddl('SYSTEM_GRANT', 'ROLE_NAME') 
FROM   dual; 

SELECT dbms_metadata.Get_granted_ddl('ROLE_GRANT', 'ROLE_NAME') 
FROM   dual; 

Open in new window

There is an example of this in the documentation link that I posted earlier.  Not sure why you couldn't find that.

If you would rather query for it, then these should work (NOTE: This implies that you are using an unidentified role which is most common):
SELECT 'create role ' 
       || ROLE 
       || ';' 
FROM   dba_roles 
WHERE  ROLE = 'ROLE_NAME'; 

SELECT 'grant ' 
       || privilege 
       || ' to ' 
       || grantee 
       || CASE 
            WHEN admin_option = 'YES' THEN ' with admin option ' 
          END 
       || ';' 
FROM   dba_sys_privs 
WHERE  grantee = 'ROLE_NAME'; 

SELECT 'grant ' 
       || privilege 
       || ' on ' 
       || owner 
       || '.' 
       || table_name 
       || ' to ' 
       || grantee 
       || ';' 
FROM   dba_tab_privs 
WHERE  grantee = 'ROLE_NAME'; 

SELECT 'grant ' 
       || granted_role 
       || ' to ' 
       || grantee 
       || CASE 
            WHEN admin_option = 'YES' THEN ' with admin option' 
          END 
       || ';' 
FROM   dba_role_privs 
WHERE  grantee = 'ROLE_NAME'; 

Open in new window

Those should catch the major options that people use on grants.  If you are using things outside those options, then you are going to have to add additional clauses to pick up the other values.
0
Mushfique KhanDirector OperationsAuthor Commented:
thanks a lot, let me test/verify, will get back soon.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.