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


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
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google