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.