Share via:


How to create user?

Syntax sys>> create user <username> identified by <password>;

Create user u1 identified by u1;

How to assign the privileges to user?

Grant connect,resource to u1;

How to assign the tablespace to user?

Alter user u1 default tablespace userdata;

How to assign the tablespace while creating the user?

Create user u2 identified by u2 default tablespace ts1;

How to assign the privileges while creating the user?

Grant connect,resource to u3 identified by u3;


What is ROLE?

Role : set of privileges or group of privileges

How to create the role?

Create role <rolename>;(without password)

Create role <rolename> identified by <password>;(using password)

Create role r1;

Create role r2 identified by r2;

How to assign the privileges to role?

Grant <privileges> to <rolename>;

Privileges are of 2 types

System level: to perform some action against your database

Object level: on top of objects

Granting system level privileges?

Grant create tablespace to u1;

Sys>Grant create tablespace to u2 with admin option;

U2>grant create tablespace u3;

Sys>revoke create tablespace from u2;


Granting object level privileges?

U1>grant select on emp to u2;

U1>grant select on dept to u2 with grant option;

U2>grant select on u1.dept to u3;

U1>revoke select on emp from u2;


To see user information?

Desc dba_users

Select username from dba_users;

To check username and default tablespace for user?

select username,default_tablespace from dba_users;

to check roles information?

Desc dba_roles

Select * from dba_roles;

To see what are the privileges in a role?

Select * from role_sys_privs;

What is profile?

Profile : profile is a set of resources

To check  profile information?

Desc dba_profiles

Select * from dba_profiles;

How to create profile?

Create profile pr1 limit

Failed_login_attempts 3

Sessions_per_user 2

Idle_time 5;

How to assign the profile to users?

Alter user u1 profile pr1;

Before creating the profile first we need to set one parameter in parameter file that is resource_limit=true

For this

Step 1 : shut immediate (NOT IN REAL TIME ONLY FOR PRACTICE)


Step 2 cd $ORACLE_HOME/dbs

Step 3 vi init$ORACLE_SID.ora

Step 4 resource_limit=true

Step 5 sqlplus / as sysdba

Step 6 startup

To check which profile for user?

Select username,profile from dba_users;



  • Database level :connect to database with password
  • o/s level :connect to database without password
  • network level :connect to database remotely

how to create o/s level user?

Create user <loginid> identified externally;

Before creating this one

Wee need to set one parameter os_authent_prefix=’’

Shut immediate

Edit the parameter file

Start the database

Create user oracle identified externally;

How to drop the user?

Drop user <username>; (if user does not contains any objects)

Drop user <username> cascade; (if user contains objects)



Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (14 votes, average: 5.00 out of 5)

Add Comment