3 Replies Latest reply on Mar 24, 2014 9:51 AM by onkar.nath

    Privileges assigned to Roles

    ProDBA

      Hello Community,

       

      I am a bit confusing on the privileges granted to roles. I had tested the following example. First I had created a role named role1 and assigned some privileges.

       

      SQL> CREATE ROLE role1;

      SQL> GRANT connect, resource, create any table, drop any table,  create any procedure, create session, create trigger, dba  TO role1;

       

      In order to verify the privileges assigned to role I had executed the following:

       

      SQL> SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT','ROLE1') FROM dual;

          

           DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','ROLE1')

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

           GRANT CREATE TRIGGER TO "ROLE1"

           GRANT CREATE ANY PROCEDURE TO "ROLE1"

           GRA

       

      SQL> SELECT dbms_metadata.get_granted_ddl('ROLE_GRANT',  'ROLE1') FROM dual;

       

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

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

           GRANT "CONNECT" TO "ROLE1"

           GRANT "RESOURCE" TO "ROLE1"

           GRANT "DBA" TO

       

      Not all the privileges are returned also there are some missing words like GRA and GRANT "DBA" TO . I need to know that how can I get all the privileges assigned to the role and why there are missing words returned by output.

       

      Regards,

      Ali Raza Memon