#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    26
    Rep Power
    0

    Role, user one doubt..pls help


    When i query dba_role_privs, one of the GRANTEE there is 'DBA' which is granted several roles & now when i query dba_users there is no 'DBA' value in column USER there....i understand one would say DBA would be a role in dba_role_privs and not an actual user..but if thats the case then why SYS which is assigned DBA role has some similar roles which are shown assigned to this GRANTEE 'DBA'....also if any user DBA is there why it is not showing up in dba_users...please let me know where am wrong or misunderstood something..or let me know if i need to rephrase my query to make it more clear.....Thanks a lot for help..!
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    859
    Rep Power
    388

    Talking


    Originally Posted by Amukherji
    When i query dba_role_privs, one of the GRANTEE there is 'DBA' which is granted several roles & now when i query dba_users there is no 'DBA' value in column USER there....i understand one would say DBA would be a role in dba_role_privs and not an actual user..but if thats the case then why SYS which is assigned DBA role has some similar roles which are shown assigned to this GRANTEE 'DBA'....also if any user DBA is there why it is not showing up in dba_users...please let me know where am wrong or misunderstood something..or let me know if i need to rephrase my query to make it more clear.....Thanks a lot for help..!
    DBA is a role.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    26
    Rep Power
    0
    Originally Posted by LKBrwn_DBA
    DBA is a role.

    i know, but confused..pls tell me why then SYS is assigned these roles (olap_dba, plustrace,javaadmin,xdbadmin, import, export db etc) again when it has dba role always assigned to it.
    pls see this & explain , sorry am confused


    SQL> select * from dba_role_privs where grantee = 'DBA';

    GRANTEE GRANTED_ROLE
    ------------------------------ -------------------------
    DBA OLAP_DBA
    DBA XDBADMIN
    DBA PLUSTRACE
    DBA JAVA_ADMIN
    DBA JAVA_DEPLOY
    DBA WM_ADMIN_ROLE
    DBA EXP_FULL_DATABASE
    DBA IMP_FULL_DATABASE
    DBA DELETE_CATALOG_ROLE
    DBA SELECT_CATALOG_ROLE
    DBA EXECUTE_CATALOG_ROLE



    and :-

    SQL> select * from dba_role_privs where grantee = 'SYS';

    GRANTEE GRANTED_ROLE
    ------------------------------ ---------------------------
    SYS DBA
    SYS CTXAPP
    SYS WKUSER
    SYS CONNECT
    SYS OLAP_DBA
    SYS RESOURCE
    SYS XDBADMIN
    SYS EJBCLIENT
    SYS PLUSTRACE
    SYS JAVAIDPRIV
    SYS JAVA_ADMIN
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    58
    Rep Power
    5
    Amukherji,

    that's because (as part of the CREATE ROLE statement) Oracle automatically grants EVERY newly created role (WITH ADMIN OPTION) to SYS
    Last edited by magicwand; January 6th, 2012 at 02:17 PM.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    26
    Rep Power
    0
    Originally Posted by magicwand
    Amukherji,

    that's because (as part of the CREATE ROLE statement) Oracle automatically grants EVERY newly created role (WITH ADMIN OPTION) to SYS
    hmm, so in dba_role _privs in GRANTEE column, only DBA is the role which is assigned roles and all other values are users..thanks ...i know this question was stupid but thanks for clearing my doubt..

IMN logo majestic logo threadwatch logo seochat tools logo