PASSWORD_REUSE_MAX and PASSWORD_REUSE_TIME

I tested this on Oracle 11.2. I created a profile called FOR_ANDREW with PASSWORD_REUSE_MAX set to 1. This meant that I could not reuse a password until I had used one other password first:

 

SQL> conn / as sysdba

Connected.

 

SQL> create profile for_andrew

  2  limit password_reuse_max 1

  3  /

 

Profile created.

 

SQL>

 

I created a user called ANDREW and gave him the FOR_ANDREW profile:

 

SQL> create user andrew

  2  identified by old_password

  3  profile for_andrew

  4  /

 

User created.

 

SQL> grant create session to andrew

  2  /

 

Grant succeeded.

 

SQL>

 

I connected to the database as user ANDREW:

 

SQL> conn andrew/old_password

Connected.

SQL>

 

I tried to reuse the existing password but this failed as I had expected it would:

 

SQL> alter user andrew

  2  identified by old_password

  3  /

alter user andrew

*

ERROR at line 1:

ORA-28007: the password cannot be reused

 

SQL>

 

I used a different password before trying to reuse the original one. This failed with the same error, which I had not expected:

 

SQL> alter user andrew

  2  identified by new_password

  3  /

 

User altered.

 

SQL> alter user andrew

  2  identified by old_password

  3  /

alter user andrew

*

ERROR at line 1:

ORA-28007: the password cannot be reused

 

SQL>

 

I did some research and saw that the PASSWORD_REUSE_TIME was set to DEFAULT. This meant that it had the same value as the DEFAULT profile where it was set to UNLIMITED:

 

SQL> conn / as sysdba

Connected.

SQL> select limit from dba_profiles

  2  where profile = 'FOR_ANDREW'

  3  and resource_name = 'PASSWORD_REUSE_TIME'

  4  /

 

LIMIT

----------------------------------------

DEFAULT

 

SQL> select limit from dba_profiles

  2  where profile = 'DEFAULT'

  3  and resource_name = 'PASSWORD_REUSE_TIME'

  4  /

 

LIMIT

----------------------------------------

UNLIMITED

 

SQL>

 

According to the Oracle 11.1 documentation:

 

These two parameters must be set in conjunction with each other. PASSWORD_REUSE_TIME specifies the number of days before which a password cannot be reused. PASSWORD_REUSE_MAX specifies the number of password changes required before the current password can be reused.

 

It then went on to say:

For these parameter to have any effect, you must specify an integer for both of them. 

... which I found a bit misleading. However, it clarified this a few lines later as follows: 

If you specify an integer for either of these parameters and specify UNLIMITED for the other, then the user can never reuse a password. 

I set PASSWORD_REUSE_TIME to 1 minute and checked that I still could not reinstate the original password:

SQL> alter profile for_andrew

  2  limit password_reuse_time 1/1440

  3  /

 

Profile altered.

 

SQL> conn andrew/new_password

Connected.

SQL> alter user andrew

  2  identified by old_password

  3  /

alter user andrew

*

ERROR at line 1:

ORA-28007: the password cannot be reused

 

SQL>

 

However, I waited for a minute and found that I could:

 

SQL> exec sys.dbms_lock.sleep(60);

 

PL/SQL procedure successfully completed.

 

SQL> alter user andrew

  2  identified by old_password

  3  /

 

User altered.

 

SQL>

 

I do not know which Oracle process controls this functionality but it seemed a bit hit and miss. When I repeated the test, I sometimes found I had to wait more than a minute before I could change the password back to its original value. 

LOGICAL_READS_PER_SESSION

One of the resources you can limit in a profile is called logical_reads_per_session. According to the Oracle documentation, it is used to:

Specify the permitted number of data blocks read in a session, including blocks read from memory and disk. I decided to try it out in an Oracle 11.1 database.

 

I created a profile called for_andrew, which would limit logical_reads_per_session to 10000:

SQL> conn / as sysdba

Connected.

SQL> create profile for_andrew

  2  limit logical_reads_per_session 10000

  3  /

 

Profile created.

 

SQL>

I set resource_limit to true so that Oracle would enforce the logical_reads_per_session limit:

SQL> alter system set resource_limit = true

  2  /

 

System altered.

 

SQL>

