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>

Challenge Your Colleagues (No 1)

To set up this challenge, you need an Oracle test database. I used one running on Oracle 11.2.0.2.7 but this works on other versions too. First you need to create a user as follows:

 

SQL> create user scott identified by tiger

  2  /

 

User created.

 

SQL> grant create session,

  2  select any table,

  3  execute any procedure to scott

  4  /

 

Grant succeeded.

 

SQL>

 

Then you need to ensure that the database has the following initialization parameter set to TRUE:

 

SQL> l

  1  select value from v$parameter

  2* where name = 'O7_DICTIONARY_ACCESSIBILITY'

SQL> /

 

VALUE

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

TRUE

 

SQL>

 

Check that your colleagues will be able to connect to the database remotely using SQL*Plus:

 

SQL> conn scott/tiger@orcl

Connected.

SQL>

 

Then E-mail the challenge to them using the following text appropriately modified:

 

Dear Colleagues,

 

I would like to challenge you to reset the SYSTEM password in my test database, explain how you did it and tell me the new password. To solve this challenge, you must connect to the database from SQL*Plus as follows conn scott/tiger@orcl. Answers which involve connecting as any other user will not be allowed.

 

You have until 28^th February (or some other date) to do this. All entries must be submitted by E-mail. A prize of a bar of chocolate / bottle of champagne (according to your budget) will be awarded to the first correct answer drawn at random.

 

Good luck

 

Andrew

 

Once the deadline has passed, send out the answer below, inserting the name of the actual winner at the end and award the prize:

 

Dear Colleagues,

 

User Scott had the SELECT ANY TABLE and EXECUTE ANY PROCEDURE system privileges:

 

SQL> conn scott/tiger@orcl

Connected.

SQL> select privilege from user_sys_privs

  2  /

 

PRIVILEGE

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

CREATE SESSION

SELECT ANY TABLE

EXECUTE ANY PROCEDURE

 

SQL>

 

The database had O7_DICTIONARY_ACCESSIBILITY set to TRUE:

 

SQL> conn scott/tiger@orcl

Connected.

SQL> l

  1  select value from v$parameter

  2* where name = 'O7_DICTIONARY_ACCESSIBILITY'

SQL> /

 

VALUE

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

TRUE

 

SQL>

 

This allowed him to reset the SYSTEM password as follows, which explains why we do not allow you to have the EXECUTE ANY PROCEDURE privilege:

 

SQL> CONN SCOTT/tiger@ORCL

Connected.

SQL> DECLARE

  2  USER_NO NUMBER;

  3  CHANGE_PASSWORD VARCHAR2(50) :=

  4  'ALTER USER SYSTEM IDENTIFIED BY NEW_PASSWORD';

  5  CURSOR_NO INTEGER;

  6  BEGIN

  7  SELECT USER_ID INTO USER_NO FROM ALL_USERS

  8  WHERE USERNAME = 'SYSTEM';

  9  CURSOR_NO:=SYS.DBMS_SYS_SQL.OPEN_CURSOR();

10  SYS.DBMS_SYS_SQL.PARSE_AS_USER

11  (CURSOR_NO,CHANGE_PASSWORD,DBMS_SQL.NATIVE,USER_NO);

12  SYS.DBMS_SYS_SQL.CLOSE_CURSOR(CURSOR_NO);

13  END;

14  /

 

PL/SQL procedure successfully completed.

 

SQL> CONN SYSTEM/NEW_PASSWORD@ORCL

Connected.

SQL> SHOW USER

USER is "SYSTEM"

SQL>

 

The winner of the challenge was John Smith.

 

Kind regards,

 

Andrew

Improve your Security with Stored Procedures

Try not to grant access to tables directly to users. If you grant delete access, for example, you cannot prevent a user from deleting 1000 rows when he should only be deleting 1. It is better to write stored procedures to access your data then grant execute access on these procedures to the users. The logic in the stored procedure will then provide a further level of control over what the user does.

ORA-00972: identifier is too long

Tested on an Oracle 9 database. The maximum length of a password is 30 characters. If you try and set one longer than that, Oracle returns an ORA-00972:

 

