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>

dba_role_privs

dba is a role

Quote:

---

Originally Posted by davey23uk

dba_role_privs

dba is a role

---

Thanx for the Update :D


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)

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'

---

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