I created a user with the new profile:

SQL> create user andrew

  2  identified by reid

  3  profile for_andrew

  4  /

 

User created.

 

SQL> grant create session,

  2  select any dictionary to andrew

  3  /

 

Grant succeeded.

 

SQL>

I logged in with this user, ran some SQL against dba_tables and counted the session logical reads this had consumed. For the purposes of this simple demonstration, I ignored any overhead which the 2^nd piece of SQL may have incurred:

SQL> conn andrew/reid

Connected.

SQL> select max(last_analyzed) from dba_tables

  2  /

 

MAX(LAST_

---------

18-OCT-15

 

SQL> select value from v$sesstat a, v$statname b

  2  where sid =

  3  (select distinct sid from v$mystat)

  4  and a.statistic# = b.statistic#

  5  and name = 'session logical reads'

  6  /

 

       VALUE

------------

        6936

 

SQL>

I logged in again ran some SQL against dba_indexes and counted the session logical reads this had consumed. Again, I ignored any overhead which may have been incurred by checking the session logical reads figure in v$sesstat:

SQL> conn andrew/reid

Connected.

SQL> select max(last_analyzed) from dba_indexes

  2  /

 

MAX(LAST_

---------

18-OCT-15

 

SQL> select value from v$sesstat a, v$statname b

  2  where sid =

  3  (select distinct sid from v$mystat)

  4  and a.statistic# = b.statistic#

  5  and name = 'session logical reads'

  6  /

 

       VALUE

------------

        7341

 

SQL>

The 2 statements together used over 14000 session logical reads. I guessed that if I tried to run them in the same session, this would exceed the limit set by the logical_reads_per_session parameter in the user’s profile. I logged in again and tried to do this. As expected, the 1^st SQL worked but the 2^nd failed with an ORA-02394:

SQL> conn andrew/reid

Connected.

SQL> select max(last_analyzed) from dba_tables

  2  /

 

MAX(LAST_

---------

18-OCT-15

 

SQL> select max(last_analyzed) from dba_indexes

  2  /

select max(last_analyzed) from dba_indexes

                               *

ERROR at line 1:

ORA-02394: exceeded session limit on IO usage, you are

being logged off

 

SQL>

CONNECT_TIME

I had to limit a SQL*Plus session’s connection time today while I was setting up a new environment so I decided to document how I did it. The example below was run on an Oracle 11.2.0.2.7 database. First I connected as SYS and limited CONNECT_TIME to 1 minute in the DEFAULT profile: 

SQL> conn / as sysdba

Connected.

SQL> alter profile default

  2  limit connect_time 1

  3  /

 

Profile altered.

 

SQL>

 

Then I set RESOURCE_LIMIT to TRUE so that limits would be enforced:

 

SQL> alter system set resource_limit = true

  2  /

 

System altered.

 

SQL>

 

I created a user and gave it the DEFAULT profile. Then I connected as that user, looked at CONNECT_TIME in USER_RESOURCE_LIMITS then checked the time at 10 second intervals:

 

SQL> create user andrew

  2  identified by reid

  3  profile default

  4  /

 

User created.

 

SQL> grant create session to andrew

  2  /

 

Grant succeeded.

 

SQL> conn andrew/reid

Connected.

SQL> select limit from user_resource_limits

  2  where resource_name = 'CONNECT_TIME'

  3  /

 

LIMIT

----------------------------------------

1

 

SQL> select to_char(sysdate,'hh24:mi:ss')

  2  time_now from dual

  3  /

 

TIME_NOW

--------

17:31:40

 

SQL> host sleep 10

 

SQL> select to_char(sysdate,'hh24:mi:ss')

  2  time_now from dual

  3  /

 

TIME_NOW

--------

17:31:50

 

SQL> host sleep 10

 

SQL> select to_char(sysdate,'hh24:mi:ss')

  2  time_now from dual

  3  /

 

TIME_NOW

--------

17:32:00

 

SQL> host sleep 10

 

SQL> select to_char(sysdate,'hh24:mi:ss')

  2  time_now from dual

  3  /

 

TIME_NOW

--------

17:32:10

 

SQL> host sleep 10

 

SQL> select to_char(sysdate,'hh24:mi:ss')

  2  time_now from dual

  3  /

 

TIME_NOW

--------

17:32:20

 

SQL> host sleep 10

 

SQL> select to_char(sysdate,'hh24:mi:ss')

  2  time_now from dual

  3  /

 

TIME_NOW

--------

17:32:30

 

SQL> host sleep 10

 

Once the session had been connected for 1 minute, the next SQL statement failed and the session was terminated with an ORA-02399:

 

SQL> select to_char(sysdate,'hh24:mi:ss')

  2  time_now from dual

  3  /

select to_char(sysdate,'hh24:mi:ss')

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-02399: exceeded maximum connect time, you are

being logged off

ORA-02399: exceeded maximum connect time, you are

being logged off

 

SQL>

 

… and a subsequent attempt to run the SQL showed that the session was no longer connected:

 

SQL> select to_char(sysdate,'hh24:mi:ss')

  2  time_now from dual

  3  /

select to_char(sysdate,'hh24:mi:ss')

*

ERROR at line 1:

ORA-01012: not logged on

Process ID: 884

Session ID: 61 Serial number: 2851

 

SQL>

ORA-02382

This was tested on Oracle 11.2. I wanted to see if you could drop a profile if somebody was using it. First I created a profile.

SQL> create profile andrews_profile

  2  limit failed_login_attempts 3

  3  /

Profile created. 

SQL> 

Then I assigned the profile to a user:

SQL> create user andrew identified by reid

  2  profile andrews_profile

  3  /

User created.

SQL> select profile from dba_users

  2  where username = 'ANDREW'

  3  /

PROFILE

------------------------------

ANDREWS_PROFILE 

SQL> 

I tried to drop the profile but this failed as it was in use:

SQL> drop profile andrews_profile

  2  /
drop profile andrews_profile

*

ERROR at line 1:

ORA-02382: profile ANDREWS_PROFILE has users assigned,

cannot drop without CASCADE

SQL>

I ran the command again, adding CASCADE at the end:

SQL> drop profile andrews_profile cascade

  2  /

Profile dropped.

SQL>

This dropped the profile and assigned the DEFAULT profile to ANDREW instead:

SQL> select profile from dba_users

  2  where username = 'ANDREW'

  3  /

PROFILE

------------------------------

DEFAULT

SQL>

Reply to: Reply to Andrew Reid

Send

ORA-02000 and ORA-02379

This was tested on Oracle 10 on Linux. You need to include a LIMIT clause when you create a profile. If you don't, you get an ORA-02000: 
 
SQL> create profile andrews_profile
  2  /
create profile andrews_profile
                             *
ERROR at line 1:
ORA-02000: missing LIMIT keyword

SQL>

You can specify a LIMIT as follows:

SQL> create profile andrews_profile
  2  limit password_life_time 60
  3  /

Profile created.

SQL>

In the example above, the LIMIT is on the number of days you are allowed to use the same password. There are various other LIMITs you can set. I will be looking at them in other posts.

You cannot have more than one profile with a given name. If you try to do so, you get an ORA-02379:

SQL> create profile andrews_profile
  2  limit failed_login_attempts 5
  3  /
create profile andrews_profile
*
ERROR at line 1:
ORA-02379: profile ANDREWS_PROFILE already exists

SQL>

ORA-04050

This was tested on Oracle 11.2. When you create a profile, its PASSWORD_VERIFY_FUNCTION (PVF) will be shown as DEFAULT if you do not specify it explicitly:

SQL> conn / as sysdba

Connected.

SQL> show user

USER is "SYS"

SQL> create profile andrews_profile

  2  limit password_life_time 60

  3  /

Profile created.

SQL> select limit from dba_profiles

  2  where profile = 'ANDREWS_PROFILE'

  3  and resource_name = 'PASSWORD_VERIFY_FUNCTION'

  4  /

LIMIT

----------------------------------------

DEFAULT 

SQL> 

This does not mean that it has a PVF called DEFAULT. It means that it has the same PVF as the DEFAULT profile. In this database the DEFAULT profile's PVF is NULL, which means it has no PVF:

SQL> select limit from dba_profiles

  2  where profile = 'DEFAULT'

  3  and resource_name = 'PASSWORD_VERIFY_FUNCTION'

  4  /

LIMIT

----------------------------------------

NULL 

SQL> 

You can create a function to use as a PVF.  Here is a very simple example:

SQL> create or replace function andrews_verify_function(

  2    username     varchar2,

  3    password     varchar2,

  4    old_password varchar2)

  5    return boolean as

  6  begin

  7    if length(password) < 4 then

  8      return false;

  9    else

 10      return true;

 11    end if;

 12  end andrews_verify_function;

 13  /

Function created. 

SQL> 

You can make a profile use it like this:

SQL> alter profile andrews_profile limit

  2  password_verify_function andrews_verify_function

  3  /

 

Profile altered.

 

SQL>

... and the name of the function will be stored in DBA_PROFILES:

SQL> select limit from dba_profiles

  2  where profile = 'ANDREWS_PROFILE'

  3  and resource_name = 'PASSWORD_VERIFY_FUNCTION'

  4  /

 

LIMIT

----------------------------------------

ANDREWS_VERIFY_FUNCTION

 

SQL>

Then I saw a potential problem. If I had a function called DEFAULT, and a profile with PVF set to DEFAULT, how would Oracle know if I was referring to the function called DEFAULT or the PVF used by the DEFAULT profile? Fortunately the clever people at Oracle have thought of that. If you try to create a function called DEFAULT, you get an error:

SQL> create or replace function default(

  2    username     varchar2,

  3    password     varchar2,

  4    old_password varchar2)

  5    return boolean as

  6  begin

  7    if length(password) < 4 then

  8      return false;

  9    else

 10      return true;

 11    end if;

 12  end default;

 13  /

create or replace function default(

                           *

ERROR at line 1:

ORA-04050: invalid or missing procedure, function, or

package name

SQL>

Reply to: Reply to Andrew Reid

Send

Password_Verify_Function (Part 1)

This example was tested on Oracle 10 on Linux. It shows you how to create a simple password verify function (PVF). First connect as the SYS user because a PVF must be owned by SYS:

SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> 

Create a profile to use the PVF:

SQL> create profile andrews_profile
  2  limit password_life_time 60
  3  /

Profile created.

SQL>

Try to give my new profile a PVF called my_verify_function. This fails as it has not been created yet:

SQL> alter profile andrews_profile limit
  2  password_verify_function my_verify_function
  3  /
alter profile andrews_profile limit
*
ERROR at line 1:
ORA-07443: function MY_VERIFY_FUNCTION not found

SQL>

Create a function with the required name. A PVF must have the parameters shown. It should be obvious what this PVF is supposed to do:

SQL> create or replace function my_verify_function (
  2  username     varchar2,
  3  password     varchar2,
  4  old_password varchar2)
  5  return boolean as
  6  begin
  7  if length(password) < 4 then
  8    return false;
  9  else
 10    return true;
 11  end if;
 12  end my_verify_function;
 13  /

Function created.

SQL>

Change the new profile to use this PVF:

SQL> alter profile andrews_profile limit
  2  password_verify_function my_verify_function
  3  /

Profile altered.

SQL>

Create a user and assign it the new profile:

SQL> create user andrew identified by reid1
  2  profile andrews_profile
  3  /

User created.

SQL>

Change the user’s password. The new value is passed to the PVF and rejected:

SQL> alter user andrew identified by rei
  2  /
alter user andrew identified by rei
*
ERROR at line 1:
ORA-28003: password verification for the specified
password failed
ORA-28003: password verification for the specified
password failed

SQL>

The error message was not very helpful so change the PVF accordingly:

SQL> create or replace function my_verify_function (
  2  username     varchar2,
  3  password     varchar2,
  4  old_password varchar2)
  5  return boolean as
  6  begin
  7  if length(password) < 4 then
  8   raise_application_error
  9   (-20000, 'Password < 4 characters long');
 10  else
 11   return true;
 12  end if;
 13  end my_verify_function;
 14  /

Function created.

SQL>

Try to change the password again. This time the reason for rejection is clear:

SQL> alter user andrew identified by rei
  2  /
alter user andrew identified by rei
*
ERROR at line 1:
ORA-28003: password verification for the specified
password failed
ORA-20000: Password < 4 characters long

SQL>

Fix the problem and try again:

SQL> alter user andrew identified by reid2
  2  /

User altered.

SQL>