ORA-02391

I tested this on an Oracle 11.1 database.

 

Oracle profiles control how certain database resources are allocated to a user session. They also define some security rules. When you create a user, it is assigned a profile and, if you do not specify it explicitly, the DEFAULT profile will be used:

 

SQL> grant create session to andrew

  2  identified by reid

  3  /

 

Grant succeeded.

 

SQL> select profile from dba_users

  2  where username = 'ANDREW'

  3  /

 

PROFILE

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

DEFAULT

 

SQL>

 

You can look at the limits defined in a profile by querying DBA_PROFILES:

 

SQL> select profile, resource_name, limit

  2  from dba_profiles

  3  where profile = 'DEFAULT'

  4  and resource_name = 'SESSIONS_PER_USER'

  5  /

 

PROFILE    RESOURCE_NAME        LIMIT

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

DEFAULT    SESSIONS_PER_USER    UNLIMITED

 

SQL>

 

The SESSIONS_PER_USER resource is set to UNLIMITED so a user with the DEFAULT profile can have as many simultaneous sessions as he wishes. What can you do if you do not like this? You can create a new profile with a different SESSIONS_PER_USER limit and assign it to the user. I will demonstrate this in a future post. Alternatively you can alter the DEFAULT profile, as shown below:

 

SQL> alter profile default

  2  limit sessions_per_user 3

  3  /

 

Profile altered.

 

SQL> select profile, resource_name, limit

  2  from dba_profiles

  3  where profile = 'DEFAULT'

  4  and resource_name = 'SESSIONS_PER_USER'

  5  /

 

PROFILE    RESOURCE_NAME        LIMIT

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

DEFAULT    SESSIONS_PER_USER    3

 

SQL>

 

You also need to set RESOURCE_LIMIT to TRUE otherwise Oracle does not check limits in a user's profile at all:

 

SQL> alter system set resource_limit = true

  2  /

 

System altered.

 

SQL>

 

The screen print below shows how this works. As usual, click on the image to enlarge it and bring it into focus if necessary. If that doesn't work, use your browser's zoom function. Three simultaneous connections have been made to the database. A fourth connection is then attempted but fails with an ORA-02391:

ORA-28010

I wondered what would happen if you tried to do a password expire on an externally identified user so I tried it out on an Oracle 11.2 database. As you might expect, it failed:

 

SQL> alter user system identified externally

  2  /

 

User altered.

 

SQL> alter user system password expire

  2  /

alter user system password expire

*

ERROR at line 1:

ORA-28010: cannot expire external or global accounts

 

SQL>

 

I guess this is because if an externally identified user had to change his password, he would not be externally identified any longer. Also, a user cannot change itself to or from external identification unless it has the ALTER USER privilege.

Password Last Change Time

I read that the PTIME column in the SYS.USER$ table shows when a user’s password was last changed so I decided to try it out in an Oracle 10 database:

SQL> SELECT VERSION FROM PRODUCT_COMPONENT_VERSION
  2  WHERE PRODUCT LIKE 'Oracle Database%'
  3  /

VERSION
--------------------
10.2.0.3.0

SQL>

I noted the time and created a user:

SQL> SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')
  2  DATE_AND_TIME1 FROM DUAL
  3  /

DATE_AND_TIME1
--------------------
06-JAN-2011 12:06:37

SQL> CREATE USER ANDREW IDENTIFIED BY REID
  2  /

SQL>

User created.

SQL>

I waited a few seconds:

SQL> EXEC DBMS_LOCK.SLEEP(5);

PL/SQL procedure successfully completed.

SQL>

I checked when the user was created and confirmed that this matched DATE_AND_TIME1:

SQL> SELECT TO_CHAR(CREATED,'DD-MON-YYYY HH24:MI:SS')
  2  USER_CREATION_TIME
  3  FROM DBA_USERS WHERE USERNAME = 'ANDREW'
  4  /

USER_CREATION_TIME
--------------------
06-JAN-2011 12:06:37

SQL>

I checked the password last change time and confirmed that this agreed with the time when the user was created:

SQL> SELECT TO_CHAR(PTIME,'DD-MON-YYYY HH24:MI:SS') PLCT
  2  FROM SYS.USER$ WHERE NAME = 'ANDREW'
  3  /

PLCT
--------------------
06-JAN-2011 12:06:37

SQL>

I noted the new time and changed the user’s password:

SQL> SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')
  2  DATE_AND_TIME2 FROM DUAL
  3  /

DATE_AND_TIME2
--------------------
06-JAN-2011 12:06:42

SQL> ALTER USER ANDREW IDENTIFIED BY NEW_PASSWORD
  2  /

User altered.

SQL>

