Chapter 9 : MANAGING PASSWORD SECURITY AND RESOURCES

Chapter 9 : MANAGING PASSWORD SECURITY AND RESOURCES

CHAPTER # 9

MANAGING PASSWORD SECURITY AND RESOURCES

PROFILE:

A profile is a named set of following password and resource limit.

1.      Password aging and expiration

2.      Password History

3.      password complexity verification

4.      account locking

5.      CPU time

6.      Input/Output (I/O) operations

7.      Idle time

8.      Connect time

9.      Memory Space (Private SQL area for shared server only)

10.  Concurrent sessions.

After a profile has been created, the database administrator can assign it an each user.

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

The user who have not been explicitly assigned a specific profile confirm to all the limits of the DEFAULT profile.

All limits of DEFAULT profile are initially unlimited.

However, the DBA can change the values so that limit all applied to all users by default.

Profile can be enabled or disabled

PROFILE USAGE:

·         Restrict users from performing some operations that require heavy use of resource.

·         Control the use of password.

CHARACTERISTICS:

• Profile can be assigned only to users and not to roles or other profiles.
• Profile assignments do not effect current sessions.

Profile are assigned to user by

CREATE USER COMMAND à at the time of user created

ALTER USER COMMAND   à after creating user

PASSWORD MANAGEMNET:

For greater control over database security, oracle password management is controlled by DBA with profiles.

There are four available password management features:

1. Account locking
2. Password Aging and expiration
3. password history
4. password complexity verification

ACCOUNT LOCKING:

Enable automatic locking of an account when a user fails to log in to the system in the specified number of attempts.

FAILED_LOGIN_ATTEMPTS

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 DBA using the alter user command.

PRACTICAL #

SQL> create profile mypro limit

  2  failed_login_attempts 3

  3  password_lock_time 2;

Profile created.

NOTE:

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 DBA can specify a PASSWORD_GRACE_TIME grace period that being after the first attempt to log into the database after password expiration.

A warning massage appears every time the user tries to log in until period is over.

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

PRACTICAL #

SQL> create profile mypro1 limit

  2  password_life_time 10

  3  password_grace_time 2;

Profile created.

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: Specifies that a user cannot reuse a password for a given number of days.

PASSWORD_REUSE_MAX: Force a user to define a password that is not identical to earlier password  

PRACTICAL #

SQL> create profile mypro2 limit

  2  password_reuse_max 5;

Profile created.

NOTE:

In PASSWORD HISTORY if one parameter set a value the other parameter must be default.

PASSWORD VERIFICATION:

PASSWORD_VERIFY_FUNCTION: This is a PL/SQL function that performs a password complexity check before password is assigned.

PRACTICAL #

Create or replace function newf

(username varchar2,

 password varchar2,

old_password varchar2,

return Boolean is

begin

       if length (password) < 4 then

return false;

else

return true;

end if;

end newf;

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

1. the password function is owned by sys
2. the procedure return the value for success and false for failure.
3. the procedure must use the specification as we describe in function
4. if the password function become invalid, then an error is return and the ALTER USER and CREATE USER COMMAND is terminated.

PASSWORD VERIFICATION FUNCTION

VERIFY_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 SYS schema.

The function have the following characteristics

1.      minimum length is four characters

2.      password should not equal to username

3.      password should have at least one alphabetic, one numeric, one, special character.

4.      password should differ from the previous password by at least three letters.

PRACTICAL #

SQL> @?\rdbms\admin\utlpwdmg.sql;

Function created.

Profile altered.

The altered profile is default if we want that PASSWORD_VERIFY_FUNCTION null than again

SQL> alter profile default limit

  2  password_verify_function null;

Profile altered.

This function is used to alter DEFAULT profile and any new created profile.

CREATING A PROFILE

create profile grace_5 limit

failed_login_attempts 3

password_lock_time unlimited

password_life_time 30

password_reuse_time 30

password_verify_function verify_function

password_grace_time 5

In this profile we use oracle server complexity function.

CREATING A USER AND ASSIGNED PROFILE

SQL > create user abc identified by a1a# profile grace_5;

User created.

Then we alter this profile

First create user define function

SQL> create or replace function newf

  2  (username varchar2,

  3   password varchar2,

  4   old_password varchar2)

  5  return boolean is

  6  begin

  7  if length(password)<4 then

  8  return false;

  9  else

 10  return true;

 11  end if ;

 12  end newf;

 13  /

Function created.

SQL> alter profile grace_5 limit

  2  failed_login_attempts 2

  3  password_life_time 60

  4  password_grace_time 3;

Profile altered.

SQL> alter profile grace_5 limit

  2  password_verify_function newf;

Profile altered.

SQL> Create user khan identified by khan profile grace_5;

User created.

SQL> alter profile grace_5 limit

  2  password_verify_function null;

Profile altered.

DROPPING A PROFILE

Drop the profile using DROP PROFILE command

SQL > DROP PROFILE GRACE_5 ;

CASCADE option use to revoke the profile from the user to whom it was assigned.

SQL > DROP PROFILE GRACE_5 CASCADE;

NOTE:

• The default profile cannot be dropped
• When a profile is dropped, this change applies only to session start later.

RESOURCE MANAGEMENT

Resource management limit can be enforced at the

• Session level
• Call level
• Both

Limit can be defined by profiles using the CREATES PROFILE command

Enable resource limit with the

• Resource_limit initialization parameters
• Alter system command

ENABLE OR BISBALE RESOURCE LIMIT

Set initialization parameter

RESOURCE_LIMIT = TRUE

OR

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE

SETTING RESOURCE LIMIT AT SESSION LEVEL

CPU_PER_SESSION:

Total COU time measured in hundredths of seconds

SESSION_PER_USER:

Number of concurrent sessions allowed for each username

CONNECT_TIME:

Elapsed connect time measured in minutes

IDLE_TIME:

Periods of inactive time measured in minutes

LAGICAL_READS_PER_SESSION:

Number of data blocks (physical and logical reads)

PRIVATE_AREA:

Private space in the SGA measured in bytes (for shared server only)

PRACTICAL #

SQL> create profile pro1 limit

  2  sessions_per_user 2

  3  cpu_per_session 10000

  4  idle_time 60

  5  connect_time 480;

Profile created.

MANAGING RESOURCES USING THE

DATABASE RESOURCE MANAGER

The goal of database resource manager is to give the oracle server more control over resource management decisions.

OBTAINING PASSWORD AND RESOURCE LIMIT INFORMATION

DBA_USERS

DBA_PROFILES