- 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.