Sql-server – Audit use of CLR Table-valued function

auditset-returning-functionssql serversql-clrweb service

I have written some CLR table-valued functions that invoke a web service and I would like to audit their use. Does anyone know of any way this can be achieved in the CLR code itself? I would be nice to keep the auditing code together with the CLR code rather than having to write an explicit INSERT statement every time they are used.

Best Answer

Since the TVF calls a Web Service, the Assembly had to be marked as WITH PERMISSION_SET = EXTERNAL_ACCESS (hopefully you chose to sign the Assembly, create an Asymmetric Key, and create a Login from that Key, as opposed to setting TRUSTWORTHY ON for the Database). This fact gives you two options:

Use something like File.AppendAllText to log the info into a file and keep it out of the database. This method creates the file if it doesn't exist. NOTE: This option needs to be tested with concurrent actions that would fire the Trigger as there might be some blocking / additional wait-time incurred if the OS does not allow concurrent write activity on the file.
Log to a table by creating an regular / external SqlConnection and issuing an INSERT statement. Be sure to specify enlist=false; in the Connection String so that it doesn't attempt to bind to the current Transaction. This will allow the INSERT (which occurs in a separate connection / Session) to happen whether or not there is an exception raised in the SQLCLR TVF (unless, of course, the error is with the INSERT operation ;-).

Related Solutions

Enabling/disabling/changing Oracle auditing without a shutdown

AUDIT_TRAIL is an initialisation parameters and needs a database restart. Anyway by default AUDIT_TRAIL is set to DB. This could be enough.

With this AUDIT_TRAIL you have just to issue:

AUDIT SELECT TABLE

to start the audit on any table for any select. As you stated this activity has an overhead from the database point of view. you have to pay attention to you SYSAUX tablespace growth where by default resides the sys.aud$ table. To move sys.aud$ you have to do:

SQL> BEGIN
 DBMS_AUDIT_MGMT.set_audit_trail_location(
 audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,--this moves table AUD$
 audit_trail_location_value => 'AUDIT_TBS');
END;
/

FGA is much more than AUDIT and lets you specify the conditions necessary for an audit record to be generated. Furthermore FGA policies are programatically bound to the object (table, view).

Apart from this you should consider a different solution like an third party tool for DAM (Direct Access Monitoring). Oracle has its own solution today called Oracle Firewall. Otherwise you can check for other products like IBM Guardium, DBProtect, etc.

Sql-server – SQL Server to XML files alternatives

Your current setup seems to be on the right track. I would cut SSIS out of the equation - the stored procedure INSERTs into the staging table, and then a C# app pulls the data out, creates the XML files, and SFTPs the files off to the destination. I realize that's more a 'developer' solution than an 'DBA' solution, but flat files and SFTP are not things well-suited to SQL environments.

Best Answer

Related Solutions

Enabling/disabling/changing Oracle auditing without a shutdown

Sql-server – SQL Server to XML files alternatives

Related Question