Oracle user profile setup guide

A profile is a named set of the following password and resource limits:

a.. Password aging and expiration
b.. Password history
c.. Password complexity verification
d.. Account locking
e.. CPU time
f.. Input/output (I/O) operations
g.. Idle time
h.. Connect time
i.. Memory space (private SQL area for Shared Server only)
j.. Concurrent sessions


After a profile has been created, the database administrator can assign it
to each user. If resource limits are enabled, the Oracle server limits the
database usage and resources to the defined profile of the user.

The Oracle server automatically creates a DEFAULT profile when the database
is created.

The users who have not been explicitly assigned a specific profile conform
to all the limits of the DEFAULT profile. All limits of the DEFAULT profile
are initially unlimited. However, the database administrator can change the
values so that limits are applied to all users by default.


Profile usage:
***********
a.. Restrict users from performing some operations that require heavy use
of resources.
b.. Ensure that users log off the database when they have left their
session idle for some time.
c.. Enable group resource limits for similar users.
d.. Easily assign resource limits to users.
e.. Manage resource usage in large, complex multiuser database systems.
f.. Control the use of passwords


Profile characteristics:
*******************
Profile assignments do not affect current sessions.

Profiles can be assigned only to users and not to roles or other profiles.

If you do not assign a profile when creating a user, the user is
automatically assigned the DEFAULT profile.


Password Management
-------------------------------

For greater control over database security, Oracle password management is
controlled by database administrators with profiles.

The available password management features:

Account locking: Enables automatic locking of an account when a user fails
to log in to the system in the specified number of attempts

Password aging and expiration: Enables the password to have a lifetime,
after which it expires and must be changed

Password history: Checks the new password to ensure that the password is not
reused for a specified amount of time or a specified number of password
changes

Password complexity verification: Performs a complexity check on the
password to verify that it is complex enough to provide protection against
intruders who might try to break into the system by guessing the password


Enabling Password Management
--------------------------------------

Create the profile to limit password settings, and assign the profile to the
user by using the CREATE USER or ALTER USER command.

Password limit settings in profiles are always enforced.

When password management is enabled, the user account can be locked or
unlocked by using the CREATE USER or ALTER USER command.

To enable password management, run the utlpwdmg.sql script as the user SYS.


Password Account Locking
---------------------------------

The Oracle server automatically locks an account after the
FAILED_LOGIN_ATTEMPTS value is reached. The account is either automatically
unlocked after a specified time, defined by the PASSWORD_LOCK_TIME
parameter, or it must be unlocked by the database administrator using the
ALTER USER command.

The database account can be explicitly locked with the ALTER USER command.
When this happens, the account is not automatically unlocked.


Password Expiration and Aging
--------------------------------------

The PASSWORD_LIFE_TIME parameter sets the maximum lifetime after which the
password must be changed.

The database administrator can specify a grace period PASSWORD_GRACE_TIME,
which begins after the first attempt to log in to the database after
password expiration. A warning message is generated every time the user
tries to log in until the grace period is over. The user is expected to
change the password within the grace period.

If the password is not changed, the account is locked.

The user's account status is changed to EXPIRED by explicitly setting the
password to be expired.


Password History
----------------------

Password history checks ensure that a user cannot reuse a password for a
specified time interval. These checks can be implemented by using one of the
following:


PASSWORD_REUSE_TIME: To specify that a user cannot reuse a password for a
given number of days

PASSWORD_REUSE_MAX: To force a user to define a password that is not
identical to earlier passwords

When one parameter is set to a value other than DEFAULT or UNLIMITED, the
other parameter must be set to UNLIMITED.


Password Verification
---------------------------

Before assigning a new password to a user, a PL/SQL function can be invoked
to verify the validity of the password.

The Oracle server provides a default verification routine or the database
administrator can write a PL/SQL function.


User-Provided Password Function
----------------------------------------

When a new password verification function is added, the database
administrator must consider the following restrictions:

a.. The procedure must use the specification indicated below.
b.. The procedure returns the value TRUE for success and FALSE for
failure.
c.. If the password function raises an exception, then an error is
returned and the ALTER USER or CREATE USER command is terminated.
d.. The password function is owned by SYS.
e.. If the password function becomes invalid, then an error message is
returned and the ALTER USER or CREATE USER command is terminated.


Template for the Password Function
----------------------------------------

function_name(

userid_parameter IN VARCHAR2(30),

password_parameter IN VARCHAR2(30),

old_password_parameter IN VARCHAR2(30))

RETURN BOOLEAN


Password Verification Function
---------------------------------------

The Oracle server provides a complexity verification function, in the form
of a default PL/SQL function called VERIFY_FUNCTION of the utlpwdmg.sql
script, which must be run in the SYS schema.

