Example of sending an email from PL/SQL using Oracle UTL_SMTP Package.
CREATE OR REPLACE PROCEDURE SEND_MAIL (
pSender VARCHAR2,
pRecipient VARCHAR2,UTL_SMTP
pSubject VARCHAR2,
pMessage VARCHAR2) IS
mailhost CONSTANT VARCHAR2(30) := '66.777.888.99';
crlf CONSTANT VARCHAR2(2):= CHR(13) || CHR(10);
mesg VARCHAR2(15000);
mail_conn UTL_SMTP.connection;
BEGIN
mail_conn := UTL_SMTP.open_connection(mailhost, 25);
mesg := 'Date: ' ||
TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss') || crlf ||
'From: <'|| pSender ||'>' || crlf ||
'Subject: '|| pSubject || crlf ||
'To: '||pRecipient || crlf || '' || crlf || pMessage;
UTL_SMTP.helo(mail_conn, mailhost);
UTL_SMTP.mail(mail_conn, pSender);
UTL_SMTP.rcpt(mail_conn, pRecipient);
UTL_SMTP.DATA(mail_conn, mesg);
UTL_SMTP.quit(mail_conn);
EXCEPTION
WHEN UTL_SMTP.transient_error
OR UTL_SMTP.permanent_error THEN
BEGIN
UTL_SMTP.quit(mail_conn);
EXCEPTION
WHEN UTL_SMTP.transient_error
OR UTL_SMTP.permanent_error THEN
NULL;
END;
RAISE_APPLICATION_ERROR(-20000, SQLERRM);
END SEND_MAIL ;
Thursday, October 29, 2015
Thursday, October 22, 2015
SQL for Space usage for Tablespaces and Segments
================================
SQLs
================================
View per Tablespace. Free space, Used space, Total space.
SET LINESIZE 120
SET PAGESIZE 200
spool tbs_report.txt SELECT TABLESPACE_NAME,
(MAX(MAX_SPACE)-MAX(USED_SPACE) )AS DBA_FREE_SPACE_MB,
MAX(USED_SPACE) AS USED_SPACE_MB,
MAX(MAX_SPACE) AS MAX_SPACE_MB,
ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE) ) /MAX(MAX_SPACE) ) *100) as FREE_PCT,
CASE WHEN (ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE) ) /MAX(MAX_SPACE) ) *100)<15) THEN 'Y' ELSE 'N' END AS ADD_MORE_SPACE
FROM (
SELECT tablespace_name,
ROUND(SUM(bytes)/1024/2014) AS FREE_SPACE,
0 AS MAX_SPACE,
0 AS USED_SPACE
FROM DBA_FREE_SPACE
WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name,
0 AS FREE_SPACE,
ROUND( SUM(CASE WHEN (bytes>maxbytes) THEN bytes ELSE maxbytes END)/1024/1024) AS MAX_SPACE,
0 AS USED_SPACE
FROM DBA_DATA_FILES
WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name,
0 AS FREE_SPACE,
0 AS MAX_SPACE,
ROUND(SUM(bytes/1024/1024)) AS USED_SPACE
FROM DBA_SEGMENTS
WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'
GROUP BY tablespace_name
)
GROUP BY tablespace_name;
spool off
TABLESPACE_NAME DBA_FREE_SPACE_MB USED_SPACE_MB MAX_SPACE_MB FREE_PCT ADD_SPACE
-------------------- ----------------- ------------- ------------ ---------- ---------
DWH_INDEX 179149 96851 276000 65 N
IGT_TABLE 144946 5054 150000 97 N
SYSTEM 1691 309 2000 85 N
IGT_INDEX 89590 410 90000 100 N
DWH_TABLE 519 101481 102000 1 Y
SELECT owner, tablespace_name, segment_name, USED_MB
FROM (
SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
WHERE tablespace_name NOT LIKE '%TABLE%'
AND tablespace_name NOT LIKE '%INDEX%'
GROUP BY owner, tablespace_name,segment_name
ORDER BY ROUND(SUM(bytes)/1024/1024) DESC
) WHERE ROWNUM < 21;
spool off
SQLs
================================
View per Tablespace. Free space, Used space, Total space.
SET LINESIZE 120
SET PAGESIZE 200
spool tbs_report.txt SELECT TABLESPACE_NAME,
(MAX(MAX_SPACE)-MAX(USED_SPACE) )AS DBA_FREE_SPACE_MB,
MAX(USED_SPACE) AS USED_SPACE_MB,
MAX(MAX_SPACE) AS MAX_SPACE_MB,
ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE) ) /MAX(MAX_SPACE) ) *100) as FREE_PCT,
CASE WHEN (ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE) ) /MAX(MAX_SPACE) ) *100)<15) THEN 'Y' ELSE 'N' END AS ADD_MORE_SPACE
FROM (
SELECT tablespace_name,
ROUND(SUM(bytes)/1024/2014) AS FREE_SPACE,
0 AS MAX_SPACE,
0 AS USED_SPACE
FROM DBA_FREE_SPACE
WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name,
0 AS FREE_SPACE,
ROUND( SUM(CASE WHEN (bytes>maxbytes) THEN bytes ELSE maxbytes END)/1024/1024) AS MAX_SPACE,
0 AS USED_SPACE
FROM DBA_DATA_FILES
WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name,
0 AS FREE_SPACE,
0 AS MAX_SPACE,
ROUND(SUM(bytes/1024/1024)) AS USED_SPACE
FROM DBA_SEGMENTS
WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'
GROUP BY tablespace_name
)
GROUP BY tablespace_name;
spool off
TABLESPACE_NAME DBA_FREE_SPACE_MB USED_SPACE_MB MAX_SPACE_MB FREE_PCT ADD_SPACE
-------------------- ----------------- ------------- ------------ ---------- ---------
DWH_INDEX 179149 96851 276000 65 N
IGT_TABLE 144946 5054 150000 97 N
SYSTEM 1691 309 2000 85 N
IGT_INDEX 89590 410 90000 100 N
DWH_TABLE 519 101481 102000 1 Y
View Segments per Tablespace.
SET LINESIZE 120
SET PAGESIZE 200
COL tablespace_name FOR A30
COL owner FOR A30
COL segment_name FOR A30
COL USED_MB FOR 9999999999999
spool segments_report.txt SELECT owner, tablespace_name, segment_name, USED_MB
FROM (
SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
WHERE tablespace_name LIKE '%TABLE%'
GROUP BY owner, tablespace_name,segment_name
ORDER BY ROUND(SUM(bytes)/1024/1024) DESC
) WHERE ROWNUM < 11;
SET PAGESIZE 200
COL tablespace_name FOR A30
COL owner FOR A30
COL segment_name FOR A30
COL USED_MB FOR 9999999999999
spool segments_report.txt SELECT owner, tablespace_name, segment_name, USED_MB
FROM (
SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
WHERE tablespace_name LIKE '%TABLE%'
GROUP BY owner, tablespace_name,segment_name
ORDER BY ROUND(SUM(bytes)/1024/1024) DESC
) WHERE ROWNUM < 11;
SELECT owner, tablespace_name, segment_name, USED_MB
FROM (
SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
WHERE tablespace_name LIKE '%INDEX%'
GROUP BY owner, tablespace_name,segment_name
ORDER BY ROUND(SUM(bytes)/1024/1024) DESC
) WHERE ROWNUM < 11;
FROM (
SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
WHERE tablespace_name NOT LIKE '%TABLE%'
AND tablespace_name NOT LIKE '%INDEX%'
GROUP BY owner, tablespace_name,segment_name
ORDER BY ROUND(SUM(bytes)/1024/1024) DESC
) WHERE ROWNUM < 21;
OWNER TABLESPACE_NAME SEGMENT_NAME USED_MB
------------- -------------------- ------------------------------ --------------
USER_A DWH_TABLE FACT_ROAMER_CAMPAIGNS 71428
USER_C DWH_TABLE FACT_ROAMER_SCENARIO 12890
USER_A DWH_TABLE FACT_ROAMER_CAMPAIGNS 9872
USER_C DWH_TABLE FACT_ROAMER_CAMPAIGNS 2764
USER_A IGT_TABLE SFI_CUSTOMER_PROFILE 2624
USER_E DWH_TABLE FACT_ROAMER_SCENARIO 1880
USER_B IGT_TABLE SFI_CUSTOMER_PROFILE_PK 1849
USER_A DWH_TABLE FACT_ROAMER_SCENARIO 792
USER_A DWH_TABLE SFI_CUSTOMER_OPTIONS 768
USER_A IGT_TABLE SFI_CUSTOMER_OPTION_20131010 363
10 rows selected.
View Datafiles Usage.
COL FILE_NAME FOR A50
SELECT FILE_NAME, TABLESPACE_NAME, ROUND(BYTES/1024/1024) AS Mb, ROUND(MAXBYTES/1024/1024) AS Max_Mb , ROUND(USER_BYTES/1024/1024) USER_Mb
FROM DBA_DATA_FILES
FILE_NAME TABLESPACE_NAME MB MAX_MB USER_MB
------------------------------------------- ---------------- -------- -------- --------
/oracle_db/db1/db_igt/ora_dwh_table_01.dbf DWH_TABLE 12000 12000 11999
/oracle_db/db1/db_igt/ora_dwh_table_02.dbf DWH_TABLE 30000 30000 29999
/oracle_db/db1/db_igt/ora_dwh_table_03.dbf DWH_TABLE 30000 30000 29999
/oracle_db/db1/db_igt/ora_dwh_table_04.dbf DWH_TABLE 30000 30000 29999
ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_dwh_table_01.dbf' RESIZE 30000M;
View Tempfiles Usage.
SET LINESIZE 120
SET PAGESIZE 120
COL file_name FOR A50
SELECT file_name, ROUND(BYTES/1024/1024) AS mb, ROUND(MAXBYTES/1024/1024) AS max_mb, ROUND(USER_BYTES/1024/1024) AS user_mb
FROM DBA_TEMP_FILES;
FILE_NAME MB MAX_MB USER_MB
-------------------------------------------------- ---------- ---------- ----------
/oracle_db/db1/db_igt/ora_temporary_01.dbf 30000 20000 29999
/oracle_db/db1/db_igt/ora_temporary_02.dbf 1024 10240 1023
================================
Solutions
================================
------------- -------------------- ------------------------------ --------------
USER_A DWH_TABLE FACT_ROAMER_CAMPAIGNS 71428
USER_C DWH_TABLE FACT_ROAMER_SCENARIO 12890
USER_A DWH_TABLE FACT_ROAMER_CAMPAIGNS 9872
USER_C DWH_TABLE FACT_ROAMER_CAMPAIGNS 2764
USER_A IGT_TABLE SFI_CUSTOMER_PROFILE 2624
USER_E DWH_TABLE FACT_ROAMER_SCENARIO 1880
USER_B IGT_TABLE SFI_CUSTOMER_PROFILE_PK 1849
USER_A DWH_TABLE FACT_ROAMER_SCENARIO 792
USER_A DWH_TABLE SFI_CUSTOMER_OPTIONS 768
USER_A IGT_TABLE SFI_CUSTOMER_OPTION_20131010 363
10 rows selected.
View Datafiles Usage.
COL FILE_NAME FOR A50
SELECT FILE_NAME, TABLESPACE_NAME, ROUND(BYTES/1024/1024) AS Mb, ROUND(MAXBYTES/1024/1024) AS Max_Mb , ROUND(USER_BYTES/1024/1024) USER_Mb
FROM DBA_DATA_FILES
FILE_NAME TABLESPACE_NAME MB MAX_MB USER_MB
------------------------------------------- ---------------- -------- -------- --------
/oracle_db/db1/db_igt/ora_dwh_table_01.dbf DWH_TABLE 12000 12000 11999
/oracle_db/db1/db_igt/ora_dwh_table_02.dbf DWH_TABLE 30000 30000 29999
/oracle_db/db1/db_igt/ora_dwh_table_03.dbf DWH_TABLE 30000 30000 29999
/oracle_db/db1/db_igt/ora_dwh_table_04.dbf DWH_TABLE 30000 30000 29999
ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_dwh_table_01.dbf' RESIZE 30000M;
View Tempfiles Usage.
SET LINESIZE 120
SET PAGESIZE 120
COL file_name FOR A50
SELECT file_name, ROUND(BYTES/1024/1024) AS mb, ROUND(MAXBYTES/1024/1024) AS max_mb, ROUND(USER_BYTES/1024/1024) AS user_mb
FROM DBA_TEMP_FILES;
FILE_NAME MB MAX_MB USER_MB
-------------------------------------------------- ---------- ---------- ----------
/oracle_db/db1/db_igt/ora_temporary_01.dbf 30000 20000 29999
/oracle_db/db1/db_igt/ora_temporary_02.dbf 1024 10240 1023
================================
Solutions
================================
1. Increase Tablespace
A. Resize Datafile
ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_table_01.dbf' RESIZE 30720M;
B. Add Datafile
ALTER TABLESPACE TABLE_IGT ADD DATAFILE '/oracle_db/db1/db_igt/ora_table_02.dbf' SIZE 1024M AUTOEXTEND ON MAXSIZE 10240M;
2. Increase TEMP Tablespace
A. Resize Datafile
--Resize to 30Gb
ALTER DATABASE TEMPFILE '/oracle_db/db1/db_igt/ora_temporary_01.dbf' RESIZE 30720M;
B. Add Datafile
--Add 1Gb
ALTER TABLESPACE TEMPORARY ADD TEMPFILE '/oracle_db/db1/db_igt/ora_temporary_02.dbf' SIZE 1024M AUTOEXTEND ON MAXSIZE 10240M;
3. Add Tablespace
CREATE TABLESPACE IGT_TABLE_TEMP DATAFILE '/oracle_db/db2/db_igt/datafiles/ora_igt_table_temp_01.dbf' SIZE 20M AUTOEXTEND ON MAXSIZE 30000M EXTENT MANAGEMENT LOCAL;
3. Add Tablespace
CREATE TABLESPACE IGT_TABLE_TEMP DATAFILE '/oracle_db/db2/db_igt/datafiles/ora_igt_table_temp_01.dbf' SIZE 20M AUTOEXTEND ON MAXSIZE 30000M EXTENT MANAGEMENT LOCAL;
Sunday, October 11, 2015
Oracle Audit Options
===========================
General
===========================
There are four types of Audit:
1. System Audit - for Administrative Logins.
2. Standard Audit - AKA AUDIT_TRAIL
3. Fine Grain Auditing
Where Are Standard Audit Activities Recorded?
Option 1. - in data dictionary table, AKA Database Audit Trail
Option 2. - in operating system files, AKA Operating System Audit Trail.
===========================
Audit Tables
===========================
===========================
Audit Parameters
===========================
AUDIT_TRAIL
AUDIT_SYS_OPERATIONS
AUDIT_FILE_DEST
SHOW PARAMETER AUDIT
NAME TYPE VALUE
---------------------- ----------- ------------------------------
audit_file_dest string C:\ORACLE\PRODUCT\10.2.0\ADMIN\DB10G\ADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE
AUDIT_SYS_OPERATIONS
Enable and disable SYS auditing - i.e. enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges, including the SYS user.
All AUDIT_SYS_OPERATIONS audit records are written to the OS audit trail.
AUDIT_FILE_DEST
Control the destination for OS audit files.
By default it points to $ORACLE_BASE/admin/$ORACLE_SID/adump/
OS Audit is generated by:
A. The mandatory auditing specified by the AUDIT_SYS_OPERATIONS parameter.
B. The optional auditing enabled by AUDIT_TRAIL when the os, xml and xml,extended options are used
AUDIT_TRAIL
AUDIT_TRAIL can have these values:
{ none | os | db | db,extended | xml | xml,extended }
NONE - No Auditing.
DB - Enables Auditing and directs audit records to SYS.AUD$
DB,EXTENDED - Same as DB, plus populates SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table
OS - Enables Auditing and directs audit records to an operating system file.
AUDIT_FILE_DEST control the directory for these audit files.
Default value is $ORACLE_BASE/admin/$ORACLE_SID/adump/.
XML - Same as OS, only writes to the operating system audit record file in XML format.
XML,EXTENDED - Same as DB,EXTENDED only to OS file.
Default value:
When creating the database via CLI it is NONE.
But when creating the Database via Database Configuration Assistant (DBCA) the default value is DB.
Changing AUDIT_TRAIL value
Changing AUDIT_TRAIL value by example.
After change, need to restart Instance, so the change would take effect.
SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
System altered.
SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1248600 bytes
Variable Size 71303848 bytes
Database Buffers 213909504 bytes
Redo Buffers 2945024 bytes
Database mounted.
Database opened.
===========================
login as sysdba
===========================
Logins as sysdba are audited always, even if AUDIT_TRAIL is set to NONE.
The login would generate a single file under $audit_file_dest.
For example:
root@my_server:/software/oracle/admin/igt/adump>% less igt_ora_29998_20181127060021262058143795.aud
Audit file /software/oracle/admin/igt/adump/igt_ora_29998_20181127060021262058143795.aud
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options
ORACLE_HOME = /software/oracle/112
System name: Linux
Node name: esp-tel-1-dbu-1
Release: 2.6.32-431.el6.x86_64
Version: #1 SMP Sun Nov 10 22:19:54 EST 2013
Machine: x86_64
Instance name: igt
Redo thread mounted by this instance: 1
Oracle process number: 667
Unix process pid: 29998, image: oracle@esp-tel-1-dbu-1 (TNS V1-V3)
Tue Nov 27 06:00:21 2018 +00:00
LENGTH : '155'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[0] ''
STATUS:[1] '0'
DBID:[10] '1066039690'
Fine grained auditing extends Oracle standard auditing capabilities by allowing the user to audit actions based on user-defined predicates.
It is independent of the AUDIT_TRAIL parameter.
All audit records are stored in the FGA_LOG$ table (DBA_FGA_AUDIT_TRAIL) , rather than the AUD$(DBA_AUDIT_TRAIL) table.
DBMS_FGA Package
FGA is set via DBMS_FGA package.
DBMS_FGA package contains the following procedures:
ADD_POLICY
DROP_POLICY
ENABLE_POLICY
DISABLE_POLICY
Example A.
BEGIN
DBMS_FGA.add_policy(
object_schema => 'SOME_USER',
object_name => 'SOME_USER',
policy_name => 'SALARY_CHK_AUDIT',
audit_condition => 'SAL > 50000',
audit_column => 'SAL',
statement_types => 'SELECT,INSERT,UPDATE,DELETE');
END;
/
Example B.
BEGIN
DBMS_FGA.add_policy(
object_schema => 'SOME_USER',
object_name => 'SOME_USER',
policy_name => 'SALARY_CHK_AUDIT',
audit_condition => 'SAL > 50000',
audit_column => 'SAL',
handler_schema => 'AUDIT_TEST',
handler_module => 'FIRE_CLERK', --Audit logic goes here
enable => TRUE);
END;
/
===========================
FGA_LOG$ Table
===========================
SYS.FGA_LOG$ table is never cleaned up!
Even when backing up data, and deleting old entries, the table storage space continues to grow, as with any other table with frequent INSERT and DELETE statements.
Since the owner is SYS, the table is stored in SYSTEM Tablespace.
This can result in table growing up, until it uses all of the SYSTEM Tablespace!!!
The solution is to manually execute TRUNCATE TABLE FGA_LOG$ after backing up the Audit Trail data.
===========================
SYS.AUDIT_ACTIONS Table
===========================
SYS.AUDIT_ACTIONS describes audit trail action type codes.
These values also appear in V$SESSION.command column.
This table maps action type numbers to action type names.
SELECT * FROM SYS.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
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
88 ALTER VIEW
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
130 ALTER MINING MODEL
131 SELECT MINING MODEL
133 CREATE MINING MODEL
134 ALTER PUBLIC SYNONYM
135 DIRECTORY EXECUTE
136 SQL*LOADER DIRECT PATH LOAD
137 DATAPUMP DIRECT PATH UNLOAD
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
166 ALTER 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
192 ALTER SYNONYM
197 PURGE USER_RECYCLEBIN
198 PURGE DBA_RECYCLEBIN
199 PURGE TABLESPACE
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
212 CREATE EDITION
213 ALTER EDITION
214 DROP EDITION
215 DROP ASSEMBLY
216 CREATE ASSEMBLY
217 ALTER ASSEMBLY
218 CREATE FLASHBACK ARCHIVE
219 ALTER FLASHBACK ARCHIVE
220 DROP FLASHBACK ARCHIVE
225 ALTER DATABASE LINK
305 ALTER PUBLIC DATABASE LINK
181 rows selected
===========================
Unified Auditing and AUDIT_TRAIL
===========================
If 'Unified Auditing' is set to TRUE - then values in AUDIT_TRAIL are ignored.
===========================
About Unified Auditing
===========================
About Unified Auditing
Unified Auditing is a new auditing facility in Oracle Database 12c Release 1 (12.1).
The unified audit trail, resides in a read-only table in the AUDSYS schema in the SYSAUX tablespace.
It makes this information available in an uniform format in the UNIFIED_AUDIT_TRAIL data dictionary view.
It enables you to capture audit records from a variety of sources.
When the database is writable, audit records are written to the unified audit trail.
When the database is not writable, then audit records are written to new format operating system files in $ORACLE_BASE/audit/$ORACLE_SID directory.
===========================
See what is being audited
===========================
SELECT POLICY_NAME, ENABLED from DBA_AUDIT_POLICIES;
SELECT audit_option, success, failure
FROM DBA_STMT_AUDIT_OPTS;
AUDIT_OPTION SUCCESS FAILURE
----------------------------- ---------- ---------
ALTER ANY TABLE BY ACCESS BY ACCESS
CREATE ANY TABLE BY ACCESS BY ACCESS
DROP ANY TABLE BY ACCESS BY ACCESS
CREATE ANY PROCEDURE BY ACCESS BY ACCESS
DROP ANY PROCEDURE BY ACCESS BY ACCESS
ALTER ANY PROCEDURE BY ACCESS BY ACCESS
GRANT ANY PRIVILEGE BY ACCESS BY ACCESS
GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS
GRANT ANY ROLE BY ACCESS BY ACCESS
SYSTEM AUDIT BY ACCESS BY ACCESS
CREATE EXTERNAL JOB BY ACCESS BY ACCESS
CREATE ANY JOB BY ACCESS BY ACCESS
CREATE ANY LIBRARY BY ACCESS BY ACCESS
CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS
EXEMPT ACCESS POLICY BY ACCESS BY ACCESS
ALTER USER BY ACCESS BY ACCESS
CREATE USER BY ACCESS BY ACCESS
ROLE BY ACCESS BY ACCESS
CREATE SESSION BY ACCESS BY ACCESS
===========================
Reference
===========================
General
===========================
There are four types of Audit:
1. System Audit - for Administrative Logins.
2. Standard Audit - AKA AUDIT_TRAIL
3. Fine Grain Auditing
Where Are Standard Audit Activities Recorded?
Option 1. - in data dictionary table, AKA Database Audit Trail
Option 2. - in operating system files, AKA Operating System Audit Trail.
===========================
Audit Tables
===========================
DBA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
===========================
Audit Parameters
===========================
AUDIT_TRAIL
AUDIT_SYS_OPERATIONS
AUDIT_FILE_DEST
SHOW PARAMETER AUDIT
NAME TYPE VALUE
---------------------- ----------- ------------------------------
audit_file_dest string C:\ORACLE\PRODUCT\10.2.0\ADMIN\DB10G\ADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE
AUDIT_SYS_OPERATIONS
Enable and disable SYS auditing - i.e. enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges, including the SYS user.
All AUDIT_SYS_OPERATIONS audit records are written to the OS audit trail.
AUDIT_FILE_DEST
Control the destination for OS audit files.
By default it points to $ORACLE_BASE/admin/$ORACLE_SID/adump/
OS Audit is generated by:
A. The mandatory auditing specified by the AUDIT_SYS_OPERATIONS parameter.
B. The optional auditing enabled by AUDIT_TRAIL when the os, xml and xml,extended options are used
AUDIT_TRAIL
AUDIT_TRAIL can have these values:
{ none | os | db | db,extended | xml | xml,extended }
NONE - No Auditing.
DB - Enables Auditing and directs audit records to SYS.AUD$
DB,EXTENDED - Same as DB, plus populates SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table
OS - Enables Auditing and directs audit records to an operating system file.
AUDIT_FILE_DEST control the directory for these audit files.
Default value is $ORACLE_BASE/admin/$ORACLE_SID/adump/.
XML - Same as OS, only writes to the operating system audit record file in XML format.
XML,EXTENDED - Same as DB,EXTENDED only to OS file.
Default value:
When creating the database via CLI it is NONE.
But when creating the Database via Database Configuration Assistant (DBCA) the default value is DB.
Changing AUDIT_TRAIL value
Changing AUDIT_TRAIL value by example.
After change, need to restart Instance, so the change would take effect.
SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
System altered.
SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1248600 bytes
Variable Size 71303848 bytes
Database Buffers 213909504 bytes
Redo Buffers 2945024 bytes
Database mounted.
Database opened.
===========================
How to setup Audit
===========================
CONNECT sys/password AS SYSDBA
AUDIT ALL BY some_user BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY some_user BY ACCESS;
AUDIT EXECUTE PROCEDURE BY some_user BY ACCESS;
AUDIT DELETE ANY TABLE BY ACCESS WHENEVER NOT SUCCESSFUL;
===========================
login as sysdba
===========================
Logins as sysdba are audited always, even if AUDIT_TRAIL is set to NONE.
The login would generate a single file under $audit_file_dest.
For example:
root@my_server:/software/oracle/admin/igt/adump>% less igt_ora_29998_20181127060021262058143795.aud
Audit file /software/oracle/admin/igt/adump/igt_ora_29998_20181127060021262058143795.aud
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options
ORACLE_HOME = /software/oracle/112
System name: Linux
Node name: esp-tel-1-dbu-1
Release: 2.6.32-431.el6.x86_64
Version: #1 SMP Sun Nov 10 22:19:54 EST 2013
Machine: x86_64
Instance name: igt
Redo thread mounted by this instance: 1
Oracle process number: 667
Unix process pid: 29998, image: oracle@esp-tel-1-dbu-1 (TNS V1-V3)
Tue Nov 27 06:00:21 2018 +00:00
LENGTH : '155'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[0] ''
STATUS:[1] '0'
DBID:[10] '1066039690'
===========================
View Audit Trail
===========================
The audit trail is stored in the SYS.AUD$ table.
Its contents can be viewed directly or via the following views:
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
DBA_STMT_AUDIT_OPTS
The main ones are:
DBA_AUDIT_TRAIL - Standard auditing
DBA_AUDIT_TRAIL -> Synonym to SYS.DBA_AUDIT_TRAIL view
SYS.DBA_AUDIT_TRAIL view => SELECT ... from SYS.AUD$
DBA_AUDIT_TRAIL -> Synonym to SYS.DBA_AUDIT_TRAIL view
SYS.DBA_AUDIT_TRAIL view => SELECT ... from SYS.AUD$
DBA_FGA_AUDIT_TRAIL - Fine-grained auditing only (from FGA_LOG$).
DBA_COMMON_AUDIT_TRAIL - Both standard and fine-grained auditing.
===========================
FGA - Fine Grain Auditing
===========================
It is independent of the AUDIT_TRAIL parameter.
All audit records are stored in the FGA_LOG$ table (DBA_FGA_AUDIT_TRAIL) , rather than the AUD$(DBA_AUDIT_TRAIL) table.
DBMS_FGA Package
FGA is set via DBMS_FGA package.
DBMS_FGA package contains the following procedures:
ADD_POLICY
DROP_POLICY
ENABLE_POLICY
DISABLE_POLICY
Example A.
BEGIN
DBMS_FGA.add_policy(
object_schema => 'SOME_USER',
object_name => 'SOME_USER',
policy_name => 'SALARY_CHK_AUDIT',
audit_condition => 'SAL > 50000',
audit_column => 'SAL',
statement_types => 'SELECT,INSERT,UPDATE,DELETE');
END;
/
Example B.
BEGIN
DBMS_FGA.add_policy(
object_schema => 'SOME_USER',
object_name => 'SOME_USER',
policy_name => 'SALARY_CHK_AUDIT',
audit_condition => 'SAL > 50000',
audit_column => 'SAL',
handler_schema => 'AUDIT_TEST',
handler_module => 'FIRE_CLERK', --Audit logic goes here
enable => TRUE);
END;
/
===========================
===========================
SYS.FGA_LOG$ table is never cleaned up!
Even when backing up data, and deleting old entries, the table storage space continues to grow, as with any other table with frequent INSERT and DELETE statements.
Since the owner is SYS, the table is stored in SYSTEM Tablespace.
This can result in table growing up, until it uses all of the SYSTEM Tablespace!!!
The solution is to manually execute TRUNCATE TABLE FGA_LOG$ after backing up the Audit Trail data.
===========================
SYS.AUDIT_ACTIONS Table
===========================
SYS.AUDIT_ACTIONS describes audit trail action type codes.
These values also appear in V$SESSION.command column.
This table maps action type numbers to action type names.
SELECT * FROM SYS.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
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
88 ALTER VIEW
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
130 ALTER MINING MODEL
131 SELECT MINING MODEL
133 CREATE MINING MODEL
134 ALTER PUBLIC SYNONYM
135 DIRECTORY EXECUTE
136 SQL*LOADER DIRECT PATH LOAD
137 DATAPUMP DIRECT PATH UNLOAD
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
166 ALTER 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
192 ALTER SYNONYM
197 PURGE USER_RECYCLEBIN
198 PURGE DBA_RECYCLEBIN
199 PURGE TABLESPACE
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
212 CREATE EDITION
213 ALTER EDITION
214 DROP EDITION
215 DROP ASSEMBLY
216 CREATE ASSEMBLY
217 ALTER ASSEMBLY
218 CREATE FLASHBACK ARCHIVE
219 ALTER FLASHBACK ARCHIVE
220 DROP FLASHBACK ARCHIVE
225 ALTER DATABASE LINK
305 ALTER PUBLIC DATABASE LINK
181 rows selected
===========================
Unified Auditing and AUDIT_TRAIL
===========================
Regular Oracle Auditing
Audit pre Oracle 12
Controlled by parameter AUDIT_TRAIL
SELECT value FROM V$PARAMETER WHERE name = 'audit_trail';
NONE/DB/OS/...
Unified Auditing
New configuration starting from Oracle 12
SELECT VALUE FROM V$OPTION WHERE parameter='Unified Auditing';
TRUE/FALSE
Fine Grained Auditing
SELECT VALUE FROM V$OPTION WHERE parameter='Fine-grained Auditing';
TRUE/FALSE
If 'Unified Auditing' is set to FALSE - then audit is per regular AUDIT_TRAIL setup.
===========================
About Unified Auditing
===========================
About Unified Auditing
Unified Auditing is a new auditing facility in Oracle Database 12c Release 1 (12.1).
The unified audit trail, resides in a read-only table in the AUDSYS schema in the SYSAUX tablespace.
It makes this information available in an uniform format in the UNIFIED_AUDIT_TRAIL data dictionary view.
It enables you to capture audit records from a variety of sources.
When the database is writable, audit records are written to the unified audit trail.
When the database is not writable, then audit records are written to new format operating system files in $ORACLE_BASE/audit/$ORACLE_SID directory.
===========================
See what is being audited
===========================
SELECT POLICY_NAME, ENABLED from DBA_AUDIT_POLICIES;
SELECT audit_option, success, failure
FROM DBA_STMT_AUDIT_OPTS;
AUDIT_OPTION SUCCESS FAILURE
----------------------------- ---------- ---------
ALTER ANY TABLE BY ACCESS BY ACCESS
CREATE ANY TABLE BY ACCESS BY ACCESS
DROP ANY TABLE BY ACCESS BY ACCESS
CREATE ANY PROCEDURE BY ACCESS BY ACCESS
DROP ANY PROCEDURE BY ACCESS BY ACCESS
ALTER ANY PROCEDURE BY ACCESS BY ACCESS
GRANT ANY PRIVILEGE BY ACCESS BY ACCESS
GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS
GRANT ANY ROLE BY ACCESS BY ACCESS
SYSTEM AUDIT BY ACCESS BY ACCESS
CREATE EXTERNAL JOB BY ACCESS BY ACCESS
CREATE ANY JOB BY ACCESS BY ACCESS
CREATE ANY LIBRARY BY ACCESS BY ACCESS
CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS
EXEMPT ACCESS POLICY BY ACCESS BY ACCESS
ALTER USER BY ACCESS BY ACCESS
CREATE USER BY ACCESS BY ACCESS
ROLE BY ACCESS BY ACCESS
CREATE SESSION BY ACCESS BY ACCESS
===========================
Reference
===========================
Subscribe to:
Posts (Atom)