OraFAQ Forum: SQL & PL/SQL » regarding ROLE

Home » SQL & PL/SQL » SQL & PL/SQL » regarding ROLE

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

regarding ROLE [message #205894]

Tue, 28 November 2006 01:08

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

Report message to a moderator

Re: regarding ROLE [message #205932 is a reply to message #205894]

Tue, 28 November 2006 03:12

Frank Naude
Messages: 4581
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"
...

Report message to a moderator

Re: regarding ROLE [message #205935 is a reply to message #205932]

Tue, 28 November 2006 03:18

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.

Report message to a moderator

Re: regarding ROLE [message #205945 is a reply to message #205932]

Tue, 28 November 2006 03:48

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

Report message to a moderator

Re: regarding ROLE [message #205971 is a reply to message #205945]

Tue, 28 November 2006 04:31

Frank Naude
Messages: 4581
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;

Report message to a moderator

Re: regarding ROLE [message #205988 is a reply to message #205971]

Tue, 28 November 2006 05:05

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

I think at this point, a RTFM is justified.

Report message to a moderator

Previous Topic:	HELP! Multiple Cursors
Next Topic:	oracle log in error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 05 00:26:16 CST 2024