SQL> CREATE USER A IDENTIFIED BY

  2  ABCDEFGHIJKLMNOPQRSTUVWXYZ1234

  3  /

 

User created.

 

SQL> ALTER USER A IDENTIFIED BY

  2  ABCDEFGHIJKLMNOPQRSTUVWXYZ12345

  3  /

ABCDEFGHIJKLMNOPQRSTUVWXYZ12345

*

ERROR at line 2:

ORA-00972: identifier is too long

 

SQL> CREATE USER B IDENTIFIED BY

  2  ABCDEFGHIJKLMNOPQRSTUVWXYZ12345

  3  /

ABCDEFGHIJKLMNOPQRSTUVWXYZ12345

*

ERROR at line 2:

ORA-00972: identifier is too long

 

SQL>

Create User

This example was tested on an Oracle 9 database. When you create a user, you must either specify a password or make the user externally identified. Otherwise you will get an ORA-01938:
  

SQL> create user andrew;

create user andrew

                 *

ERROR at line 1:

ORA-01938: IDENTIFIED BY must be specified for CREATE USER

SQL> create user andrew identified by reid;

User created.

SQL> drop user andrew;

User dropped.

SQL> create user andrew identified externally;

User created.

SQL>

With Admin Option

This post was tested on Oracle 9. You can grant system privileges with or without the admin option. The default is to grant the system privilege without the admin option. As a first step, grant select any dictionary to public to allow the test users to look at dba_sys_privs:
 

SQL> col grantee format a10
SQL> col privilege format a15
SQL> conn / as sysdba
Connected.
SQL> grant select any dictionary to public
  2  /

Grant succeeded.

SQL>

Now create the first test user and allow it to connect to the database:

SQL> create user a identified by a
  2  /

User created.

SQL> grant create session to a
  2  /

Grant succeeded.

SQL>

You can see if a privilege has been granted with the admin option by looking at the admin_option column in dba_sys_privs: 

SQL> desc dba_sys_privs
Name                    Null?    Type
----------------------- -------- ----------------
GRANTEE                 NOT NULL VARCHAR2(30)
PRIVILEGE               NOT NULL VARCHAR2(40)
ADMIN_OPTION                     VARCHAR2(3)

SQL> select * from  dba_sys_privs
  2  where grantee in ('A', 'B', 'C')
  3  /

GRANTEE    PRIVILEGE       ADM
---------- --------------- ---
A          CREATE SESSION  NO

SQL> 

If a user has been granted a system privilege without the admin option, he cannot grant that privilege to other users:

SQL> create user b identified by b
  2  /

User created.

SQL> conn a/a
Connected.
SQL> grant create session to b
  2  /
grant create session to b
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL>

To get round this, you need to grant the system privilege to user A with the admin option:

SQL> conn / as sysdba
Connected.
SQL> grant create session to a with admin option
  2  /

Grant succeeded.

SQL> select * from  dba_sys_privs
  2  where grantee in ('A', 'B', 'C')
  3  /

GRANTEE    PRIVILEGE       ADM
---------- --------------- ---
A          CREATE SESSION  YES

SQL> conn a/a
Connected.
SQL> grant create session to b
  2  /

Grant succeeded.

SQL> select * from  dba_sys_privs
  2  where grantee in ('A', 'B', 'C')
  3  /

GRANTEE    PRIVILEGE       ADM
---------- --------------- ---
A          CREATE SESSION  YES
B          CREATE SESSION  NO

SQL>

User A can even grant create session to user B with the admin option:

SQL> grant create session to b with admin option
  2  /

Grant succeeded.

SQL> select * from  dba_sys_privs
  2  where grantee in ('A', 'B', 'C')
  3  /

GRANTEE    PRIVILEGE       ADM
---------- --------------- ---
A          CREATE SESSION  YES
B          CREATE SESSION  YES

SQL> 

Which might not be a great idea as user B can then revoke create session from user A:

SQL> conn b/b
Connected.
SQL> revoke create session from a
  2  /

Revoke succeeded.

SQL> select * from  dba_sys_privs
  2  where grantee in ('A', 'B', 'C')
  3  /

