• 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 Backups using Shell Scripts

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

Feedback