Auditing an Oracle database for security issues is very important. PeteFinnigan.com provides all of the information and tools that you will need Click here for details of PeteFinnigan.com Limited's detailed Oracle database security audit service Click here for details of PeteFinnigan.com Limited's Oracle Security Training Courses
There are 76 visitors online    
Cookie Policy:We only use essential cookies on small sections of this website. For details see here.

Pete Finnigan's Oracle security weblog


Home » Archives » December 2004 » SYS.USER_ASTATUS_MAP missing values solved

[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

December 22nd, 2004 by Pete


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.

December 2004
SMTWTFS
   1234
567891011
12131415161718
19202122232425
262728293031 

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.

Weblog Home
Weblog Archives


Home
Oracle Security Tools page
Oracle security papers
Oracle Security alerts

Web Development
SQL Server Security

RSS 1.0 FEED
RSS 2.0 FEED
Atom 0.3 FEED
Powered by gm-rss 2.0.0


Valid XHTML 1.0!