During the execution of the utlpwdmg.sql script, the Oracle server creates
VERIFY_FUNCTION and changes the DEFAULT profile with the following ALTER
PROFILE command:


SQL> ALTER PROFILE DEFAULT LIMIT

2 PASSWORD_LIFE_TIME 60

3 PASSWORD_GRACE_TIME 10

4 PASSWORD_REUSE_TIME 1800

5 PASSWORD_REUSE_MAX UNLIMITED

6 FAILED_LOGIN_ATTEMPTS 3

7 PASSWORD_LOCK_TIME 1/1440

8 PASSWORD_VERIFY_FUNCTION verify_function;


The default routine provides the following restrictions:


a.. Minimum length is four characters.
b.. Password should not be equal to username.
c.. Password should have at least one alphabetic, one numeric, and one
special character.
d.. Password should differ from the previous password by at least three
letters.


This is the default routine provided by Oracle in the utlpwdmg.sql file:

CREATE OR REPLACE FUNCTION verify_function

(username varchar2,

password varchar2,

old_password varchar2)

RETURN boolean IS

n boolean;

m integer;

differ integer;

isdigit boolean;

ischar boolean;

ispunct boolean;

digitarray varchar2(20);

punctarray varchar2(25);

chararray varchar2(52);

BEGIN

digitarray:= '0123456789';

chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';

punctarray:='!"#$%&()``*+,-/:;<=>?_';


-- Check if the password is same as the username

IF NLS_LOWER(password) = NLS_LOWER(username) THEN

raise_application_error(-20001, 'Password same as or similar to user');

END IF;


-- Check for the minimum length of the password

IF length(password) < 4 THEN

raise_application_error(-20002, 'Password length less than 4');

END IF;


-- Check if the password is too simple. A dictionary of words may be

-- maintained and a check may be made so as not to allow the words

-- that are too simple for the password.

IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user',
'password', 'oracle', 'computer', 'abcd') THEN

raise_application_error(-20002, 'Password too simple');

END IF;


-- Check if the password contains at least one letter, one digit and one

-- punctuation mark.

-- 1. Check for the digit

isdigit:=FALSE;

m := length(password);

FOR i IN 1..10 LOOP

FOR j IN 1..m LOOP

IF substr(password,j,1) = substr(digitarray,i,1) THEN

isdigit:=TRUE;

GOTO findchar;

END IF;

END LOOP;

END LOOP;

IF isdigit = FALSE THEN

