DBMS_METADATA.GET_GRANTED_DDL and DEFAULT ROLE errors

From: mhthomas <qnxodba_at_gmail.com>
Date: Wed, 16 Feb 2005 19:52:16 -0500
Message-ID: <d6bad0800502161652ed1730e@mail.gmail.com>

Hi,

I have one instance that generates syntax errors with the following command:

SELECT DBMS_METADATA.GET_GRANTED_DDL('DEFAULT_ROLE', 'CBSLATE') FROM dual;

ALTER USER "CBSLATE" DEFAULT ROLE ALL EXCEPT The systax error is the 'EXCEPT' because this user has no non-default roles. I attached ERROR OUTPUT below to show important details.

2) I can prove it with this command:

select * from dba_role_privs where grantee = 'CBSLATE';

GRANTEE                        GRANTED_ROLE                   ADM DEF


------------------------------ ------------------------------ --- ---

CBSLATE                        CONNECT                        NO  YES

1 row selected.

3) Before I file a TAR, I'm curious if this happens to others or just me?

I've attached some TEST SQL below. I checked metalink and found no other error reports. I'm on 10.1.0.3 on rh3as u2, etc.

4) I wrote one decent version of ROLE GRANT/SYSTEM GRANT/OBJECT GRANT using the DBMS package as well. If requested I'll post in followup. I've seen other versions in various places on the web/docs but they were incomplete. Please advise if anyone has other good references or links.

Regards

Mike Thomas

TEST SQL **********
- *** DEFAULT ROLE *** set pages 9999 line 132 trimspool on long 90000
  --
- Roles which are granted to roles SELECT DBMS_METADATA.GET_GRANTED_DDL('DEFAULT_ROLE', role) FROM role_role_privs /
- Roles which are granted to users and public SELECT DBMS_METADATA.GET_GRANTED_DDL('DEFAULT_ROLE', username) FROM (select distinct username from dba_users d, dba_role_privs s where d.username=s.grantee --union select 'PUBLIC' from dual ) /
ERROR OUTPUT: ********** Connected to: Oracle Database 10g Release 10.1.0.3.0 - Production With the Real Application Clusters option

SYS_at_calp > set pages 9999 line 132 trimspool on long 90000
SYS_at_calp > --
SYS_at_calp > SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'CBSLATE')

2 FROM dual;

DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','CBSLATE')

GRANT "CONNECT" TO "CBSLATE" 1 row selected.

SYS_at_calp > --
SYS_at_calp > SELECT DBMS_METADATA.GET_GRANTED_DDL('DEFAULT_ROLE', 'CBSLATE') 2 FROM dual;

DBMS_METADATA.GET_GRANTED_DDL('DEFAULT_ROLE','CBSLATE')

ALTER USER "CBSLATE" DEFAULT ROLE ALL EXCEPT 1 row selected.

SYS_at_calp > --
SYS_at_calp > select * from dba_role_privs
2 where grantee = 'CBSLATE';

GRANTEE                        GRANTED_ROLE                   ADM DEF


------------------------------ ------------------------------ --- ---

CBSLATE                        CONNECT                        NO  YES

1 row selected.

SYS_at_calp >

--
--

http://www.freelists.org/webpage/oracle-l Received on Wed Feb 16 2005 - 19:55:09 CST