Create User in Oracle Database Pre-12c and 12c

This post will give you brief details about how to create user in oracle database in 10g,11g and 12c

Pre-12c Database (Application to all below 12c ):

creating user is normal and important activity is DBA’s life. below commands are described to create user in databases under 12c.

Granting create session is mandatory as it will allow you to connect to database, assume username is xyz and password is abc123

create user xyz identified by abc123 
default tablespace users
temporary tablespace temp
profile default 
password expire;
grant create session to xyz;

Here

USERS is default tablespace

TEMP is temporary tablespace

“password expire” option will allow user to change the password while first login.

DEFAULT is profile assigned to user

User Creation in 12c Database:

In 12c database we have concept container database and pluggable databases, we should be clear with our idea whether we need to create user in container to pluggable db

In order to create user in container database i.e. user having access to all pluggable databases we need to use c## prefix with username. For example

create user c##xyz identified by abc123;

grant create session to c##xyz;

For creating user under any pluggable database we need to set container database using alter session command, Suppose pluggable database is PDB1

Command to set pluggable database

alter session set container=PDB1;

create user xyz identified by abc123;

grant create session to xyz;

Changing Default ,Temporary Tablespace, Granting Role,Privileges

We can simply perform these operation using alter user command in order to give more access to user as per application requirement

In case we don’t mention anything during create user command oracle will assign default and temporary tablespace as per database’s default settings

Changing users default and temp tablespace

alter user xyz default tablespace <tablespace_name>;

alter user xyz temporary tablespace <temporary tablespace name>;

In case we need to create objects under this user then we are suppose to grant quota on tablespace this can be done using below command

alter user xyz quota unlimited on <tablespace_name>;

granting Roles and Privileges

grant <role_name> to xyz

grant select,insert,update,delete on <table_name> to xyz ;

Verification of user creation  and grants

Select username, profile,account_status from dba_users where username='XYZ';

select grantee,PRIVILEGE from dba_sys_privs where grantee='XYZ';

select grantee,granted_role from dba_role_privs where grantee='XYZ';