User Management in ORACLE

USER MANAGEMENT in ORACLE 11g

User management is a common activity for a ORACLE DBAs. Followings are real time & day-to-day activity examples for a DBA .

A DBA should create a user, after understanding from the application team that, what the user needs.

While creating a user, we can assign the default tablespace and default temporary tablespace for that user.

Privileges

It is a rights for a user to create, modify and delete on object-level and schema-level.

It is two types.

·         System level privilege - eg: create table, create view etc

·         Object level privilege - eg: select on A, update on B etc

Role

A role is a set of privileges which will reduce the risk of issuing many commands

To findout roles and privileges assigned to a user, use following views
a. DBA_SYS_PRIVS
b. DBA_TAB_PRIVS

c. DBA_ROLE_PRIVS
d. ROLE_SYS_PRIVS
e. ROLE_TAB_PRIVS
f. ROLE_ROLE_PRIVS

g. USER_SYS_PRIVS

h. USER_TAB_PRIVS

i. DBA_ROLES

Privileges with admin option

System level privileges can be granted with admin option so that the grantee can grant the same
privilege to other users. If we revoke the privilege from first grantee, still others will have that
privilege.

Privileges with grant option

Object level privileges can be granted with grant option so that grantee can grant the same
privilege to other users. If we revoke the privilege from first grantee, it will revoked from all the
users.

EXAMPLES

# To create a user
SQL> create user dr identified by dr
default tablespace kalamtbs
temporary tablespace temp;

# Create user with demand for a new password at the time of login

SYS@ORCL>alter user dr identified by dr password expire;

# To grant permissions to user
SQL> grant create session, create table to dr;

# To grant permissions to user with admin option
SQL> grant create table to scott with admin option;

# To grant permissions to user with grant option
SQL> grant update on scott.salary to dr with grant option;

# To revoke any permissions from user
SQL> revoke create table from scott;

# To change password of user

SQL> alter user dr identified by oracle;

# To allocate quota on tablespace
SQL> alter user dr quota 10m on mydata;

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 or temporary tablespace
SQL> alter user dr default tablespace test;
SQL> alter user dr default temporary tablespace mytemp;

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

# To check default permanent & temporary tablespace for a user (for user level)
SQL> select default_tablespace,temporary_tablespace from dba_users where username='SCOTT';

# To lock or unlock a user
SQL> alter user scott account lock;
SQL> alter user scott account unlock;

# To check default permanent tablespace and temporary tablespace (in database level)

SQL> select property_name,property_value from database_properties where property_name like 'DEFAULT%';

# To change default permanent tablespace

SQL> alter database default tablespace mydata;

# To change default temporary tablespace

SQL> alter database default temporary tablespace mytemp;

# 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='SCOTT';

# 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='MYROLE';
SQL> select owner,table_name,privilege from role_tab_privs where role='MYROLE';
SQL> select granted_role from role_role_privs where role='MYROLE';

# Create a role

SYS@ORCL>create role drrole1 identified by dr;

# Assign some privileges to a role

SYS@ORCL>grant create table to drrole1;

SYS@ORCL>grant select on hr.employees to drrole1;

# Assign a role to user

SYS@ORCL>grant drrole1 to dr;

# Revoke a role from a user

SYS@ORCL>revoke drrole1 from dr;

# To drop a user
SQL> drop user user1;
Or
SQL> drop user user1 cascade;

Note :

a)       After creating user, don't grant “connect” and “resource” roles . We can grant “connect” role as it contains only create session privilege.

b)      Resource role internally contains “unlimited tablespace” privilege and because of this, the user can create table in any tablespace. So, it should not be granted in real time until it is required.