Home » RDBMS Server » Security » Privs to a user.
Privs to a user. [message #60609] Thu, 19 February 2004 00:22 Go to next message
Neha
Messages: 20
Registered: July 2000
Junior Member
Hi

I have created some users in my database oracle9i and I have granted them connect and resource as roles. How do I know what priviliges have been assigned to them. Also there are priviliges within priviliges how can I query that.

Neha
Re: Privs to a user. [message #60611 is a reply to message #60609] Thu, 19 February 2004 04:28 Go to previous message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
-- to find out system privileges granted to user 

SQL> select privilege from dba_sys_privs where grantee='THIRU';

PRIVILEGE
----------------------------------------
ALTER SESSION
UNLIMITED TABLESPACE

-- to find out roles granted to a user

SQL> select GRANTED_ROLE from dba_role_privs where GRANTEE='THIRU';

GRANTED_ROLE
------------------------------
DBA
CONNECT
RESOURCE
TEST_ROLE

-- to find out explicit table privileges granted

SQL> select owner,table_name,PRIVILEGE from dba_tab_privs where GRANTEE='THIRU';

no rows selected

-- to find out privileges contained within a role

SQL> select privilege from dba_sys_privs where grantee='CONNECT';

PRIVILEGE
----------------------------------------
CREATE VIEW
CREATE TABLE
ALTER SESSION
CREATE CLUSTER
CREATE SESSION
CREATE SYNONYM
CREATE SEQUENCE
CREATE DATABASE LINK

8 rows selected.

-- also system privileges contained in a role

SQL> select PRIVILEGE from role_sys_privs where ROLE='CONNECT';

PRIVILEGE
----------------------------------------
CREATE VIEW
CREATE TABLE
ALTER SESSION
CREATE CLUSTER
CREATE SESSION
CREATE SYNONYM
CREATE SEQUENCE
CREATE DATABASE LINK

8 rows selected.

..and so on.



-Thiru
Previous Topic: trigger auditing in Ora 8.1.7.4.1
Next Topic: error: ora01931 grant references to a role
Goto Forum:
  


Current Time: Tue Sep 21 00:01:09 CDT 2021