How to Check / Find out the Privileges and Roles for My Own Session Oracle

advertisements

_____________________________________________________________________________________________________________________

Most of the time, as a SQL programmer user might not be having the privilege to select the data dictionary views to check the permissions of the user sessions. In Oracle any of the users can check their own privileges using following commands after connecting to the SQLPLUS using their credentials.

a.  SELECT * FROM SESSION_ROLES;

Above query will give you which and all roles assigned to your user.

b.  SELECT * FROM SESSION_PRIVS;

This one will give you which and all privileges assigned to your user.

SQL> CREATE USER TEST IDENTIFIED BY TEST;

User created.

SQL> GRANT CREATE SESSION TO TEST;

Grant succeeded.

SQL> CONNECT TEST/TEST

Connected.

SQL> SELECT * FROM SESSION_PRIVS;

PRIVILEGE

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

CREATE SESSION

SQL> SELECT * FROM SESSION_ROLES;

no rows selected

SQL> CONNECT / AS SYSDBA

Connected.

SQL> GRANT RESOURCE TO TEST;

Grant succeeded.

SQL> CONNECT TEST/TEST

Connected.

SQL> SELECT * FROM SESSION_PRIVS;

PRIVILEGE

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

CREATE SESSION

UNLIMITED TABLESPACE

CREATE TABLE

CREATE CLUSTER

CREATE SEQUENCE

CREATE PROCEDURE

CREATE TRIGGER

CREATE TYPE

CREATE OPERATOR

CREATE INDEXTYPE

10 rows selected.

SQL> SELECT * FROM SESSION_ROLES;

ROLE

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

RESOURCE

_____________________________________________________________________________________________________________________

Website Stats