Thursday, June 29, 2017

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:


Thursday, June 08, 2017

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.

Wednesday, June 07, 2017

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>

Tuesday, June 06, 2017

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 2nd role to the user:
 
SQL> grant high to andrew
  2  /
 
Grant succeeded.
 
SQL>
 
I then connected as the user and activated the 2nd role. This had the effect of also activating the 1st 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 1st role to the 2nd 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 2nd 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 2nd role but the 1st 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>

Monday, June 05, 2017

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.

Sunday, June 04, 2017

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.