Friday, November 7, 2008

Priviliges of current logged in oracle user

When we create a schema/user in a data base. These schemas can be accessed using user name and relevant password linked to it in sql plus.
Syntax : -UserName/Password@DataBaseName

This user may not have all the available rights to perform tasks in database.
There are many instants, say suppose if we would like to create another user from this user in data base, definitely the current logged in user must have DBA prviliges to perform such administration task.

In such case to know all the available roles/privilges with the current login.
There is a table called "DBA_ROLES" or "SESSION_ROLES" which holds data.

1) Log in into sql plus with username,password and data base name provided.
2) Run Query "select * from SESSION_ROLES";

Ex:-

ROLE
---------------------------
CONNECT
RESOURCE
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN

ROLE
---------------------------
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
XDBADMIN
XDBWEBSERVICES
OLAP_DBA

You can find listed role names available to current logged in user.

No comments: