DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: systems privs

  1. #1
    Join Date
    Sep 2002
    Posts
    376

    systems privs

    I am trying to see the system privs granted via role to a user using the view dba_sys_privs, but it is not listing.

    Am i missing something ??


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


    SQL> connect / as sysdba
    Connected.

    SQL> grant DBA to scott;

    Grant succeeded.

    SQL> select * from dba_sys_privs where grantee='SCOTT';

    GRANTEE PRIVILEGE ADM
    -------- --------------------- ---
    SCOTT UNLIMITED TABLESPACE NO

    SQL>
    ------------------------------------------------------------------------
    The most enjoyable things in the world are either Immoral or too Expensive or otherwise Inaccessible anyway

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    dba_role_privs

    dba is a role

  3. #3
    Join Date
    Sep 2002
    Posts
    376
    Quote Originally Posted by davey23uk
    dba_role_privs

    dba is a role

    Thanx for the Update


    Why does it show only one system priv UNLIMITED TABLESPACE, but not the rest of the system privs granted via DBA ?

    I am trying to list all the system privs granted to the user (irrespective of grant via Role or Direct)
    ------------------------------------------------------------------------
    The most enjoyable things in the world are either Immoral or too Expensive or otherwise Inaccessible anyway

  4. #4
    Join Date
    Sep 2005
    Posts
    278
    hope the following is the query which your looking for:

    Code:
    SELECT * FROM dba_role_privs
    WHERE grantee IN (SELECT granted_role FROM dba_roles
    WHERE grantee = 'SCOTT')
    union
    SELECT * FROM dba_role_privs
    WHERE grantee = 'SCOTT'

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    dba is role, the privelege unlimited tablespace is granted in addtion to the dba role - it is not part of the dba role (you get the same thing with resource)

    if you want to see what a user has, find their roles and then find the privs assoicated with those roles

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