User Accounts and Profile

User Accounts

For users to access your database, you must create user accounts and grant appropriate database access privileges to those accounts. A user account is identified by a user name and defines the attributes of the user, including the following:
- Authentication method
- Password (encrypted) for database authentication
- Default tablespaces for permanent and temporary data storage
- Tablespace quotas
- Account status (locked or unlocked)
- Password status (expired or not)

When you create a user account, you must not only assign a user name, a password, and default tablespaces for the account, but you must also do the following:
- Grant the appropriate system privileges, object privileges, and roles to the account.
- If the user will be creating database objects, give the user account a space usage quota on each tablespace in which the objects will be created.

Oracle recommends that you grant each user just enough privileges to perform his job, and no more.
For example, a database application developer needs privileges to create and modify tables, indexes, views, and stored procedures, but does not need (and should not be granted) privileges to drop (delete) tablespaces or recover the database. You can create user accounts for database administration, and grant only a subset of administrative privileges to those accounts. In addition, you may want to create user accounts that are used by applications only. That is, nobody logs in with these accounts; instead, applications use these accounts to connect to the database, and users log in to the applications. This avoids giving application users the ability to log in to the database directly, where they could unintentionally cause damage.

When you create a user account, you are also implicitly creating a schema for that user. A schema is a logical container for the database objects (such as tables, views, triggers, and so on) that the user creates. The schema name is the same as the user name, and can be used to unambiguously refer to objects owned by the user. For example, hr.employees refers to the table named employees in the hr schema. (The employees table is owned by hr.) The terms database object and schema object are used interchangeably.

When you delete a user, you must either simultaneously delete all schema objects of that user, or you must have previously deleted the schema objects in separate operations.


Predefined User Accounts

In addition to the user accounts that you create, the database includes a number of user accounts that are automatically created upon installation.

All databases include the administrative accounts SYS, SYSTEM, SYSMAN, and DBSNMP. Administrative accounts are highly privileged accounts, and should be used only by individuals authorized to perform administrative tasks such as starting and stopping the database, managing database memory and storage, creating and managing database users, and so on. You log in to Oracle Enterprise Manager Database Control (Database Control) with SYS, SYSTEM, or SYSMAN. The Management Agent of Database Control uses the DBSNMP account to monitor and manage the database. You assign the passwords for these accounts when you create the database with Oracle Database Configuration Assistant (DBCA). You must not delete these accounts.

All databases also include internal accounts, which are automatically created so that individual Oracle Database features or components can have their own schemas. An example is the WKSYS account, which is used by Oracle Ultra Search. All Oracle Ultra Search database objects are installed in the WKSYS schema. To protect these accounts from unauthorized access, they are initially locked and their passwords are expired. (A locked account is an account for which login is disabled.) You must not delete internal accounts, and you must not use them to log in to the database.

Your database may also include sample schemas, which are a set of interlinked schemas that enable Oracle documentation and Oracle instructional materials to illustrate common database tasks. These schemas also provide a way for you to experiment without endangering production data. Each sample schema has a user account associated with it. For example, the hr user account owns the hr schema, which contains a set of simple tables for a human resources application. The sample schema accounts are also initially locked and have an expired password. As the database administrator, you are responsible for unlocking these accounts and assigning passwords to these accounts.


Profiles

In the context of system resources, a profile is a named set of specified resource limits that can be assigned to a valid user name in Oracle Database. Profiles provide for easy management of resource limits. Profiles are also the way in which you administer password policy.
Different profiles can be created and assigned individually to each user of the database. A default profile is present for all users not explicitly assigned a profile. The resource limit feature prevents excessive consumption of global database system resources.

You must create and manage user profiles only if resource limits are a requirement of your database security policy. To use profiles, first categorize the related types of users in a database. Just as roles are used to manage the privileges of related users, profiles are used to manage the resource limits of related users. Determine how many profiles are needed to encompass all types of users in a database and then determine appropriate resource limits for each profile.
Determine Values for Resource Limits of a Profile

Before creating profiles and setting the resource limits associated with them, determine appropriate values for each resource limit. You can base these values on the type of operations a typical user performs. Usually, the best way to determine the appropriate resource limit values for a given user profile is to gather historical information about each type of resource usage.

You can gather statistics for other limits using the Monitor feature of Oracle Enterprise Manager (or SQL*Plus), specifically the Statistics monitor.


Profiles and Resource Limits

Each user is assigned 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


Understanding profiles and users

Profiles impose a named set of resource limits on database usage and instance resources. Profiles also manage the account status and place limitations on users' passwords such as length or expiration time.

Every user is assigned a profile and may belong to only one profile at any given time. If users have already logged in when you change their profile, the change does not take effect until their next login.

