To Audit a System Privilege
A system privilege is the right to perform a particular action or to perform an action on any schema objects of a particular type. For example, the privileges to create tablespaces and to delete the rows of any table in a database are system privileges. There are over 100 distinct system privileges. Each system privilege allows a user to perform a particular database operation or class of database operations. Remember that
system privileges are very powerful. Only grant them when necessary to roles and trusted users of the database.
To Audit a system privilege
SQL> audit create user, alter user, drop user by system,bhuvan;
Audit succeeded.
Note: When you doesn't specify the username, it will take it as SYSTEM schema name.
SQL> conn bhuvan
Enter password:
Connected.
SQL> drop user a cascade;
User dropped.
SQL> alter user b identified by b;
User altered.
SQL> conn / as sysdba
Connected.
-- To Find the current system privileges being audited across the system and by user
SQL> col user_name format a7
SQL> col privilege format a25
SQL> col PROXY_NAME format a5
SQL> select * from sys.dba_priv_audit_opts;
USER_NA PROXY PRIVILEGE SUCCESS FAILURE
------- ----- ------------------------- ---------- ----------
BHUVAN DROP ANY INDEX BY ACCESS BY ACCESS
BHUVAN CREATE ANY TABLE BY ACCESS BY ACCESS
BHUVAN CREATE TABLE BY ACCESS BY ACCESS
BHUVAN DROP USER BY ACCESS BY ACCESS
SYSTEM DROP USER BY ACCESS BY ACCESS
BHUVAN ALTER USER BY ACCESS BY ACCESS
SYSTEM ALTER USER BY ACCESS BY ACCESS
BHUVAN CREATE USER BY ACCESS BY ACCESS
SYSTEM CREATE USER BY ACCESS BY ACCESS
Note: I have removed the entry which are not audited by any users
To know the descriptions for audit trail action type codes
SQL> desc AUDIT_ACTIONS
Name Null? Type
----------------------------------------- -------- ----------------------------
ACTION NOT NULL NUMBER
NAME NOT NULL VARCHAR2(28)
SQL> select * from AUDIT_ACTIONS;
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
............................
219 ALTER FLASHBACK ARCHIVE
220 DROP FLASHBACK ARCHIVE
225 ALTER DATABASE LINK
305 ALTER PUBLIC DATABASE LINK
180 rows selected.
To Find the audit action code for USER related privileges
SQL> select * from AUDIT_ACTIONS where name like '%USER%';
ACTION NAME
---------- ----------------------------
43 ALTER USER
51 CREATE USER
53 DROP USER
117 USER COMMENT
197 PURGE USER_RECYCLEBIN
To Read the audit information from Audit Table
SQL> col USERNAME format a7
SQL> col USERHOST format a15
SQL> col OBJ_NAME format a10
SQL> COL RETURNCODE format 99
SQL> col PRIV_USED format a10
SQL> col EXTENDED_TIMESTAMP format a35
SQL> set linesize 10000
SQL> select USERNAME,USERHOST,EXTENDED_TIMESTAMP,OBJ_NAME, ACTION_NAME,RETURNCODE,PRIV_USED from dba_audit_trail where ACTION in(43,51,53);
USERNAM USERHOST EXTENDED_TIMESTAMP OBJ_NAME ACTION_NAME RETURNCODE PRIV_USED
------- --------------- ----------------------------------- ---------- ---------------------------- ---------- ----------
BHUVAN cnbas-ldom-022 08-JUN-11 02.31.16.913957 PM +01:00 A DROP USER 0 DROP USER
BHUVAN cnbas-ldom-022 08-JUN-11 02.31.32.604636 PM +01:00 B ALTER USER 0 ALTER USER
SQL>
Find the output
system privileges that can be audited in the Oracle database(11gR2)
SQL> select * from system_privilege_map;
PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-3 ALTER SYSTEM 0
-4 AUDIT SYSTEM 0
-5 CREATE SESSION 0
-6 ALTER SESSION 0
-7 RESTRICTED SESSION 0
-10 CREATE TABLESPACE 0
-11 ALTER TABLESPACE 0
-12 MANAGE TABLESPACE 0
-13 DROP TABLESPACE 0
-15 UNLIMITED TABLESPACE 0
-20 CREATE USER 0
-21 BECOME USER 0
-22 ALTER USER 0
-23 DROP USER 0
-30 CREATE ROLLBACK SEGMENT 0
-31 ALTER ROLLBACK SEGMENT 0
-32 DROP ROLLBACK SEGMENT 0
-40 CREATE TABLE 0
-41 CREATE ANY TABLE 0
-42 ALTER ANY TABLE 0
-43 BACKUP ANY TABLE 0
-44 DROP ANY TABLE 0
-45 LOCK ANY TABLE 0
-46 COMMENT ANY TABLE 0
-47 SELECT ANY TABLE 0
-48 INSERT ANY TABLE 0
-49 UPDATE ANY TABLE 0
-50 DELETE ANY TABLE 0
-60 CREATE CLUSTER 0
-61 CREATE ANY CLUSTER 0
-62 ALTER ANY CLUSTER 0
-63 DROP ANY CLUSTER 0
-71 CREATE ANY INDEX 0
-72 ALTER ANY INDEX 0
-73 DROP ANY INDEX 0
-80 CREATE SYNONYM 0
-81 CREATE ANY SYNONYM 0
-82 DROP ANY SYNONYM 0
-83 SYSDBA 0
-84 SYSOPER 0
-85 CREATE PUBLIC SYNONYM 0
-86 DROP PUBLIC SYNONYM 0
-90 CREATE VIEW 0
-91 CREATE ANY VIEW 0
-92 DROP ANY VIEW 0
-105 CREATE SEQUENCE 0
-106 CREATE ANY SEQUENCE 0
-107 ALTER ANY SEQUENCE 0
-108 DROP ANY SEQUENCE 0
-109 SELECT ANY SEQUENCE 0
-115 CREATE DATABASE LINK 0
-120 CREATE PUBLIC DATABASE LINK 0
-121 DROP PUBLIC DATABASE LINK 0
-125 CREATE ROLE 0
-126 DROP ANY ROLE 0
-127 GRANT ANY ROLE 0
-128 ALTER ANY ROLE 0
-130 AUDIT ANY 0
-135 ALTER DATABASE 0
-138 FORCE TRANSACTION 0
-139 FORCE ANY TRANSACTION 0
-140 CREATE PROCEDURE 0
-141 CREATE ANY PROCEDURE 0
-142 ALTER ANY PROCEDURE 0
-143 DROP ANY PROCEDURE 0
-144 EXECUTE ANY PROCEDURE 0
-151 CREATE TRIGGER 0
-152 CREATE ANY TRIGGER 0
-153 ALTER ANY TRIGGER 0
-154 DROP ANY TRIGGER 0
-160 CREATE PROFILE 0
-161 ALTER PROFILE 0
-162 DROP PROFILE 0
-163 ALTER RESOURCE COST 0
-165 ANALYZE ANY 0
-167 GRANT ANY PRIVILEGE 0
-172 CREATE MATERIALIZED VIEW 0
-173 CREATE ANY MATERIALIZED VIEW 0
-174 ALTER ANY MATERIALIZED VIEW 0
-175 DROP ANY MATERIALIZED VIEW 0
-177 CREATE ANY DIRECTORY 0
-178 DROP ANY DIRECTORY 0
-180 CREATE TYPE 0
-181 CREATE ANY TYPE 0
-182 ALTER ANY TYPE 0
-183 DROP ANY TYPE 0
-184 EXECUTE ANY TYPE 0
-186 UNDER ANY TYPE 0
-188 CREATE LIBRARY 0
-189 CREATE ANY LIBRARY 0
-190 ALTER ANY LIBRARY 0
-191 DROP ANY LIBRARY 0
-192 EXECUTE ANY LIBRARY 0
-200 CREATE OPERATOR 0
-201 CREATE ANY OPERATOR 0
-202 ALTER ANY OPERATOR 0
-203 DROP ANY OPERATOR 0
-204 EXECUTE ANY OPERATOR 0
-205 CREATE INDEXTYPE 0
-206 CREATE ANY INDEXTYPE 0
-207 ALTER ANY INDEXTYPE 0
-208 DROP ANY INDEXTYPE 0
-209 UNDER ANY VIEW 0
-210 QUERY REWRITE 0
-211 GLOBAL QUERY REWRITE 0
-212 EXECUTE ANY INDEXTYPE 0
-213 UNDER ANY TABLE 0
-214 CREATE DIMENSION 0
-215 CREATE ANY DIMENSION 0
-216 ALTER ANY DIMENSION 0
-217 DROP ANY DIMENSION 0
-218 MANAGE ANY QUEUE 1
-219 ENQUEUE ANY QUEUE 1
-220 DEQUEUE ANY QUEUE 1
-222 CREATE ANY CONTEXT 0
-223 DROP ANY CONTEXT 0
-224 CREATE ANY OUTLINE 0
-225 ALTER ANY OUTLINE 0
-226 DROP ANY OUTLINE 0
-227 ADMINISTER RESOURCE MANAGER 1
-228 ADMINISTER DATABASE TRIGGER 0
-233 MERGE ANY VIEW 0
-234 ON COMMIT REFRESH 0
-235 EXEMPT ACCESS POLICY 0
-236 RESUMABLE 0
-237 SELECT ANY DICTIONARY 0
-238 DEBUG CONNECT SESSION 0
-241 DEBUG ANY PROCEDURE 0
-243 FLASHBACK ANY TABLE 0
-244 GRANT ANY OBJECT PRIVILEGE 0
-245 CREATE EVALUATION CONTEXT 1
-246 CREATE ANY EVALUATION CONTEXT 1
-247 ALTER ANY EVALUATION CONTEXT 1
-248 DROP ANY EVALUATION CONTEXT 1
-249 EXECUTE ANY EVALUATION CONTEXT 1
-250 CREATE RULE SET 1
-251 CREATE ANY RULE SET 1
-252 ALTER ANY RULE SET 1
-253 DROP ANY RULE SET 1
-254 EXECUTE ANY RULE SET 1
-255 EXPORT FULL DATABASE 0
-256 IMPORT FULL DATABASE 0
-257 CREATE RULE 1
-258 CREATE ANY RULE 1
-259 ALTER ANY RULE 1
-260 DROP ANY RULE 1
-261 EXECUTE ANY RULE 1
-262 ANALYZE ANY DICTIONARY 0
-263 ADVISOR 0
-264 CREATE JOB 0
-265 CREATE ANY JOB 0
-266 EXECUTE ANY PROGRAM 0
-267 EXECUTE ANY CLASS 0
-268 MANAGE SCHEDULER 0
-269 SELECT ANY TRANSACTION 0
-270 DROP ANY SQL PROFILE 0
-271 ALTER ANY SQL PROFILE 0
-272 ADMINISTER SQL TUNING SET 0
-273 ADMINISTER ANY SQL TUNING SET 0
-274 CREATE ANY SQL PROFILE 0
-275 EXEMPT IDENTITY POLICY 0
-276 MANAGE FILE GROUP 1
-277 MANAGE ANY FILE GROUP 1
-278 READ ANY FILE GROUP 1
-279 CHANGE NOTIFICATION 0
-280 CREATE EXTERNAL JOB 0
-284 CREATE ASSEMBLY 0
-285 CREATE ANY ASSEMBLY 0
-286 ALTER ANY ASSEMBLY 0
-287 DROP ANY ASSEMBLY 0
-288 EXECUTE ANY ASSEMBLY 0
-289 EXECUTE ASSEMBLY 0
-281 CREATE ANY EDITION 0
-282 DROP ANY EDITION 0
-283 ALTER ANY EDITION 0
-301 CREATE CUBE DIMENSION 0
-302 ALTER ANY CUBE DIMENSION 0
-303 CREATE ANY CUBE DIMENSION 0
-304 DELETE ANY CUBE DIMENSION 0
-305 DROP ANY CUBE DIMENSION 0
-306 INSERT ANY CUBE DIMENSION 0
-307 SELECT ANY CUBE DIMENSION 0
-308 CREATE CUBE 0
-309 ALTER ANY CUBE 0
-310 CREATE ANY CUBE 0
-311 DROP ANY CUBE 0
-312 SELECT ANY CUBE 0
-313 UPDATE ANY CUBE 0
-314 CREATE MEASURE FOLDER 0
-315 CREATE ANY MEASURE FOLDER 0
-316 DELETE ANY MEASURE FOLDER 0
-317 DROP ANY MEASURE FOLDER 0
-318 INSERT ANY MEASURE FOLDER 0
-319 CREATE CUBE BUILD PROCESS 0
-320 CREATE ANY CUBE BUILD PROCESS 0
-321 DROP ANY CUBE BUILD PROCESS 0
-322 UPDATE ANY CUBE BUILD PROCESS 0
-326 UPDATE ANY CUBE DIMENSION 0
-350 FLASHBACK ARCHIVE ADMINISTER 0
-290 CREATE MINING MODEL 0
-291 CREATE ANY MINING MODEL 0
-292 DROP ANY MINING MODEL 0
-293 SELECT ANY MINING MODEL 0
-294 ALTER ANY MINING MODEL 0
-295 COMMENT ANY MINING MODEL 0
-327 ADMINISTER SQL MANAGEMENT OBJECT 0
-328 ALTER PUBLIC DATABASE LINK 0
-329 ALTER DATABASE LINK 0
208 rows selected.
AUDIT RELATED VIEWS
DBA_FGA_AUDIT_TRAIL - display the captured audit information(FGA only (from FGA_LOG$))
DBA_AUDIT_EXISTS -- displays audit trail entries produced by AUDIT EXISTS and AUDIT NOT EXISTS.
DBA_AUDIT_OBJECT -- displays audit trail records for all objects in the database.
DBA_AUDIT_POLICIES -- identify FGA audit policies
DBA_AUDIT_POLICY_COLUMNS -- Identify FGA audit policies for the columns level in FGA
DBA_AUDIT_SESSION -- display session level auditing information
DBA_AUDIT_STATEMENT -- display statement level auditing information
DBA_AUDIT_TRAIL -- display the captured audit information(Standard auditing only (from AUD$))
DBA_COMMON_AUDIT_TRAIL -- displays the captured audit information for both standard & FGA
DBA_OBJ_AUDIT_OPTS -- display any object auditing
DBA_PRIV_AUDIT_OPTS -- display any privilege auditing
DBA_STMT_AUDIT_OPTS -- display any statement auditing
DBA_AUDIT_MGMT_CLEANUP_JOBS -- Displays the currently configured audit trail purge jobs
DBA_AUDIT_MGMT_CLEAN_EVENTS -- Displays the history of purge events. Periodically, as user SYS connected with the SYSDBA privilege, you should delete the contents of this view so that it does not grow too large.
DBA_AUDIT_MGMT_CONFIG_PARAMS -- displays information about the currently configured audit trail properties that are used by the DBMS_AUDIT_MGMT PL/SQL package.
DBA_AUDIT_MGMT_LAST_ARCH_TS -- Displays the last archive timestamps that have set for audit trail purges.
V$XML_AUDIT_TRAIL -- If you are writing to an XML file, you can query V$XML_AUDIT_TRAIL.