raise_application_error(-20003, 'Password should contain at least one
digit, one character and one punctuation');

END IF;

-- 2. Check for the character

<>

ischar:=FALSE;

FOR i IN 1..length(chararray) LOOP

FOR j IN 1..m LOOP

IF substr(password,j,1) = substr(chararray,i,1) THEN

ischar:=TRUE;

GOTO findpunct;

END IF;

END LOOP;

END LOOP;

IF ischar = FALSE THEN

raise_application_error(-20003, 'Password should contain at least one
\ digit, one character and one punctuation');

END IF;

-- 3. Check for the punctuation

<>

ispunct:=FALSE;

FOR i IN 1..length(punctarray) LOOP

FOR j IN 1..m LOOP

IF substr(password,j,1) = substr(punctarray,i,1) THEN

ispunct:=TRUE;

GOTO endsearch;

END IF;

END LOOP;

END LOOP;

IF ispunct = FALSE THEN

raise_application_error(-20003, 'Password should contain at least one
\ digit, one character and one punctuation');

END IF;


<>

-- Check if the password differs from the previous password by at least

-- 3 letters

IF old_password IS NOT NULL THEN

differ := length(old_password) - length(password);


IF abs(differ) < 3 THEN

IF length(password) < length(old_password) THEN

m := length(password);

ELSE

m := length(old_password);

END IF;


differ := abs(differ);

FOR i IN 1..m LOOP

IF substr(password,i,1) != substr(old_password,i,1) THEN

differ := differ + 1;

END IF;

END LOOP;


IF differ < 3 THEN

raise_application_error(-20004, 'Password should differ by at \
least 3 characters');

END IF;

END IF;

END IF;

-- Everything is fine; return TRUE ;

RETURN(TRUE);

END;

/



Creating a Profile
-------------------------------

Use the following CREATE PROFILE command to administer passwords:

CREATE PROFILE profile LIMIT

[FAILED_LOGIN_ATTEMPTS max_value]

[PASSWORD_LIFE_TIME max_value]

[ {PASSWORD_REUSE_TIME

|PASSWORD_REUSE_MAX} max_value]

[PASSWORD_LOCK_TIME max_value]

[PASSWORD_GRACE_TIME max_value]

[PASSWORD_VERIFY_FUNCTION

{function|NULL|DEFAULT} ]


where:


PROFILE: Is the name of the profile to be created


FAILED_LOGIN_ATTEMPTS: Specifies the number of failed attempts to log in to
the user account before the account locked


PASSWORD_LIFE_TIME: Limits the number of days the same password can be used
for authentication. Password expires if not changed within this period, and
further connections are rejected


PASSWORD_REUSE_TIME: Specifies the number of days before a password can be
reused. If you set PASSWORD_REUSE_TIME to an integer value, then you must
set PASSWORD_REUSE_MAX to UNLIMITED


PASSWORD_REUSE_MAX: Specifies the number of password changes required before
the current password can be reused. If you set PASSWORD_REUSE_MAX to an
integer value, then you must set PASSWORD_REUSE_TIME to UNLIMITED


PASSWORD_LOCK_TIME: Specifies the number of days an account will be locked
after the specified number of consecutive failed login attempts


PASSWORD_GRACE_TIME: Specifies the number of days after the grace period
begins during which a warning is issued and login is allowed. Password
expires if not changed during grace period


PASSWORD_VERIFY_FUNCTION: Enables a PL/SQL password complexity verification
function to be passed as an argument to the CREATE PROFILE statement


SQL> CREATE PROFILE grace_5 LIMIT

2 FAILED_LOGIN_ATTEMPTS 3

3 PASSWORD_LOCK_TIME UNLIMITED

4 PASSWORD_LIFE_TIME 30

5 PASSWORD_REUSE_TIME 30

6 PASSWORD_VERIFY_FUNCTION verify_function

7 PASSWORD_GRACE_TIME 5;

Profile created.


SQL> create user spongebob identified by squarepants

2 default tablespace userdata01

3 quota 5m on userdata01

4 profile grace_5;

create user spongebob identified by squarepants

*

ERROR at line 1:

ORA-28003: password verification for the specified password failed

ORA-20003: Password should contain at least one digit, one character and one
punctuation


SQL> ed

Wrote file afiedt.buf

1 create user spongebob identified by square_pants2

2 default tablespace userdata01

3 quota 5m on userdata01

4* profile grace_5


SQL> /

User created.

SQL> connect spongebob/square_pants2

ERROR:

ORA-01045: user SPONGEBOB lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

SQL> connect system/manager

Connected.

SQL> grant create session to spongebob;

Grant succeeded.

SQL> grant resource to spongebob;

Grant succeeded.

SQL> connect spongebob/square_pants2

Connected.

SQL> connect spongebob/square_pants3

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> connect spongebob/square_pants4

ERROR:

ORA-01017: invalid username/password; logon denied

SQL> connect spongebob/square_pants5

ERROR:

ORA-01017: invalid username/password; logon denied

SQL> connect spongebob/square_pants2

ERROR:

ORA-28000: the account is locked

SQL> connect system/manager

Connected.

SQL> alter user spongebob account unlock;

User altered.

SQL> connect spongebob/square_pants2

Connected.


Altering a Profile
----------------------------

Use the ALTER PROFILE command to change the password limits assigned to a
profile:


ALTER PROFILE profile LIMIT

[FAILED_LOGIN_ATTEMPTS max_value]

[PASSWORD_LIFE_TIME max_value]

[ {PASSWORD_REUSE_TIME

|PASSWORD_REUSE_MAX} max_value]

[PASSWORD_LOCK_TIME max_value]

[PASSWORD_GRACE_TIME max_value]

[PASSWORD_VERIFY_FUNCTION

{function|NULL|DEFAULT} ]


If you want to set the password parameters to less than a day:

1 hour: PASSWORD_LOCK_TIME = 1/24

10 minutes: PASSWORD_LOCK_TIME = 10/1400

5 minutes: PASSWORD_LOCK_TIME = 5/1440


ALTER PROFILE default LIMIT

FAILED_LOGIN_ATTEMPTS 3

PASSWORD_LIFE_TIME 60

PASSWORD_GRACE_TIME 10;


Dropping a Profile: Password Setting
-----------------------------------------------

Drop a profile using the DROP PROFILE command:


DROP PROFILE profile [CASCADE]

where:

profile: Is the name of the profile to be dropped


CASCADE: Revokes the profile from users to whom it is assigned (The Oracle
server automatically assigns the DEFAULT profile to such users. Specify this
option to drop a profile that is currently assigned to users.)


Guidelines:

The DEFAULT profile cannot be dropped.

When a profile is dropped, this change applies only to sessions that are
created subsequently and not to the current sessions.


DROP PROFILE developer_prof;

DROP PROFILE developer_prof CASCADE;