• Arun Kumar

Oracle User Management

In this article I will be sharing simple SQL queries that will help you manage Oracle database users.


User Management Queries


CHECK NUMBER OF USER ACCOUNTS IN DATABASE


SQL> select username, account_status, default_tablespace from dba_users;

CHECK USERNAME YOU ARE CONNECTED WITH


SQL> show user;

HOW TO LOCK/UNLOCK USER ACCOUNTS


SQL> alter user scott account unlock;
SQL> alter user scott account lock;

HOW TO CREATE NEW USER IN DATABASE


SQL> create user usr1 identified by usr1;
SQL> create user usr2 identified by usr2 default tablespace EXAMPLE;

CHANGE USER PASSWORD


SQL> alter user usr1 identified by oracle;

HOW TO CHECK DATABASE DEFAULT TABLESPACE


SQL> select PROPERTY_NAME, PROPERTY_VALUE from database_properties where PROPERTY_NAME like '%DEFAULT%';

GRANT/REVOKE USER PERMISSIONS


Note: When you create a new user, you directly cannot connect to the sqlplus because the new user lacks CREATE SESSION privilage


SQL> grant connect, resource to usr1;
SQL> revoke connect, resource to usr1;

GRANT QUOTA ON A TABLESPACE TO USER


SQL> Alter user usr1 quota 10M on example


Note: Allocating quota doesn’t represent reserving the space. If 2 or more users are sharing a tablespace, quota will filled up in first come first serve basis

TO CHANGE DEFAULT TABLESPACE OF USER


SQL> alter user usr1 default tablespace example;


Note: The objects created in the old tablespace remain unchanged even after changing a default tablespace for a user

DROP A USER


SQL> drop user usr1;
SQL> drop user usr1 cascade;


User Permission Queries

To check system privileges for a user


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

To check object level privileges


SQL> select owner,table_name,privilege from dba_tab_privs where grantee='SALES_CLERK';

To check roles assigned to a user


SQL> select granted_role from dba_role_privs where grantee='SCOTT';

To check permissions assigned to role


SQL> select privilege from role_sys_privs where role='SALES_CLERK';


SQL> select owner,table_name,privilege from role_tab_privs where role='SALES_CLERK';


SQL> select granted_role from role_role_privs where role='SALES_CLERK';


User Role Management


Providing multiple privileges to a user is time consuming. For example:


There are many sales clearks in office and they must be able to perform:

  • INSERT into the DEPT table

  • UPDATE the EMP table

  • DELETE from the BONUS table


CREATE ROLE sales_clerk;

Grant privileges to the role:


GRANT INSERT ON SCOTT.DEPT TO sales_clerk;


GRANT UPDATE ON SCOTT.EMP TO sales_clerk;


GRANT DELETE ON SCOTT.BONUS TO sales_clerk;

Grant the role to the users:


GRANT sales_clerk TO john;

Note: Never grant sysdba role to any other user until specified by database architect.

309 views

Recent Posts

See All

Oracle 12c to 19c Database Upgrade

In this article we will be looking at two most commonly used methods of upgrading Oracle database from 12c to 19c Pre-upgrade tasks Upgrade using DBUA Upgrade using Manual Method You can perform a dir