Home » SQL & PL/SQL » SQL & PL/SQL » regarding ROLE
regarding ROLE [message #205894] Tue, 28 November 2006 01:08 Go to next message
frank.svs
Messages: 162
Registered: February 2006
Senior Member
Hi frns,

I have one issue can we build the ddl script for a particular role.

for example as scott user

CREATE ROLE R1;
GRANT SELECT ON EMP TO R1;
GRANT ALL ON DEPT TO R1;
SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE='R1'


because i am able to do the following to get the ddl for below objects
SET LONG 50000
select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') FROM DUAL;
select dbms_metadata.get_ddl('INDEX','IDX','SCOTT') FROM DUAL;
select dbms_metadata.get_ddl('VIEW','V1','SCOTT') FROM DUAL;
select dbms_metadata.get_ddl('PROCEDURE','P1','SCOTT') FROM DUAL;
select dbms_metadata.get_ddl('PACKAGE','MYPKG1','SCOTT') FROM DUAL;
select dbms_metadata.get_ddl('SYNONYM','SYN','SCOTT') FROM DUAL;

but how to get ddl for a role.is it possible.

If any one knows plz let me know.

Thanks in advance,
Franky
Re: regarding ROLE [message #205932 is a reply to message #205894] Tue, 28 November 2006 03:12 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
SQL> SELECT dbms_metadata.get_ddl('ROLE', role) FROM dba_roles;

DBMS_METADATA.GET_DDL('ROLE',ROLE)
----------------------------------------------------------------

   CREATE ROLE "CONNECT"

   CREATE ROLE "RESOURCE"

   CREATE ROLE "DBA"
...
Re: regarding ROLE [message #205935 is a reply to message #205932] Tue, 28 November 2006 03:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That'll teach me.
I'd assumed the OP would have tried that and had it fail on them.

Hey ho.
Re: regarding ROLE [message #205945 is a reply to message #205932] Tue, 28 November 2006 03:48 Go to previous messageGo to next message
frank.svs
Messages: 162
Registered: February 2006
Senior Member
Well That's ok but to generate the ddl for what Object privileges has been granted to the ROLE R1 and what system privileges are granted to the ROLE R1.

Any idea.

Thanks in advance,
Franky
Re: regarding ROLE [message #205971 is a reply to message #205945] Tue, 28 November 2006 04:31 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
SELECT dbms_metadata.get_granted_ddl('ROLE_GRANT','CONNECT')   FROM DUAL;
SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT','CONNECT') FROM DUAL;
SELECT dbms_metadata.get_granted_ddl('OBJECT_GRANT','CONNECT') FROM DUAL;
Re: regarding ROLE [message #205988 is a reply to message #205971] Tue, 28 November 2006 05:05 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think at this point, a RTFM is justified.
Previous Topic: HELP! Multiple Cursors
Next Topic: oracle log in error
Goto Forum:
  


Current Time: Fri Dec 09 13:48:07 CST 2016

Total time taken to generate the page: 0.12993 seconds