Forum Stats

  • 3,876,209 Users
  • 2,267,082 Discussions
  • 7,912,469 Comments

Discussions

In a non CDB 12c database, DBA_ROLE_PRIVS.COMMON has 'YES' values for grantee 'SYS' with some grante

Thilinaa
Thilinaa Member Posts: 13
edited Aug 24, 2014 1:40AM in Multitenant

SELECT /*+ RULE */ grantee, granted_role

      FROM   sys.dba_role_privs

      WHERE  grantee LIKE 'SYS'

      AND    default_role = 'YES'

This query returns two rows with one Common value one set to YES ans other set to NO as follows.

SYSJAVAIDPRIVYESYESNO
SYSJAVAIDPRIVNOYESYES
GranteeGranted_RoleAdmin_OptionDefault_RoleCommon

 

Facts: SELECT CDB FROM V$DATABASE; returns NO

          SELECT CON_ID, CON_UID FROM V$CONTAINERS ORDER BY CON_ID;  returns 0,0

What I want to clarify is, while this is being a non container database, what is the meaning of Common = Yes.

(I posted this initially in here

As rp0428 has said I posted this in this forum, but i can't mark that discussion as ANSWERED because it has been locked. I don't know why it has been locked)

Thilinaa

Answers

  • Thilinaa wrote:
    
    SELECT /*+ RULE */ grantee, granted_role
          FROM   sys.dba_role_privs
          WHERE  grantee LIKE 'SYS'
          AND    default_role = 'YES'
    
    This query returns two rows with one Common value one set to YES ans other set to NO as follows.
    
    
    
    
    
    SYS
    JAVAIDPRIV
    YES
    YES
    NO
    
    
    
    SYS
    JAVAIDPRIV
    NO
    YES
    YES
    
    
    
    Grantee
    Granted_Role
    Admin_Option
    Default_Role
    Common
    
    
    
    
    Facts: SELECT CDB FROM V$DATABASE; returns NO
              SELECT CON_ID, CON_UID FROM V$CONTAINERS ORDER BY CON_ID;  returns 0,0
    
    What I want to clarify is, while this is being a non container database, what is the meaning of Common = Yes.
    
    (I posted this initially in here In a non CDB 12c database, DBA_ROLE_PRIVS.COMMON has 'YES' values for grantee 'SYS' with some granted roles.
    As rp0428 has said I posted this in this forum, but i can't mark that discussion as ANSWERED because it has been locked. I don't know why it has been locked)
    

    It was locked by a moderator to prevent further use in that forum. The only other option was to delete it and some moderators are not authorized to do that.

    As I said in that forum, when you post you need to provide ACCURATE information. The query you posted select only TWO columns but then you show a result set that appears to include FIVE columns.

    That means the the query you posted DID NOT produce the result set that you posted. That immediately raises a RED FLAG and questions about what else might be wrong.

    See the Database Reference

    DBA_ROLE_PRIVS

    Most of the system views have a consistent interface regardless of whether the database is a CDB or a non-cdb.

    Non-cdb databases are designed to be able to be plugged into a CDB if desired.

    The grant you show is for SYS. By default SYS has privileges in the CDB and in ALL PDBs of a multitenant database so most privileges for SYS needed to be commonly granted.

    So my 'best guess' is that the two rows you see are the grants that are needed to work properly in a CDB environment if you were to plug that non-cdb into a CDB container. Oracle decided to just provide the second 'COMMON' grant even for non-CDBs rather than have to generate all of those grants (and there are a LOT  of them) during a future migration of the non-cdb DB to a CDB.

    ThilinaaThilinaa
  • Thilinaa
    Thilinaa Member Posts: 13

    Sorry for the mismatched information given. It was by mistaken while re posting from the previous forum to this and by adding additional information. And thank you for the helpful reply.

This discussion has been closed.