Pete Finnigan's Oracle Security Weblog

There was a post to the comp.databases.oracl.server newsgroup yesterday that raised an issue about creating a new account with a expired password. Then the poster immediately connected as this new user and was prompted to change the password as the account was created expired. This password change occurred and then the user tried to connect but failed with:

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

This is because when the user was created no privileges were granted. I did not agree with the poster that this is a bug or error in Oracle as was suggested in his original post. I said this in my reply which is included below.

Here is my reply:

~~~~ my post ~~~~

Hi,

I would say that this is not an error. The error you get is because the
user unnx does not have the CREATE SESSION privilege. This means he
cannot connect. This is what the error message says. This is unconnected
to changing a users password. The same example could be done as follows:

Connected to:
Personal Oracle9i Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> connect sys/a@sans as sysdba
Connected.
SQL> create user unnx identified by unnx password expire;

User created.

SQL> alter user unnx identified by unnx2;

User altered.

SQL> connect unnx/unnx2
ERROR:
ORA-01045: user UNNX lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.
SQL> connect sys/a@sans as sysdba
Connected.
SQL> grant create session to unnx;

Grant succeeded.

SQL> connect unnx/unnx2
Connected.
SQL>

Instead here the user SYS changes the user UNNX's password and then the
user UNNX tries to connect (I realise this is concocted and the purpose
of expiring the password is to make the user change it on his next
connection) and receives the "LACK OF CREATE SESSION privilege" error.
This shows that changing the password is unconnected to the lack of
create session privilege.

BUT, you are right to make people aware of this, some may assume that
the password change was rolled back (so to speak) when in fact it was
not. but not a bug in the database.

kind regards

Pete
~~~~ my post ~~~~

So why are we talking about this issue?

Two reasons really. The first is that even though this is not a bug as the original poster acknowledged it is still worth being aware of as if you create new user accounts as expired or even expire existing accounts expecting users to immediately change the passwords you need to be aware that if an error occurs after the password change its not rolled back because of the later error. This can cause confusion and rolling of passwords unnecessarily.

The second reason I wanted to talk about this is because of the issue of new accounts and protecting them based on potential use. If an account is to be used to support functionality such as a set of PL/SQL package procedures or as the schema owner for an applications objects. In these cases unlike user accounts there is generally no need for the account to be used after it has been created and the objects created in it. An account used to support PL/SQL packages can have CREATE SESSION privileges removed. The account should have an expired password and locked account. You can also create the account with an impossible password. This can be done with the ALTER USER BLAH IDENTIFIED BY VALUES command. A password that contains non 0-9A-F characters will fall into this camp. Also use password management features for these accounts and audit access to them. This should prevent direct access to the schema and also to the main functionality of your applications. This makes it easier to control change and unauthorised access.

As always use the least privilege principle to ensure users have only the privileges needed and no more. Any accounts that do not normally need to be accessed should be expired, locked and have no CREATE SESSION privileges.