DBMS_METADATA.GET_GRANTED_DDL and DEFAULT ROLE errors

From: mhthomas <qnxodba@xxxxxxxxx>
To: oracle-l <oracle-l@xxxxxxxxxxxxx>
Date: Wed, 16 Feb 2005 19:52:16 -0500
Hi,

1) 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@calp > set pages 9999 line 132 trimspool on long 90000
SYS@calp > --
SYS@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@calp > --
SYS@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@calp > --
SYS@calp > select * from dba_role_privs
  2   where grantee = 'CBSLATE';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
CBSLATE                        CONNECT                        NO  YES

1 row selected.

SYS@calp >


--
--
//www.freelists.org/webpage/oracle-l
DBMS_METADATA.GET_GRANTED_DDL and DEFAULT ROLE errors

Other related posts:
