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

Recent Posts

See All

MySQL Installation on Linux

This article demonstrates MySQL installation on Oracle Enterprise Linux 6.5 version. Installation of MySQL Make sure you are able to connect internet via virtual machine. Try to ping google.com and pr

Add ISO image storage repository on XenServer

If you have used VMware ESXI, its very easy to upload ISO files to the data store and create VMs. With XenServer, you must create your own ISO repository to upload ISO files. Create ISO directory Acce

Automate RMAN Backup using Shell Script

In a real environment, off course you will not manually trigger all the Oracle database backups. You need an automated mechanism to trigger RMAN backups. In this article we will look at RMAN backup au

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis