Pete Finnigan's Oracle Security Weblog

Thanks to an email from Gary Myers last night we have solved why the values of 3 and 7 are missing from the values listed in this table. This is rather obvious and as Gary suggested I am now kicking myself for not totally realising. I saw the connection of the first few records having numbers 0, 1, 2, 4, 8 but didn't realise the rest are just sums of these values. Let me illustrate. Firs the table contents again:

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Dec 22 14:50:20 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


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>
SQL> select * from sys.user_astatus_map;

STATUS# STATUS
---------- --------------------------------
0 OPEN
1 EXPIRED
2 EXPIRED(GRACE)
4 LOCKED(TIMED)
8 LOCKED
5 EXPIRED & LOCKED(TIMED)
6 EXPIRED(GRACE) & LOCKED(TIMED)
9 EXPIRED & LOCKED
10 EXPIRED(GRACE) & LOCKED

9 rows selected.

SQL>

OK, the status's work as follows. There are 5 basic statuses - These are

0 OPEN
1 EXPIRED
2 EXPIRED(GRACE)
4 LOCKED(TIMED)
8 LOCKED

These are the core statuses and have what look like binary values, although the column used is a number:

SQL> desc sys.user_astatus_map
Name Null? Type
----------------------------------------- -------- --------------
STATUS# NOT NULL NUMBER
STATUS NOT NULL VARCHAR2(32)

SQL>

The additional statuses are simply sums of the core status numeric identifiers. e.g:

5 EXPIRED & LOCKED(TIMED) => 1 + 4 = 5
6 EXPIRED(GRACE) & LOCKED(TIMED) => 2 + 4 = 6
9 EXPIRED & LOCKED => 1 + 8 = 9
10 EXPIRED(GRACE) & LOCKED => 2 + 8 = 10

Therefore we can now deduce why the values of 3 and 7 do not exist. The value for 3 could only be made up of:

1 (EXPIRED) + 2 (EXPIRED(GRACE)) = 3

This would not make sense as they are mutually exclusive. Next for the value of 7. This could only be made up of:

1 (EXPIRED) + 2 (EXPIRED(GRACE)) + 4 (LOCKED(TIMED)) = 7

Again the same problem with having EXPIRED and EXPIRED(GRACE) which are mutually exclusive. The same arguments could be applied to why values of 11 - 15 which would include again the same pair or LOCKED and LOCKED(TIMED) together.

Thanks again to Gary for pointing this out to me, although I should have worked it out myself.