GRANTEE    PRIVILEGE       ADM
---------- --------------- ---
B          CREATE SESSION  YES

SQL> 

If you revoke a system privilege, the users it has passed that system privilege to will retain it:

SQL> conn / as sysdba
Connected.
SQL> create user c identified by c
  2  /

User created.

SQL> conn b/b
Connected.
SQL> grant create session to c with admin option
  2  /

Grant succeeded.

SQL> conn / as sysdba
Connected.
SQL> select * from  dba_sys_privs
  2  where grantee in ('A', 'B', 'C')
  3  /

GRANTEE    PRIVILEGE       ADM
---------- --------------- ---
B          CREATE SESSION  YES
C          CREATE SESSION  YES

SQL> revoke create session from b
  2  /

Revoke succeeded.

SQL> select * from  dba_sys_privs
  2  where grantee in ('A', 'B', 'C')
  3  /

GRANTEE    PRIVILEGE       ADM
---------- --------------- ---
C          CREATE SESSION  YES

SQL> 

Finally, to revoke the with admin option, you have to revoke the system privilege and grant it again without the admin option:

SQL> conn / as sysdba
Connected.
SQL> revoke create session from c
  2  /

Revoke succeeded.

SQL> grant create session to c
  2  /

Grant succeeded.

SQL> select * from  dba_sys_privs
  2  where grantee in ('A', 'B', 'C')
  3  /

GRANTEE    PRIVILEGE       ADM
---------- --------------- ---
C          CREATE SESSION  NO

SQL>

SQL*Plus on UNIX and ps -ef

This example was tested on an Oracle 10 database running on Solaris.

Imagine you have a UNIX user called smithj. He logs into the system database user as follows:

TEST10 > whoami

smithj

TEST10 > sqlplus system/secret

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Apr 26 15:13:23 2011

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL>

However, while he is doing this, another user, e.g. bloggsf, can use the ps command to see the password for the system database user as follows:

TEST10 > whoami

bloggsf

TEST10 > ps -ef|grep sqlplus|grep -v grep

smithj 4324 29829 0 15:13:23 pts/12 0:00 sqlplus system/secret
TEST10 >

To stop this happening, smithj should login to the database with the username and wait for Oracle to prompt him for the password:

TEST10 > sqlplus system

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Apr 26 16:12:37 2011

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

Enter password:

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL>

Then bloggsf can see that smithj has logged in to the system user but cannot see the password:

TEST10 > ps -ef|grep sqlplus|grep -v grep

smithj 21306 20953 0 16:12:37 pts/12 0:00 sqlplus system

TEST10 >

Failed Login Attempts

(Tested on an Oracle 9.2.0.7.0 database.)

This can be used to limit the number of times a user can enter an incorrect password. First create a test user and show what profile he is using:

  1  grant create session to andrew

  2* identified by reid

SQL> /

Grant succeeded.

SQL> select profile from dba_users

  2  where username = 'ANDREW';

PROFILE

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

DEFAULT

SQL>

Then change that profile so that only two incorrect password attempts are allowed:

SQL> alter profile default

  2  limit failed_login_attempts 2;

Profile altered.

SQL>

To demonstrate the limit, the user must then try to login twice with the wrong password:

SQL> conn andrew/wrong_password

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn andrew/wrong_password

ERROR:

ORA-01017: invalid username/password; logon denied

SQL>

If he tries to login again he will see that the account is locked:

SQL> conn andrew/wrong_password

ERROR:

ORA-28000: the account is locked

SQL>

Note that this limit still applies even if resource_limit is set to false:

SQL> col value format a20

SQL> l

  1  select value from v$parameter

  2* where name = 'resource_limit'

SQL> /

VALUE

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

FALSE

SQL>

Also note that the failed login attempts need to be consecutive. A successful login attempt sets the count of failed login attempts back to zero:

SQL> conn / as sysdba

Connected.

SQL> alter user andrew account unlock;

User altered.

SQL> conn andrew/wrong_password

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn andrew/reid

Connected.

SQL> conn andrew/wrong_password

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn andrew/reid

Connected.

SQL> conn andrew/wrong_password

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn andrew/reid

Connected.

SQL>