Managing Oracle Database Users

  • (4.0)
  • | 1144 Ratings

Each oracle database has a list of USER NAMES. To access a database, a user must use a database application and attempt a connection with a valid user name of the database. Each user name has an associated password to prevent unauthorized use.

Each user has a security domain—a set of properties that determine such things as:

  • The actions (privileges and roles) available to the user
  • The tablespace quotas (available disk space) for the user
  • The system resource limits (for example, cpu processing time) for the user
  • Each property that contributes to a user’s security domain is discussed in the following sections.


A privilege is a right to run a particular type of sql statement. Some examples of privileges include the right to:

  • Connect to the database (create a session)
  • Create a table in your schema
  • Select rows from someone else’s table
  • Run someone else’s stored procedure


Oracle database provides for easy and controlled privilege management through roles. Roles are named groups of related privileges that you grant to users or other roles.

Storage settings and quotas:

You can direct and limit the use of disk space allocated to the database for each user, including default and temporary tablespaces and tablespace quotas.

Want To Get DBA Training From Experts? Enroll Now For Free Demo On Oracle DBA Training.

Default tablespace:

Each user is associated with a default tablespace. When a user creates a table, index, or cluster and no tablespace is specified to physically contain the schema object, the user’s default tablespace is used if the user has the privilege to create the schema object and a quota in the specified default tablespace. The default tablespace provides oracle database with information to direct space use in situations where schema object’s location is not specified.


Each user has a temporary tablespace. When a user runs a sql statement that requires the creation of temporary segments (such as the creation of an index), the user’s temporary tablespace is used. By directing all users’ temporary segments to a separate tablespace, the temporary tablespace can reduce i/o contention among temporary segments and other types of segments.

Tablespace quotas:

Oracle database can limit the collective amount of disk space available to the objects in a schema. Quotas (space limits) can be set for each tablespace available to a user. This permits selective control over the amount of disk space that can be consumed by the objects of specific schemas.

Profiles and resource limits:

Each user is assigned with a profile that specifies limitations on several system resources available to the user, including the following:

  • Number of concurrent sessions the user can establish
  • CPU processing time available for the user’s session and a single call to oracle database made by a sql statement
  • Amount of logical i/o available for the user’s session and a single call to oracle database made by a sql statement
  • Amount of idle time available for the user’s session
  • Amount of connect time available for the user’s session
  • Password restrictions:
    • Account locking after multiple unsuccessful login attempts
    • Password expiration and grace period
    • Password reuse and complexity restrictions

Checkout Oracle DBA Interview Questions

Database schema:

  • A schema is a named for a collection of objects.
  • A user is created and corresponding schema is created
  • A user can be associated only with one schema
  • Username and schema are often used interchangeably

Schema objects:

  • Tables
  • Triggers
  • Constraints
  • Indexes
  • Views
  • Sequences
  • Synonyms
  • User defined data types
  • Database links

Creation of new user:


Sql> create user  identified by 
Default tablespace 
 Temporary tablespace 
 Quota  on 
 Profile ;


Sql>create user john identified by john123
Default tablespace user_data
Temporary tablespace temp_tbs
Quota 500m on user_data
Quota 300m on temp_tbs
Quota 400m on acc_info
Profile xyz;

Dropping the user:


Sql>drop user ;


Sql>drop user  cascade;


Sql>drop user john;


Sql>drop use john cascade;


  • Dropping a user causes the user and the user schema to be immediately deleted from the database.
  • If the user has created objects within their schema, it is necessary to use the cascade option in order to drop the user

Alter user commands:

1. Changing the user password:

Sql> alter user john identified by john456;

2. Assigning default tablespace:

Sql>alter user john default tablespace users_det_tab;

3. Assigning quota on particular tablespace:

Sql>alter user john quota 500m on users_det_tab;

4. Assigning the profiles:

Sql>alter user john profile xyz;
Explore Oracle DBA Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!

Subscribe For Free Demo

Free Demo for Corporate & Online Trainings. Protection Status