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:
A privilege is a right to run a particular type of sql statement. Some examples of privileges include the right to:
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.
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.
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:
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;
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;
Get Updates on Tech posts, Interview & Certification questions and training schedules