The DEFAULT profile serves as the basis for all other profiles.

A profile can use one of the following limitations:
- it can be implicitly specified, as in CPU/Session
- it can be unlimited, as in CPU/Call
- it can reference whatever setting is in the DEFAULT profile, as in Connect Time

Profiles cannot impose resource limitations on users unless the RESOURCE_LIMIT initialization parameter is set to TRUE.

With RESOURCE_LIMIT at its default value of FALSE, profile resource limitations are ignored. Profile password settings are always enforced. Profiles enable the administrator to control system resources, such as the
- CPU
- network/memory
- disk I/O

CPU resources may be limited on a per-session or per-call basis.

A CPU/Session limitation of 1,000 means that if any individual session that uses this profile consumes more than 10 seconds of CPU time. That session receives an error such as:

ORA-02392: exceeded session limit on CPU usage, you are being logged off

Note
CPU time limitations are in hundredths of a second.
A per-call limitation does the same thing, but instead of limiting the user's overall session, it prevents any single command from consuming too much CPU.

If CPU/Call is limited and the user exceeds the limitation, the command aborts. The user receives an error message such as:

ORA-02393: exceeded call limit on CPU usage

When using profiles to control network/memory, each database session consumes system memory resources and – if the session is from a user who is not local to the server – network resources.

When using profiles to control network/memory, you can specify the
- connect time
- idle time
- concurrent sessions
- private SGA

connect time
Connect time indicates for how many minutes a user can be connected before being automatically logged off.

idle time
Idle time indicates for how many minutes a user's session can remain idle before being automatically logged off. Idle time is calculated for the server process only. It does not take into account application activity. The IDLE_TIME limit is not affected by long-running queries and other operations.

concurrent sessions
Concurrent sessions indicates how many concurrent sessions can be created by using a database user account.

private SGA
Private SGA limits the amount of space consumed in the System Global Area (SGA) for such things as sorting and merging bitmaps. This restriction takes effect only if the session uses a shared server.

Disk I/O limits the amount of data a user can read at the per-session level or per-call level.

Reads/Session and Reads/Call place a limitation on the total number of reads from both memory and the disk. This can be done to ensure that no I/O-intensive statements overuse memory and disks.

Profiles also allow a composite limit. Composite limits are based on a weighted combination of CPU/Session, Reads/Session, Connect Time, and Private SGA.


Implementing password security features

Oracle password management is implemented with user profiles. Profiles can provide many standard security features, such as account locking, password aging and expiration, password history, and password complexity verification.

Account locking enables automatic locking of accounts for a set duration when users fail to log in to the system in the specified number of attempts.

To implement account locking, you can use
FAILED_LOGIN_ATTEMPTS which specifies the number of failed login attempts before the lockout of the account which
PASSWORD_LOCK_TIME which specifies the number of days for which the account is locked after the specified number of failed login attempts.

Password aging and expiration enables user passwords to have a lifetime, after which the passwords expire and must be changed.

PASSWORD_LIFE_TIME determines the lifetime of the password in days, after which the password expires. PASSWORD_GRACE_TIME specifies a grace period in days for changing the password after the first successful login after the password has expired.


Assigning quota to users

A quota is a space allowance in a given tablespace. By default, a user has no quota on any of the tablespaces.
You have three options for providing a quota for a user on a tablespace:
- Unlimited
- a Value
- UNLIMITED TABLESPACE system privilege

Unlimited
The Unlimited option allows the user to use as much space as is available in the tablespace.

a Value
The Value option specifies the number of kilobytes or megabytes that the user can use. This does not guarantee that the space is set aside for the user. This value can be larger or smaller than the current space that is available in the tablespace.

UNLIMITED TABLESPACE system privilege
The UNLIMITED TABLESPACE system privilege option overrides all individual tablespace quotas and gives the user unlimited quota on all tablespaces, including SYSTEM and SYSAUX. This privilege must be granted with caution.

Be aware that granting the RESOURCE role includes granting this privilege. You must not provide a quota to users on the SYSTEM or SYSAUX tablespaces. Typically, only the SYS and SYSTEM users are able to create objects in the SYSTEM or SYSAUX tablespaces.
You do not need a quota on an assigned temporary tablespace or any undo tablespaces.

The Oracle instance checks the quota when a user creates or extends a segment.
For activities that are assigned to a user schema, only those activities that use space in a tablespace count against the quota. Activities that do not use space in the assigned tablespace do not affect the quota, such as creating views or using temporary tablespaces.

The quota is replenished when objects owned by the user are dropped with the PURGE clause or when the objects owned by the user in the recycle bin are automatically purged.