Mindmajix

Managing Oracle Database Users

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.

Privileges:

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

Roles:

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.

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.

Temporary tablespace:

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

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:

Syntax:

Sql> create user <username> identified by <password>
Default tablespace <tablespace name>
 Temporary tablespace <temporary tablespace name>
 Quota <size> on <tablespace name>
 Profile <profile name>;

Example:

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:

Syntax:

Sql>drop user <username>;

               (or)

Sql>drop user <username> cascade;

Example:

Sql>drop user john;

             (or)

Sql>drop use john cascade;

Note:

  • 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;
  1. Assigning default tablespace:
Sql>alter user john default tablespace users_det_tab;
  1. Assigning quota on particular tablespace:
Sql>alter user john quota 500m on users_det_tab;
  1. Assigning the profiles:
Sql>alter user john profile xyz;

 

 

 


 

0 Responses on Managing Oracle Database Users"

Leave a Message

Your email address will not be published. Required fields are marked *

Copy Rights Reserved © Mindmajix.com All rights reserved. Disclaimer.
Course Adviser

Fill your details, course adviser will reach you.