can't access dba_role_privs view anymore after upgrading from 10g to11gR2
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: can't access dba_role_privs view anymore after upgrading from 10g to11gR2

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Posts
    198

    can't access dba_role_privs view anymore after upgrading from 10g to11gR2

    the Applicatin Admin of our system can no longer access this view
    dba_role_privs since we upgraded from 10g to 11gR2.

    this is what I have before:
    -----
    role (app_admin)
    grant select on dba_role_privs to app_admin
    grant app_admin to user -- applicatioin administrator
    ----

    the above doesn't work anymore even after I recreated the whole
    thing again.

    Now, if I grant the user direct access to the view then it works fine.
    grant select on dba_role_privs to user

    any idea???

    Thanks in advance

  2. #2
    Join Date
    Jul 2006
    Posts
    195
    This works for me.

    sqlplus '/ as sysdba'

    SQL> select * from v$version;

    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    PL/SQL Release 11.2.0.1.0 - Production
    CORE 11.2.0.1.0 Production
    TNS for Solaris: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production


    SQL> create role xxxx;
    Role created.
    SQL> GRANT CREATE VIEW TO xxxx;
    Grant succeeded.
    SQL> GRANT CREATE TABLE TO XXXX;
    Grant succeeded.
    SQL> GRANT CREATE SESSION TO XXXX;
    Grant succeeded.
    SQL> GRANT ALTER SESSION TO XXXX;
    Grant succeeded.

    SQL> grant select on dba_role_privs to XXXX;

    SQL> create user yyy identified by yyy default tablespace users
    temporary tablespace temp;


    SQL> grant xxxx to yyy;

    SQL> exit

    sqlplus yyy/yyy

    Grant succeeded.
    SQL> show user
    USER is "YYY"

    SQL> select * from dba_role_privs;

    GRANTEE GRANTED_ROLE ADM DEF
    ------------------------------ ------------------------------ --- ---
    SYS GL_RUID YES YES
    SYS EXP_FULL_DATABASE YES YES
    SYS CONNECT YES YES
    DBA DATAPUMP_IMP_FULL_DATABASE NO YES
    IMP_FULL_DATABASE SELECT_CATALOG_ROLE NO YES
    .....
    ......
    ......

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    Quote Originally Posted by ocpdude View Post
    the Applicatin Admin of our system can no longer access this view
    dba_role_privs since we upgraded from 10g to 11gR2.

    this is what I have before:
    -----
    role (app_admin)
    grant select on dba_role_privs to app_admin
    grant app_admin to user -- applicatioin administrator
    ----

    the above doesn't work anymore even after I recreated the whole
    thing again.

    Now, if I grant the user direct access to the view then it works fine.
    grant select on dba_role_privs to user

    any idea???

    Thanks in advance
    because priveleges given to a role do not work in plsql

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width