I waited a few more seconds and noted the new time:

SQL> EXEC DBMS_LOCK.SLEEP(5);

PL/SQL procedure successfully completed.

SQL> SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')
  2  DATE_AND_TIME3 FROM DUAL
  3  /

DATE_AND_TIME3
--------------------
06-JAN-2011 12:06:47

SQL>

I checked the password last change time again and confirmed that it matched the time when the password was changed (i.e. DATE_AND_TIME2):

SQL> SELECT TO_CHAR(PTIME,'DD-MON-YYYY HH24:MI:SS') PLCT
  2  FROM SYS.USER$ WHERE NAME = 'ANDREW'
  3  /

PLCT
--------------------
06-JAN-2011 12:06:42

SQL>

ORA-28405

This post is an update to an earlier one, which I have now deleted. I tested the first part of it in an Oracle 11.1.0.6 database. First I created a role which was identified by a password:

 

SQL> conn / as sysdba

Connected.

SQL> create role low identified by secret_password

  2  /

 

Role created.

 

SQL>

 

I granted the role to a user and made sure it had no default roles:

 

SQL> grant create session, low

  2  to andrew identified by reid

  3  /

 

Grant succeeded.

 

SQL> alter user andrew default role none

  2  /

 

User altered.

 

SQL>

 

I connected as the user and tried to activate the role but this failed with an ORA-01979  as I had not supplied the password:

 

SQL> conn andrew/reid

Connected.

SQL> set role low

  2  /

set role low

*

ERROR at line 1:

ORA-01979: missing or invalid password for role 'LOW'

 

SQL> select role from session_roles

  2  /

 

no rows selected

 

SQL>

 

When I supplied the password, I was able to activate the role successfully:

 

SQL> set role low identified by secret_password

  2  /

 

Role set.

 

SQL> select role from session_roles

  2  /

 

ROLE

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

LOW

 

SQL>

 

I created another role without a password:

 

SQL> conn / as sysdba

Connected.

SQL> create role high

  2  /

 

Role created.

 

SQL>

 

… granted the first role to it:

 

SQL> grant low to high

  2  /

 

Grant succeeded.

 

SQL>

 

… and granted the 2^nd role to the user:

 

SQL> grant high to andrew

  2  /

 

Grant succeeded.

 

SQL>

 

I then connected as the user and activated the 2^nd role. This had the effect of also activating the 1^st role without needing to supply the password. This has always seemed wrong to me:

 

SQL> conn andrew/reid

Connected.

SQL> select role from session_roles

  2  /

 

no rows selected

 

SQL> set role high

  2  /

 

Role set.

 

SQL> select role from session_roles

  2  /

 

ROLE

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

HIGH

LOW

 

SQL>

 

I went to a presentation given by Simon Pane from Pythian when I was at  a UKOUG conference. He said that this behaviour changed in Oracle 11.2.0.4 so I decided to repeat the test in a database on this version:

 

SQL> conn / as sysdba

Connected.

SQL> create role low identified by secret_password

  2  /

 

Role created.

 

SQL> grant create session, low

  2  to andrew identified by reid

  3  /

 

Grant succeeded.

 

SQL> alter user andrew default role none

  2  /

 

User altered.

 

SQL> conn andrew/reid

Connected.

SQL> set role low

  2  /

set role low

*

ERROR at line 1:

ORA-01979: missing or invalid password for role 'LOW'

 

SQL> select role from session_roles

  2  /

 

no rows selected

 

SQL> set role low identified by secret_password

  2  /

 

Role set.

 

SQL> select role from session_roles

  2  /

 

ROLE

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

LOW

 

SQL> conn / as sysdba

Connected.

SQL> create role high

  2  /

 

Role created.

 

SQL>

 

It all worked as before until I tried to grant the 1^st role to the 2^nd role where this failed with an ORA-28405, which seems much more sensible to me:

 

SQL> grant low to high

  2  /

grant low to high

*

ERROR at line 1:

ORA-28405: cannot grant secure role to a role

 

SQL>

 

So when I granted the 2^nd role to the user:

 

SQL> grant high to andrew

  2  /

 

Grant succeeded.

 

SQL> conn andrew/reid

Connected.

SQL> select role from session_roles

  2  /

 

no rows selected

 

SQL>

 

… he was able to activate the 2^nd role but the 1^st one, which had the password, was not activated:

 

SQL> set role high

  2  /

 

Role set.

 

SQL> select role from session_roles

  2  /

 

ROLE

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

HIGH

 

SQL>

Password Expire

If a user forgets his password, he may ask you to reset it for him. You will then know his new password, which you may see as a security issue. By including the password expire clause in the alter user command, you can force the user to change his password the next time he logs in. After this, you will no longer know his password. The examples which follow show a DBA changing a password in red and a user logging in afterwards in green.

 

