René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -

Profiles in Oracle

Profiles are a means to limit resources a user can use.
Before profiles can be assigned, they must be created with create profile.
Then, they can be assigned to users with alter user ... profile.

Limitable resources

The following limits can be specified:

Kernel limits

  • Maximum concurrent sessions for a user
  • CPU time limit per session
  • CPU time limit per call
    Call being parse, execute and fetch
  • Maximum connect time
    The session will be dropped by oracle after specified time.
  • Maximum idle time
    The session will be dropped by oracle after specified time of doing nothing. Long running processes are not idle!
  • Maximum blocks read per session
  • Maximum blocks read per call
  • Maximum amount of SGA
  • ....
In order to enforce kernel limits, resource_limit must be set to true.

Password limits

  • Maximum failed login attempts
  • Maximum time a password is valid
  • Minimum of different passwords before password can be reused
  • Minimum of days before a password can be reused
  • Number of days an account is locked after failing to login
  • ???
  • Verify function for passwords
If a session exceeds one of these limits, Oracle will terminate the session. If there is a logoff trigger, it won't be executed.

History of passwords

In order to track password related profile limits, Oracle stores the history of passwords for a user in user_history$.

Quering created profiles

Profiles already created and their settings can be queried through dba_profiles