tag:blogger.com,1999:blog-89231238094094174612024-03-14T03:19:59.613-07:00Database FundasCool stuffs which spoiled a few nights...Dwaipayanhttp://www.blogger.com/profile/06948203367115191113noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-8923123809409417461.post-7919251395657052562011-08-21T06:59:00.000-07:002011-08-21T08:04:41.890-07:00ORACLE LOCKSOracle Database always performs necessary locking to ensure data concurrency,
<br />integrity, and statement-level read consistency. You can override these default locking
<br />mechanisms. For example, you might want to override the default locking of Oracle
<br />Database if:
<br />
<br />■ You want transaction-level read consistency or "repeatable reads"—where
<br />transactions query a consistent set of data for the duration of the transaction,
<br />knowing that the data has not been changed by any other transactions. This level
<br />of consistency can be achieved by using explicit locking, read-only transactions,
<br />serializable transactions, or overriding default locking for the system.
<br />
<br />■ A transaction requires exclusive access to a resource. To proceed with its
<br />statements, the transaction with exclusive access to a resource does not have to
<br />wait for other transactions to complete.
<br />
<br /><span style="font-weight: bold;">ROW-LEVEL AND TABLE-LEVEL LOCKS:</span>
<br />
<br />DML locks are locks which are acquired automatically by Oracle to protect data in tables and indexes Whenever you issue a DML statement to modify data i.e SELECT..FOR UPDATE,INSERT, UPDATE, MERGE, and DELETE statments.DML locks prevent destructive interference of simultaneous conflicting DML or DDL operations.
<br />DML statments automatically acquire lock on two levels :
<br />
<br />i) Row-Level Locks (TX)
<br />ii)Table Level Locks(TM)
<br />
<br />These DML locks are always and only (exclusive) at row level but it can be shared as well as exclusive at table level.
<br />
<br />Here TX specify lock at row level while TM is at table level.
<br />
<br />i) Row Level Locks (TX)
<br />Row-level locks are primarily used to prevent two transactions from modifying the same row. When a transaction needs to modify a row, a row lock is acquired.Any INSERT, DELETE, UPDATE, or SELECT FOR UPDATE statements will automatically issue an exclusive lock on the rows affected by the transaction. This exclusive lock at row means that other transactions can’t modify the affected rows until the original transaction commits or rolls back, thereby releasing the exclusive locks.
<br />
<br />ii) Table Level Lock (TM)
<br />Whenever you acquire any row level lock there is ultimately a table level is also acquired to prvent others session to alter, drop this table whose rows are being modified.TM Per table locks are acquired during the execution of a transaction when referencing a table with a DML statement so that the object is not dropped or altered during the execution of the transaction.
<br />
<br />
<br />
<br /><span style="font-weight: bold;">Choosing a Locking Strategy</span> :
<br />
<br />Different Locking modes:
<br />
<br />The v$lock view has an ‘LMODE’ column which indicates the Lock mode in which the session holds the lock. The value of LMODE can vary from 0 to 6.The higher the value of LMODE, stronger is the lock. I am describing below each of these locking modes.
<br />
<br />• 0 - none
<br />• 1 - null (NULL)
<br />• 2 - row-S (RS)
<br />• 3 - row-X (RX)
<br />• 4 - share (S)
<br />• 5 - S/Row-X (SRX)
<br />• 6 - exclusive (X)
<br />
<br /><span style="font-weight: bold;">ROW SHARE </span><span style="font-weight: bold;">(RS)</span>
<br />ROW SHARE permits concurrent access to the locked table but prohibits users from locking the entire table for exclusive access.
<br />
<br /><span style="font-weight: bold;">ROW EXCLUSIVE </span><span style="font-weight: bold;">(RX)</span>
<br />ROW EXCLUSIVE is the same as ROW SHARE, but it also prohibits locking in SHARE mode. ROW EXCLUSIVE locks are automatically obtained when updating, inserting, or deleting.
<br />The following statements lock a table in ROW-EXCLUSIVE mode.
<br />INSERT INTO table ... ;
<br />
<br />UPDATE table ... ; (see example 1).
<br />
<br />DELETE FROM table ... ;
<br />
<br />LOCK TABLE table IN ROW EXCLUSIVE MODE; (For notes on usage of LOCK TABLE see Example 2).
<br />
<br /><span style="font-weight: bold;">SHARE</span> <span style="font-weight: bold;">(S)</span>
<br />SHARE permits concurrent queries but prohibits updates to the locked table.
<br />
<br /><span style="font-weight: bold;">SHARE ROW EXCLUSIVE </span><span style="font-weight: bold;">(SRX)</span>
<br />SHARE ROW EXCLUSIVE is used to look at a whole table and to allow others to look at rows in the table but to prohibit others from locking the table in SHARE mode or from updating rows.
<br />
<br /><span style="font-weight: bold;">EXCLUSIVE </span><span style="font-weight: bold;">(E)</span>
<br />EXCLUSIVE permits queries on the locked table but prohibits any other activity on it.
<br />
<br />I am giving below the compatibility matrix of the different locking modes:
<br />
<br /><table>
<br /><tbody><tr><td style="font-weight: bold;">compatible</td> <td style="font-weight: bold;">RS</td><td> <span style="font-weight: bold;">RX</span> </td><td><span style="font-weight: bold;">S</span> </td><td style="font-weight: bold;">SRX</td><td style="font-weight: bold;">X</td></tr>
<br /><tr><td style="font-weight: bold;">RS</td><td> yes</td><td>yes</td><td>yes</td><td>yes</td><td>no</td></tr>
<br /><tr><td style="font-weight: bold;">RX</td><td>yes</td><td> yes</td><td> no</td><td> no</td><td>no</td></tr>
<br /><tr><td style="font-weight: bold;">S</td><td> yes</td><td> no</td><td> yes</td><td> no</td> <td>no</td></tr>
<br /><tr><td style="font-weight: bold;">SRX</td><td> yes</td><td> no</td><td> no</td><td> no</td><td> no</td></tr>
<br /><tr><td style="font-weight: bold;">X</td><td> no</td><td> no</td> <td>no</td><td> no</td><td> no</td></tr>
<br /></tbody></table>
<br />
<br />Lets try out an example where we will update the salary column of the EMP table for a particular employee and use the V$LOCK view to check the corresponding locks it acquires:
<br />
<br /><pre><span style="font-weight: bold;">Example 1.1:</span>
<br />
<br />SESSION 1:
<br />
<br />SQL> update emp set salary=salary+100 where empno=1;
<br />
<br />1 row updated.
<br />
<br />SQL> select a.sid,a.type,a.lmode,a.request from v$lock a,v$session b where a.sid=b.sid
<br /> and b.username='SCOTT';
<br />
<br /> SID TY LMODE REQUEST
<br />---------- -- ---------- ----------
<br /> 162 TX 6 0
<br /> 162 TM 3 0
<br />
<br />So It acquires an EXCLUSIVE row-level(TX) lock to the row it updates and a ROW-EXCLUSIVE table-level(TM) lock on the EMP table.
<br />
<br />The session has not committed yet.
<br />
<br />Session 2:
<br />
<br />Now lets say from another session, the user wants to drop the table EMP which would require an EXCLUSIVE LOCK on the EMP table.
<br />
<br />SQL> DROP TABLE EMP;
<br />DROP TABLE EMP
<br /> *
<br />ERROR at line 1:
<br />ORA-00054: resource busy and acquire with NOWAIT specified.
<br />
<br />It throws an error because the an EXCLUSIVE lock is denied on EMP as another session has already acquired a ROW-EXCLUSIVE(RX) lock on EMP and RX is not compatible with EXCLUSIVE(E) mode(see the compatibility matrix).
<br />
<br />To release the locks, session 1 simply needs to commit or rollback.
<br /></pre>
<br />
<br /><pre><span style="font-weight: bold;">EXAMPLE 1.2:</span>
<br />
<br />Now the session 1 has not yet committed/Rolled back and is still holding the locks.
<br />
<br />Session 1:
<br />
<br />SQL> select a.sid,a.type,a.lmode,a.request from v$lock a,v$session b where a.sid=b.sid
<br /> and b.username='SCOTT';
<br />
<br /> SID TY LMODE REQUEST
<br />---------- -- ---------- ----------
<br /> 162 TX 6 0
<br /> 162 TM 3 0
<br />
<br />Session 2:
<br />
<br />The user wants to update the salary of the particular employee with employee id 2.
<br />
<br />SQL> update emp set salary=salary+100 where empno=2;
<br />
<br />1 row updated.
<br />The transaction is allowed.
<br />Both the session have not yet committed/Rolled back. Now lets find out the locks they are holding.
<br />
<br />SQL> select a.sid,a.type,a.lmode,a.request from v$lock a,v$session b where a.sid=b.sid
<br />2 and b.username='SCOTT';
<br />
<br /> SID TY LMODE REQUEST
<br />---------- -- ---------- ----------
<br /> 149 TX 6 0
<br /> 149 TM 3 0
<br /> 162 TX 6 0
<br /> 162 TM 3 0
<br />
<br />As we can see,So both the sessions are holding RX locks on the table level
<br />and EXCLUSIVE locks at row level (but at different rows). As RX is compatible
<br />with RX and their EXCLUSIVE locks are at different rows , so both their TX and
<br />TM locks are compatible and hence the transactions are allowed.
<br /></pre>
<br />
<br /><span style="font-weight: bold;">Lock table Statement :</span>
<br />
<br />A LOCK TABLE statement manually overrides default locking.
<br />When a LOCK TABLE statement is issued on a view, the underlying base tables are
<br />locked. The following statement acquires exclusive table locks for the
<br />EMP table on behalf of the containing transaction:
<br />
<br /><pre><span style="font-weight: bold;">Example 2.1: </span>
<br />
<br />SQL>LOCK TABLE EMP IN EXCLUSIVE MODE;
<br />
<br />You can specify several tables or views to lock in the same mode; however, only a
<br />single lock mode can be specified for each LOCK TABLE statement.
<br />
<br />Lets see what the V$LOCK view has to say about the lock held by the session
<br />
<br />SQL>select a.sid,a.type,a.lmode,a.request from v$lock a,v$session b where a.sid=b.sid and
<br />b.username='SCOTT'
<br />
<br />
<br /> SID TY LMODE REQUEST
<br />---------- -- ---------- ----------
<br /> 162 TM 6 0
<br />
<br />LMODE 6 is EXCLUSIVE lock.
<br /></pre>
<br />
<br /><pre>EXAMPLE 2.2:
<br />
<br />Lets give another example where the table is locked is SHARE ROW EXCLUSIVE (SRX) mode.
<br />
<br />SQL> LOCK TABLE EMP IN SHARE ROW EXCLUSIVE MODE;
<br />
<br />Table(s) Locked.
<br />
<br />SQL> select a.sid,a.type,a.lmode,a.request from v$lock a,v$session b where a.sid=b.sid and
<br />2 b.username='SCOTT';
<br />
<br /> SID TY LMODE REQUEST
<br />---------- -- ---------- ----------
<br /> 162 TM 5 0
<br />
<br />LMODE 5 is SRX lock mode.
<br /></pre>
<br />
<br />NOWAIT CLAUSE:
<br />
<br />You can also indicate if you do or do not want to wait to acquire the lock. If you
<br />specify the NOWAIT option, then you only acquire the table lock if it is immediately
<br />available. Otherwise an error is returned to notify that the lock is not available at this
<br />time. In this case, you can attempt to lock the resource at a later time. If NOWAIT is
<br />omitted, then the transaction does not proceed until the requested table lock is
<br />acquired.
<br />
<br />
<br />
<br /><pre><span style="font-weight: bold;">Example 3:</span>
<br />
<br />Session 1:
<br />
<br />SQL> Lock table EMP IN SHARE ROW EXCLUSIVE MODE;
<br />
<br />Session 2:
<br />
<br />SQL> LOCK TABLE EMP IN SHARE MODE NOWAIT;
<br />LOCK TABLE EMP IN SHARE MODE NOWAIT
<br /> *
<br />ERROR at line 1:
<br />ORA-00054: resource busy and acquire with NOWAIT specified
<br />
<br />Note: ALTER TABLE and DROP TABLE commands have implicitly NOWAIT keyword specified (see example 1.1).
<br /></pre>
<br />
<br />Dwaipayanhttp://www.blogger.com/profile/06948203367115191113noreply@blogger.com0tag:blogger.com,1999:blog-8923123809409417461.post-6272008949021850662011-06-26T11:28:00.000-07:002011-06-27T09:33:42.840-07:00PLAYING WITH ORACLE LOBsLOBs deal with unstructured data, the ones which are the most difficult to store and retrieve in a relational database.<br /><br />In this article, I am going to discuss extensively and manipulate the ORACLE<br /><br /><ul><br /><li>LOBs that are stored in the database itself like BLOB,CLOB,NCLOB ,and</li><br /><br /><li>LOBs like BFILE which are Stored outside the database as Operating System files.</li></ul>First let us create a directory :<br /><br /><pre>create or replace directory my_dir as 'D:\pics';<br />Directory created.</pre><strong>BFILE:</strong><br />BFILEs act as a pointer and store the location of the external OS files in database tables.<br /><br />1. POPULATING BFILE COLUMNS IN DATABASE TABLES:<br />Now we insert a row in it.<br /><br /><pre>Insert into lob_test values (1,’dwaipayan’,bfilename(‘MY_DIR’,’ pic1.jpg’));<br /></pre><br />This bfilename function returns a BFILE locator for a physical LOB binary file.<br />Now, if we wish to display the contents of the table:<br /><br /><pre>select * from lob_test;<br />SP2-0678: Column or attribute type can not be displayed by SQL*Plus<br /></pre>We get an error as we cannot display the contents of a bfile like this.<br /><br />Now, let us insert another row in the table:<br /><br /><pre>insert into lob_test values (2,'shiba',bfilename('MY_DIR','9.jpg'));<br />1 row created.</pre>Using the DBMS_LOB.GETLENGTH procedure we can get the lengths of the OS files as follows:<br /><br /><pre>select dbms_lob.getlength(PIC) from lob_test;<br />DBMS_LOB.GETLENGTH(PIC)<br />---------------------------<br />180496<br />15032<br /></pre><br /><br />Note: PIC is a BFILE column<br /><br />2. Locating the files in OS from a BFILE:<br /><br />Now, say I have loaded the tables with data, and I come back after 3 months and I don’t remember the locations of the OS files with which I had loaded the data into the tables.<br />In that case what should I do…<br />Simply, we take the help of DBMS_LOB.FILEGETNAME procedure.<br /><pre>CREATE or replace FUNCTION get_dir_name (bf BFILE) RETURN VARCHAR2 IS<br />DIR_ALIAS VARCHAR2(255);<br />FILE_NAME VARCHAR2(255);<br />BEGIN<br />IF bf is NULL<br />THEN<br />RETURN NULL;<br />ELSE<br />DBMS_LOB.FILEGETNAME (bf, dir_alias, file_name);<br />RETURN FILE_NAME;<br />END IF;<br />END;<br /><br />select get_dir_name(pic) from lob_test;<br /><br />GET_DIR_NAME(PIC)<br />--------------------------------------------------------------------------------<br />pic1.jpg<br />9.jpg</pre><br /><br />3. OPENING A BFILE AND COPYING IT TO ANOTHER OS LOCATION<br /><br />File type: image (jpg)<br /><br /><br /><pre>CREATE OR REPLACE procedure imagecopy<br />AS<br />h1 bfile;<br />h2 utl_file.file_type;<br />len integer;<br />strt integer;<br />buf raw(32767);<br />BEGIN<br />h2 := utl_file.fopen('MY_DIR','copyof9.jpg','Wb');<br />select pic into h1 from lob_test where empno=1;<br />dbms_lob.open(h1,dbms_lob.file_readonly);<br />strt:=1;<br />select dbms_lob.getlength(h1) into len from dual;<br />while strt<=len<br />loop<br />buf := dbms_lob.substr(h1,10000,strt) ;<br />utl_file.put_raw(h2,buf,TRUE);<br />strt := strt+10000;<br />end loop;<br />utl_file.fclose(h2);<br />END;<br />/<br /></pre><br />So, the image file that was pointed to by the BFILE in lob_test table with empno=1, is now copied to the new location in the OS under the directory ’MY_DIR’ as 'copyof9.jpg'.<br />Note: Here we have used utl_file package to open the database file where we are going to write the data. The mode in which the 'copyof9.jpg' file is opened is ‘Wb’ because we are going to write binary data in it.<br />Utl_file.put_raw is used to write raw data into the file<br />Besides these, we have used the dbms_lob.substr function.It’s syntax is a bit different from the ordinary substr function. In dbms_lob.substr, the requested substring length comes first, followed by the offset.<br /><br /><br /><strong>BLOB :<br /></strong>We , first create a table test_blob containing a blob column ‘DOC’ :<br /><br /><pre>create table test_blob (empno number,empname varchar2(30),doc blob);<br /><br />Table created.<br /></pre>1. Populating Blob column in database from OS file:<br /><pre>create or replace procedure blobfromfile</pre><pre>as<br />blob_buf blob;<br />h1 bfile;<br />begin<br />h1 := bfilename('MY_DIR','9.jpg');<br />dbms_lob.open(h1,dbms_lob.file_readonly);<br />blob_buf := dbms_lob.substr(h1,dbms_lob.getlength(h1),1);<br />insert into test_blob values (1,'dwaipayan',blob_buf);<br />commit;<br />end;<br /><br /><br />select count(*) from test_blob;<br /><br />COUNT(*)<br />----------<br />1</pre><br /><p></p><br /><br /><p></p><strong>CLOB :<br /></strong>We , first create a table test_clob containing a clob column ‘ABOUT_ME’ :<br /><pre>create table test_clob (empno number,about_me clob);<br /><br />Table created.<br /><br />insert into test_clob values (1,empty_clob());<br /><br />1 row created.<br /></pre>1. Populating Clob column in database from OS file:<br /><pre>CREATE OR REPLACE procedure CLOB_POPULATE<br />AS<br />CLOB_BUF blob;<br />h1 BFILE;<br />BEGIN<br />h1 := BFILENAME('MY_DIR','t3.txt');<br />dbms_lob.open(h1,dbms_lob.file_readonly);<br />clob_buf := dbms_lob.substr(h1,dbms_lob.getlength(h1),1) ;<br />update test_clob set about_me=utl_raw.cast_to_varchar2(clob_buf) where empno=1;<br />commit;<br />end;<br /><br />select substr(about_me,1,100) FROM test_clob;<br /><br />SUBSTR(ABOUT_ME,1,100)<br />--------------------------------------------------------------------------------<br />ooo<br />hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh<br />ooooooooooooooooooooooooooooooooo<br />hhh<br /></pre>2. CONVERTING CLOB TO BLOB :<br /><pre>insert into test_blob<br />2 select 2,'sinha',utl_raw.cast_to_raw(about_me) from test_clob where empno=1;<br /><br />1 row created.</pre><br /><p>Displaying The raw data:<br /></p><pre>select dbms_lob.substr(doc,100,1) from test_blob where empno=2;<br /><br />DBMS_LOB.SUBSTR(DOC,100,1)<br />--------------------------------------------------------------------------------<br />0D0A6F6F6F0D0A686868686868686868686868686868686868686868686868686868686868686868<br />0D0A6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F6F0D0A686868<br />6868686868686868686868686868686868686868<br /><br /></pre>Dwaipayanhttp://www.blogger.com/profile/06948203367115191113noreply@blogger.com0tag:blogger.com,1999:blog-8923123809409417461.post-76445060980711198222011-06-06T09:39:00.000-07:002011-06-06T10:42:13.156-07:00DBMS_FGA - ORACLE FINE GRAINED AUDITINGSuppose your boss calls you one day and tells you that there has been some unexpected changes in the employee database.Employee's designation, their salary are being manipulated illegally.Such things have been continuing from a past few days and he asks if you could help getting hold of the culprit.<br />Don't worry Oracle's DBMS_FGA package will save your day and earn you a raise in your job.<br /><br /><span style="font-weight: bold;">The Oracle DBMS_FGA package provides fine grained auditing on objects.</span><br /><br /><br />To have an overview of the summary of dbms_fga subprograms visit :<br /><br /><a href="http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_fga.htm#i1011920">http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_fga.htm#i1011920</a><br /><br />In this article I am going to add a policy on a table FGA_TEST in the <span style="font-weight: bold;">SCOTT</span> schema.<br />The policy will report on any dml actions on this table affecting its 2 columns 'esal' and 'designation'.<br />Another user <span style="font-weight: bold;">HACKER</span> will execute dml queries on this table and we will try and investigate whether the actions of the HACKER are reported.<br />The corresponding event handler of this policy will be in a 3rd schema<span style="font-weight: bold;"> FGA_HANDLER</span> .we will also find out if the audit event was handled properly.<br /><br /><span style="font-weight: bold;">Note</span>: <span style="font-style: italic;">Here it is worthwhile mentioning that the DBMS_FGA package can be used not only to audit records in case of data manipulation(DML) but also in cases where data might have been simply viewed depending upon the policy we define. eg:- in case of selecting particular records from a database table.</span><br /><table><br /><span style="font-style: italic;">SQL> show user;</span><br /><span style="font-style: italic;">USER is "SCOTT"</span><br /></table><br /><br /><br />First of all let us create a new schema FGA_HANDLER which will contain the event handler .<br /><br /><table><br /><span style="font-style: italic;">SQL> create user fga_handler identified by fga_handler;</span><br /><br /><span style="font-style: italic;">User created.</span><br /><br /><span style="font-style: italic;">SQL> grant resource,connect to fga_handler;</span><br /><br /><span style="font-style: italic;">Grant succeeded.</span><br /></table><br /><br /><br />Now, let us create a new table ,FGA_TEST in SCOTT schema, on which we will enforce the audit conditions(policy) with the help of the DBMS_FGA package.<br /><br /><table><br /><span style="font-style: italic;">SQL> create table fga_test</span><br /><span style="font-style: italic;">2 (empno number,</span><br /><span style="font-style: italic;">3 empname varchar2(30),</span><br /><span style="font-style: italic;">4 esal number,</span><br /><span style="font-style: italic;">5 designation varchar2(20)</span><br /><span style="font-style: italic;">6 );</span><br /><br />Table created.<br /><br /></table><br /><br />Let us insert some dummy rows in it now.<br /><br /><table><br /><span style="font-style: italic;">SQL> insert into FGA_TEST</span><br /><span style="font-style: italic;">2 SELECT 1,'dwaipayan',20000,'programmer' from dual</span><br /><span style="font-style: italic;">3 union all</span><br /><span style="font-style: italic;">4 select 2,'dhruva',30000,'analyst' from dual</span><br /><span style="font-style: italic;">5 union all</span><br /><span style="font-style: italic;">6 select 3,'shiba',40000,'manager' from dual</span><br /><span style="font-style: italic;">7 ;</span><br /><br /><span style="font-style: italic;">3 rows created.</span><br /><br /><span style="font-style: italic;">SQL> select * from fga_test;</span><br /><br /><span style="font-style: italic;"> </span><tbody><tr> EMPNO EMPNAME ESAL DESIGNATION </tr><br /><span style="font-style: italic;">---------- ------------------------------ ---------- --------------------</span><br /><span style="font-style: italic;"></span><tr> 1 dwaipayan 20000 programmer</tr><br /><span style="font-style: italic;"></span><tr> 2 dhruva 30000 analyst</tr><br /><span style="font-style: italic;"></span><tr> 3 shiba 40000 manager</tr><br /><br /></tbody></table><br /><br />Given below are the parameters of the ADD_POLICY Procedure:<br />Now, let us add a policy on our FGA_TEST table such that whenever any user tries to insert, update or delete the ‘esal’ or the ‘designation’ columns of any row of FGA_TEST table ,the action will be recorded.<br /><br /><br /><table><br />BEGIN<br /><span style="font-style: italic;"> DBMS_FGA.ADD_POLICY (</span><br /><span style="font-style: italic;"> object_schema => 'SCOTT',</span><br /><span style="font-style: italic;"> object_name => 'FGA_TEST',</span><br /><span style="font-style: italic;"> policy_name => 'FGA_TEST_POLICY',</span><br /><span style="font-style: italic;"> audit_condition => NULL,</span><br /><span style="font-style: italic;"> audit_column => 'ESAL,DESIGNATION',</span><br /><span style="font-style: italic;"> handler_schema => 'FGA_HANDLER',</span><br /><span style="font-style: italic;"> handler_module => 'sp_audit',</span><br /><span style="font-style: italic;"> enable => true,</span><br /><span style="font-style: italic;">statement_types => ‘INSERT,UPDATE,DELETE’</span><br /><span style="font-style: italic;"> );</span><br /><span style="font-style: italic;">end;</span><br /><br /></table><span style="font-weight: bold;">Note:</span> the default '<span><span style="font-style: italic;">statement_types' is 'SELECT'</span></span><br /><br />Now, sp_audit is the audit procedure, which will act as the alerting mechanism for the administrator.<br /><br />The required interface for such a procedure is as follows:<br /><span style="font-weight: bold;">PROCEDURE </span><fname><span style="font-weight: bold;"> ( object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2 ) AS ....</span><br />Now, let us connect to the FGA_HANDLER schema and design the<br />the sp_audit procedure.<br /><br />First let us create the table where the sp_audit procedure will dump the data into.<br /><br /><table><br /><span style="font-style: italic;">SQL> conn fga_handler/fga_handler;</span><br /><span style="font-style: italic;">Connected.</span><br /><br /><span style="font-style: italic;">SQL> create table audit_event</span><br /><span style="font-style: italic;">2 (audit_event_no number);</span><br /><br /><span style="font-style: italic;">Table created.</span><br /></table><br /><br />We create the sp_audit procedure as follows:<br /><br /><br /><table><br /><span style="font-style: italic;">SQL> create or replace procedure sp_audit</span><br /><span style="font-style: italic;">(object_schema in varchar2,</span><br /><span style="font-style: italic;">object_name in varchar2,</span><br /><span style="font-style: italic;">policy_name in varchar2</span><br /><span style="font-style: italic;">)</span><br /><span style="font-style: italic;">as</span><br /><span style="font-style: italic;">count number;</span><br /><span style="font-style: italic;">begin</span><br /><br /><span style="font-style: italic;">select nvl(max(audit_event_no),0) into count from audit_event;</span><br /><br /><span style="font-style: italic;">insert into audit_event</span><br /><span style="font-style: italic;">values</span><br /><span style="font-style: italic;">(count+1);</span><br /><span style="font-style: italic;">commit;</span><br /><br /><span style="font-style: italic;">end;</span><br /></table><br /><br />The procedure simply adds a record to the audit_event table each time it is executed and the column audit_event_no acts as counter which displays the number of times the proc has been executed.<br /><br />Now, finally we create another schema ‘HACKER’ which tries to manipulate the values of the ‘esal’ or ‘designation’ columns of the FGA_TEST table.<br /><br /><table><br /><span style="font-style: italic;">SQL> create user hacker identified by hacker;</span><br /><span style="font-style: italic;">User created.</span><br /><br /><span style="font-style: italic;">SQL> grant resource,connect to hacker;</span><br /><span style="font-style: italic;">Grant succeeded.</span><br /><br /><span style="font-style: italic;">SQL> grant all on fga_test to hacker;</span><br /><span style="font-style: italic;">Grant succeeded.</span><br />Now we log onto HACKER and try to change the ‘designation’ of ‘dwaipayan’ from ‘Programmer’ to ‘manager’<br /><br /><span style="font-style: italic;">SQL> conn hacker/hacker;</span><br /><span style="font-style: italic;">Connected.</span><br /><br /><span style="font-style: italic;">SQL> update scott.fga_test set designation='manager' where empname='dwaipayan';</span><br /><br /><span style="font-style: italic;">1 row updated.</span><br /><br /></table><br /><br />Now, we connect with SCOTT to see the dba_fga_audit_trail view to find if the event was recorded.<br /><br /><table style="font-style: italic;"><br />SQL> select DB_USER,OS_USER,POLICY_NAME,SQL_TEXT,<br />TIMESTAMP from dba_fga_audit_trail<br />where POLICY_NAME='FGA_TEST_POLICY';<br /></table><br /><br /><table><br /><tbody><tr><td>DB_USER</td> <td>OS_USER</td> <td>POLICY_NAME</td> <td>SQL_TEXT</td> <td>TIMESTAMP</td> </tr><br /> <br /><tr><td>HACKER</td> <td>HOME-6C286D743C\Dwaipayan</td> <td>FGA_TEST_POLICY</td> <td>update scott.fga_test<br />set<br />designation<br />= 'manager'<br />where<br />empname='dwaipayan'</td> <td>5-Jun-11</td> </tr><br /><br /></tbody></table><br /><br /><br />Now we connect to the FGA_HANDLER schema to see if the event handler(sp_audit) was called:<br /><br /><table><br /><span style="font-style: italic;">SQL> conn fga_HANDLER/fga_handler;</span><br /><span style="font-style: italic;">Connected.</span><br /><span style="font-style: italic;">SQL> select * from audit_event;</span><br /><br />AUDIT_EVENT_NO<br />--------------<br /> 1<br /><br /></table><br /><br />We execute the following from HACKER schema:<br /><table><br /><span style="font-style: italic;">SQL>update SCOTT.FGA_TEST set designation='HR' where name='shiba';</span><br /><span style="font-style: italic;">SQL> conn fga_HANDLER/fga_handler;</span><br /><span style="font-style: italic;">Connected.</span><br /><span style="font-style: italic;">SQL> select * from audit_event;</span><br /><br /><br />AUDIT_EVENT_NO<br />--------------<br /> 1<br /> 2<br /><br /></table><br /><br />Note: I have just created the sp_audit procedure to add rows to the audit_event table in this testing environment but in a ideal production scenario, we are likely to send emails or Page instead.<br /><br /><br /></fname>Dwaipayanhttp://www.blogger.com/profile/06948203367115191113noreply@blogger.com0