Call: +44 (0)1904 557620 Call
Blog

Pete Finnigan's Oracle Security Weblog

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

[Previous entry: "Database user account status's in SYS.USER_ASTATUS_MAP"] [Next entry: "Bruce Schneier talks about google desktop search security"]

SYS.USER_ASTATUS_MAP missing values solved



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.