The first example shows a DBA using an Oracle 11 version of SQL*Plus to change a password in an Oracle 11 database:

TEST11 > sqlplus / as sysdba

 

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 26 11:03:51 2015

 

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> alter user a identified by b

  2  password expire

  3  /

 

User altered.

 

SQL>

The user then logs in with the same Oracle 11 version of SQL*Plus and is prompted to change his password. After doing this, he reconnects to the database. This is not necessary, it is just to show that the password change has taken effect:

TEST11 > sqlplus a/b

 

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 26 11:11:51 2015

 

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

 

ERROR:

ORA-28001: the password has expired

 

Changing password for a

New password:

Retype new password:

Password changed

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> conn a/c

Connected.

SQL>

The DBA then resets and expires the password again using the same Oracle 11 version of SQL*Plus:

TEST11 > sqlplus / as sysdba

 

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 26 11:56:10 2015

 

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> alter user a identified by b

  2  password expire

  3  /

 

User altered.

 

SQL>

The user logs in using an Oracle 10 version of SQL*Plus this time. He is prompted to change his password but is unable to do so:

TEST10 > sqlplus a/b@test11

 

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Aug 26 11:59:46 2015

 

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

 

ERROR:

ORA-28001: the password has expired

 

Changing password for a

New password:

Retype new password:

ERROR:

ORA-01017: invalid username/password; logon denied

 

Password unchanged

Enter user-name: 

So, if you want to expire a password in an Oracle 11 database, you need to check that the person who will be logging in to that user afterwards is using an Oracle 11 version of SQL*Plus, not an Oracle 10 one.

ALTER USER Hangs on row cache lock

I created the following UNIX shell script called loop1.ksh:

Oracle 11.1 > cat loop1.ksh

#!/bin/bash

export ORACLE_SID=PQEDPT1

export ORAENV_ASK=NO

. oraenv

sqlplus / as sysdba << eof

grant create session to andrew

identified by reid1

/

exit

eof

echo "User created"

./loop2.ksh > loop2.log1 &

./loop2.ksh > loop2.log2 &

./loop2.ksh > loop2.log3 &

./loop2.ksh > loop2.log4 &

./loop2.ksh > loop2.log5 &

./loop2.ksh > loop2.log6 &

./loop2.ksh > loop2.log7 &

./loop2.ksh > loop2.log8 &

./loop2.ksh > loop2.log9 &

./loop2.ksh > loop2.log10 &

Oracle 11.1 >

I ran it against an Oracle 11.1 database. It created a user called andrew with a password of reid1. It then ran another shell script called loop2.ksh 10 times. The ampersand at the end of each line made these 10 jobs run simultaneously in the background. Each run wrote its output to a different log file i.e. loop2.log1, loop2.log2 etc.

Here is the shell script loop2.ksh:

Oracle 11.1 > cat loop2.ksh

#!/bin/bash

export ORACLE_SID=PQEDPT1

export ORAENV_ASK=NO

. oraenv

for (( i=1; i <=100; i++ ))

do

echo 'i = '$i

sqlplus andrew/reid1 << eof

exit

eof

done

Oracle 11.1 >

It connected to and disconnected from the same database 100 times as user andrew. So I had 10 background jobs, each connecting to and disconnecting from the same database 100 times. Here is part of one of the output files:

DUM11.2 /export/home/oracle/andrew/hanging > cat loop2.log2|more

The Oracle base for ORACLE_HOME=/oracle/app/oracle/product/11.1.0 is /oracle/app/oracle/product

i = 1

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Oct 13 18:26:40 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

i = 2

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Oct 13 18:26:41 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

i = 3

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Oct 13 18:26:41 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

i = 4

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Oct 13 18:26:41 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

i = 5

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Oct 13 18:26:42 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

While this was going on, I tried to change the password for user andrew in a separate SQL*Plus session. This took a long time. When it had finished, I checked the wait events and saw that it had been waiting on a row cache lock:

SQL> conn / as sysdba

Connected.

SQL> set timing on

SQL> alter user andrew identified by reid2

  2  /

User altered.

Elapsed: 00:00:24.94

SQL> set timing off

SQL> select event, time_waited/100

  2  from v$session_event

  3  where sid = (select distinct sid from v$mystat)

  4  and wait_class != 'Idle'

  5  /

EVENT                               TIME_WAITED/100

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

log file sync                                     0

row cache lock                                24.34

SQL*Net message to client                         0

SQL>

So if your ALTER USER statement hangs in this way, it could be due to other sessions trying to connect to the database again and again.