User and Profile

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.