More on COMMAND_TYPE values. January 8, 2010
Posted by mwidlake in internals.Tags: data dictionary, SQL
trackback
This is just a quick update on finding the values for COMMAND_TYPE as is held in V$SQL and V$SQLAREA. The original post is here. Quick sumary – table SYS.AUDIT_ACTIONS as an almost-complete list of COMMAND_TYPES.
In one of those little concidences of fate, I have been looking at use of SQL AUDIT for the last few days. I am not very knowledgeble about AUDIT or oracle security in general, so I do what I normally do when I don’t know much about an area. I look on the web, scan the manual and then go poking around in the data dictionary. I came across two potentially interesting tables, SYS.AUDIT_ACTIONS and SYS.STMT_AUDIT_OPTION_MAP. At first glance they look to hold similar information, things that can be audited. This seems to be what SYS.STMT_AUDIT_OPTION_MAP is:-
select * from SYS.STMT_AUDIT_OPTION_MAP
order by option#
OPTION# NAME PROPERTY
---------- ---------------------------------------- ----------
3 ALTER SYSTEM 0
4 SYSTEM AUDIT 0
5 CREATE SESSION 0
6 ALTER SESSION 0
7 RESTRICTED SESSION 0
8 TABLE 0
9 CLUSTER 0
10 CREATE TABLESPACE 0
11 ALTER TABLESPACE 0
12 MANAGE TABLESPACE 0
13 DROP TABLESPACE 0
14 TABLESPACE 0
15 UNLIMITED TABLESPACE 0
-- lets look at dba_audit_trail that underlys many of the audit views.
@vw_txt
Enter value for vw_name: dba_audit_trail
Any Key...>
OWNER VIEW_NAME TEXT_LENGTH
---------- ------------------------------ -----------
TEXT
------------------------------------------------------------------
SYS DBA_AUDIT_TRAIL 3424
select spare1 /* OS_USERNAME */,
userid /* USERNAME */,
userhost /* USERHOST */,
terminal /* TERMINAL */,
cast ( /* TIMESTAMP */
(from_tz(ntimestamp#,'00:00') at local) as date),
obj$creator /* OWNER */,
obj$name /* OBJECT_NAME */,
aud.action# /* ACTION */,
act.name /* ACTION_NAME */,
....
from sys.aud$ aud, system_privilege_map spm, system_privilege_map spx,
STMT_AUDIT_OPTION_MAP aom, audit_actions act
where aud.action# = act.action (+)
and - aud.logoff$dead = spm.privilege (+)
and aud.logoff$dead = aom.option# (+)
and - aud.priv$used = spx.privilege (+)
What about AUDIT_ACTIONS? Well, I looked at the table and thought “this looks very similar to the list of values for COMMAND_TYPE – I should check that out”.
I don’t have to. The very next day Christian Antognini posted a quick comment on the posting about COMMAND_TYPE telling me where one could find a list of these commands in the DB – Yep, AUDIT_ACTIONS.
Christian’s posting warned me that a couple of commands are missing (our old favorite from the original post MERGE being one and CREATE_DISKGROUP being the other he cites) but it includes several more that are not listed in the manual. If anyone is aware of COMMAND_TYPES not included in AUDIT_ACTIONS then let me know and I can maintain a list here.
{Thanks to Chris for providing many more missing actions and two new to 11.2, included below}
New ones to those liste in the manual are:-
128 – FLASHBACK
129 – CREATE_SESSION
197 – PURGE USER_RECYCLEBIN
198 – PURGE DBA_RECYCLEBIN
199 – PURGE TABLESAPCE
200 – PURGE TABLE
201 – PURGE INDEX
202 – UNDROP OBJECT
204 – FLASHBACK DATABASE
205 – FLASHBACK TABLE
206 – CREATE RESTORE POINT
207 – DROP RESTORE POINT
208 – PROXY AUTHENTICATION ONLY
209 – DECLARE REWRITE EQUIVALENCE
210 – ALTER REWRITE EQUIVALENCE
211 – DROP REWRITE EQUIVALENCE
and new in the table for 11.2
225 – ALTER DATABASE LINK
305 – ALTER PUBLIC DATABASE LINK
Missing codes are:-
88 – ALTER VIEW
90 – SET CONSTRAINTS
187 – CREATE SPFILE
188 – CREATE PFILE
189 – MERGE
192 – ALTER SYNONYM
193 – ALTER DISKGROUP
194 – CREATE DISKGROUP
195 – DROP DISKGROUP
XXX – FLASHBACK
Here is the list form AUDIT_ACTIONS from 10.2.0.3 (an eyeball check with 11.1.0.1 indicates the list has not increased in that version at least, which surprised me):
select * from audit_actions order by action
ACTION NAME
---------- ----------------------------
0 UNKNOWN
1 CREATE TABLE
2 INSERT
3 SELECT
4 CREATE CLUSTER
5 ALTER CLUSTER
6 UPDATE
7 DELETE
8 DROP CLUSTER
9 CREATE INDEX
10 DROP INDEX
11 ALTER INDEX
12 DROP TABLE
13 CREATE SEQUENCE
14 ALTER SEQUENCE
15 ALTER TABLE
16 DROP SEQUENCE
17 GRANT OBJECT
18 REVOKE OBJECT
19 CREATE SYNONYM
20 DROP SYNONYM
21 CREATE VIEW
22 DROP VIEW
23 VALIDATE INDEX
24 CREATE PROCEDURE
25 ALTER PROCEDURE
26 LOCK
27 NO-OP
28 RENAME
29 COMMENT
30 AUDIT OBJECT
31 NOAUDIT OBJECT
32 CREATE DATABASE LINK
33 DROP DATABASE LINK
34 CREATE DATABASE
35 ALTER DATABASE
36 CREATE ROLLBACK SEG
37 ALTER ROLLBACK SEG
38 DROP ROLLBACK SEG
39 CREATE TABLESPACE
40 ALTER TABLESPACE
41 DROP TABLESPACE
42 ALTER SESSION
43 ALTER USER
44 COMMIT
45 ROLLBACK
46 SAVEPOINT
47 PL/SQL EXECUTE
48 SET TRANSACTION
49 ALTER SYSTEM
50 EXPLAIN
51 CREATE USER
52 CREATE ROLE
53 DROP USER
54 DROP ROLE
55 SET ROLE
56 CREATE SCHEMA
57 CREATE CONTROL FILE
59 CREATE TRIGGER
60 ALTER TRIGGER
61 DROP TRIGGER
62 ANALYZE TABLE
63 ANALYZE INDEX
64 ANALYZE CLUSTER
65 CREATE PROFILE
66 DROP PROFILE
67 ALTER PROFILE
68 DROP PROCEDURE
70 ALTER RESOURCE COST
71 CREATE MATERIALIZED VIEW LOG
72 ALTER MATERIALIZED VIEW LOG
73 DROP MATERIALIZED VIEW LOG
74 CREATE MATERIALIZED VIEW
75 ALTER MATERIALIZED VIEW
76 DROP MATERIALIZED VIEW
77 CREATE TYPE
78 DROP TYPE
79 ALTER ROLE
80 ALTER TYPE
81 CREATE TYPE BODY
82 ALTER TYPE BODY
83 DROP TYPE BODY
84 DROP LIBRARY
85 TRUNCATE TABLE
86 TRUNCATE CLUSTER
91 CREATE FUNCTION
92 ALTER FUNCTION
93 DROP FUNCTION
94 CREATE PACKAGE
95 ALTER PACKAGE
96 DROP PACKAGE
97 CREATE PACKAGE BODY
98 ALTER PACKAGE BODY
99 DROP PACKAGE BODY
100 LOGON
101 LOGOFF
102 LOGOFF BY CLEANUP
103 SESSION REC
104 SYSTEM AUDIT
105 SYSTEM NOAUDIT
106 AUDIT DEFAULT
107 NOAUDIT DEFAULT
108 SYSTEM GRANT
109 SYSTEM REVOKE
110 CREATE PUBLIC SYNONYM
111 DROP PUBLIC SYNONYM
112 CREATE PUBLIC DATABASE LINK
113 DROP PUBLIC DATABASE LINK
114 GRANT ROLE
115 REVOKE ROLE
116 EXECUTE PROCEDURE
117 USER COMMENT
118 ENABLE TRIGGER
119 DISABLE TRIGGER
120 ENABLE ALL TRIGGERS
121 DISABLE ALL TRIGGERS
122 NETWORK ERROR
123 EXECUTE TYPE
128 FLASHBACK
129 CREATE SESSION
157 CREATE DIRECTORY
158 DROP DIRECTORY
159 CREATE LIBRARY
160 CREATE JAVA
161 ALTER JAVA
162 DROP JAVA
163 CREATE OPERATOR
164 CREATE INDEXTYPE
165 DROP INDEXTYPE
167 DROP OPERATOR
168 ASSOCIATE STATISTICS
169 DISASSOCIATE STATISTICS
170 CALL METHOD
171 CREATE SUMMARY
172 ALTER SUMMARY
173 DROP SUMMARY
174 CREATE DIMENSION
175 ALTER DIMENSION
176 DROP DIMENSION
177 CREATE CONTEXT
178 DROP CONTEXT
179 ALTER OUTLINE
180 CREATE OUTLINE
181 DROP OUTLINE
182 UPDATE INDEXES
183 ALTER OPERATOR
197 PURGE USER_RECYCLEBIN
198 PURGE DBA_RECYCLEBIN
199 PURGE TABLESAPCE
200 PURGE TABLE
201 PURGE INDEX
202 UNDROP OBJECT
204 FLASHBACK DATABASE
205 FLASHBACK TABLE
206 CREATE RESTORE POINT
207 DROP RESTORE POINT
208 PROXY AUTHENTICATION ONLY
209 DECLARE REWRITE EQUIVALENCE
210 ALTER REWRITE EQUIVALENCE
211 DROP REWRITE EQUIVALENCE
Hi Martin
I just checked the list I have implemented in TVD$XTAT (my profiler…). I added the following codes that do not appear in AUDIT_ACTIONS:
88 ALTER VIEW
90 SET CONSTRAINTS
187 CREATE SPFILE
188 CREATE PFILE
189 MERGE
192 ALTER SYNONYM
193 ALTER DISKGROUP
194 CREATE DISKGROUP
195 DROP DISKGROUP
In addition, in 11.2, there are two new entries:
SQL> select * from audit_actions@dba112
2 minus
3 select * from audit_actions;
ACTION NAME
———- —————————-
225 ALTER DATABASE LINK
305 ALTER PUBLIC DATABASE LINK
Cheers,
Chris
Wish I’d read these posts sooner. I had a conversation with Jeff Moss about this a few years ago …. http://www.oramoss.com/blog/2007/02/using-awr-to-summarise-sql-operations.html
[...] 10-How to get list of available command types ? Martin Widlake-More on COMMAND_TYPE values [...]