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;
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';
0 comments:
Post a Comment