Auditing SOA Server DML Usage (Insert/Update/Delete operations)

 

1. alter system set audit_trail=db,extended scope=spfile;

2. restart the database

3. Login as scott/tiger
audit update , insert, delete on dept;

4. select * from DBA_OBJ_AUDIT_OPTS;
SQL> select * from DBA_OBJ_AUDIT_OPTS;
OWNER                          OBJECT_NAME
------------------------------ ------------------------------
OBJECT_TYPE             ALT       AUD       COM       DEL       GRA
----------------------- --------- --------- --------- --------- ---------
IND       INS       LOC       REN       SEL       UPD       REF EXE
--------- --------- --------- --------- --------- --------- --- ---------
CRE       REA       WRI       FBK
--------- --------- --------- ---------
SCOTT                          DEPT
TABLE                   -/-       -/-       -/-       S/S       -/-
-/-       S/S       -/-       -/-       -/-       S/S       -/- -/-
-/-       -/-       -/-       -/-

5. Now run some sql ,
insert into dept values(60,'SUPPORT1','BANGALORE1')

6. Run the following sql , to check the sql ,
select ntimestamp# , sqltext , sqlbind  from sys.aud$ order by ntimestamp# desc ;

7. To remove the audit , login as scott/tiger user ,
noaudit update , insert, delete on dept;

8.  select * from DBA_OBJ_AUDIT_OPTS;
To capture the sql for a schema ,

1. Login as sys or system user and run the following sql
AUDIT update table , insert table , delete table ,  EXECUTE PROCEDURE by dev1117_soainfra;

2. select * from DBA_STMT_AUDIT_OPTS where user_name = 'DEV1117_SOAINFRA'

USER_NAME                      PROXY_NAME
------------------------------ ------------------------------
AUDIT_OPTION                             SUCCESS    FAILURE
---------------------------------------- ---------- ----------
DEV1117_SOAINFRA
EXECUTE PROCEDURE                        BY SESSION BY SESSION
DEV1117_SOAINFRA
DELETE TABLE                             BY SESSION BY SESSION
DEV1117_SOAINFRA
UPDATE TABLE                             BY SESSION BY SESSION
DEV1117_SOAINFRA
INSERT TABLE                             BY SESSION BY SESSION

3. Now run sql server and you will see all the dml statemnets captured
select ntimestamp# , sqltext , sqlbind  from sys.aud$ order by ntimestamp# desc ;

4. To remove the auditing run ,
NOAUDIT update table , insert table , delete table ,  EXECUTE PROCEDURE by dev1117_soainfra;

Restart the SOA Server so that the audit logs are disabled.

http://stackoverflow.com/questions/13100054/oracle-database-audit-doesnt-show-sql-text-and-bind-values

SOA with SqlServer Express 2008

 

 

1. Stop SQL Server's process on the machine.

2. Copy the file x64sqljdbc.dll (64 bit windows) or sqljdbc.dll (32 bit windows)  from
C:\Oracle\Middleware5\wlserver_10.3\server\lib
to
C:\Program Files\Database\MicrosoftSQLServer\MSSQL10_50.MSSQLSERVER\MSSQL\Binn

3. Copy the file C:\Program
Files\Database\MicrosoftSQLServer\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\x64sqljdbc.dll as sqljdbc.dll in the same directory (Only on 64 bit windows)

4. Run the file instjdbc.sql (C:\Oracle\Middleware5\wlserver_10.3\server\lib)
from the Sql Server Management console , here I commented the line
"dump tran master with no_log" as it was not executing , and ensured the
objects are created in the master db.

If you are getting the errors like this ,

XA transactions are disabled by default on Windows Server 2003. Microsoft? Windows Server 2003, Microsoft Distributed
Transaction Coordinator (MS DTC) requires the creation of registry values for all XA DLLs that you plan to use.

Solution (Reference : https://forums.oracle.com/thread/793763)

Connect JDBC Drivers have the XA DLL sqljdbc.dll that is normally installed on SQLServer_Install_Root\MSSQL\Binn.
For example: c:\Program Files\Microsoft SQL Server\MSSQL\Binn) The registry values required for XA transactions are not created automatically. You must create the values manually as follows:
Turn on support for XA transactions:
Open Component Services.
Expand the tree view to locate the computer where you want to turn on support for XA transactions; for example, My Computer.
Right-click the computer name, then click Properties.
Click the MSDTC tab, then click Security Configuration.
Under Security Settings, click the check box for XA Transactions to turn on this support.
Click OK, then click OK again.
Create a registry named-value:
Use Registry Editor and navigate to registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\XADLL
Create a new registry named-value:
Name is the file name of the XA DLL (in the format dllname.dll)
Type is String (REG_SZ)
Value is the full path name (including the file name) of the DLL file
Name Type Value
sqljdbc.dll String (REG_SZ) c:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqljdbc.dll

Note: You must create an entry for each XA DLL file that you plan to use. Also, if you are configuring MS DTC on a cluster, you must create these registry entries on each node in the cluster.
For more details, see the following Microsoft document:

5. Restarted the sql server machine

6. Created a new db from the Sql Server Management console with the name as  "soadb"

7. Ran the following sql from the Sql Server Management console (in this
order only)
ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
ALTER database soadb COLLATE SQL_Latin1_General_CP1_CS_AS
go
ALTER database soadb SET READ_COMMITTED_SNAPSHOT ON
go
ALTER DATABASE SOADB SET MULTI_USER;
go

8. After this ran the rcu and created the necessary schema's

9. Ran the config wizard and created a new doamin with the name
"soa_admin_domain" and
selected "soa 11g development" option so that every thing can be installed on the Admin Server.