IQ DB Auditing

[Former Member]

Hi All,

I did some readings  on IQ auditing  , I followed below steps :

1.	SET OPTION PUBLIC.AUDITING = 'ON'
2.	sa_enable_auditing_type('DDL')
3.	dbtran -g mydb.log mydb.sql

I am able to view the  Audit details in mydb.sql  But all the time I need to run the step 3 ,  is this  a  right approach for Sybase IQ auding , Or is there any log file  when I can get details   like we have for HANA .

Thanks,

Razal

[markmumy · Accepted solution]

The transaction log is the only location of the official audit information.  Here's a snippet from the manuals on this (http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.sqlanywhere.12.0.1/dbadmin/auditi...😞

The transaction log stores all executed data definition statements, and the user ID that executed them. It also stores all updates, deletes, and inserts and which user executed those statements. However, this is insufficient for some auditing purposes. By default, the transaction log does not contain the time of the event, just the order in which events occurred. It also contains neither failed events, nor select statements.

Auditing is a way of keeping track of the activity performed on a database. When you use auditing, additional data is saved in the transaction log, including:

•   All login attempts (successful and failed), including the terminal ID. 
•   Accurate timestamps of all events (to a resolution of milliseconds). 
•   All permissions checks (successful and failed), including the object on which the permission was checked (if applicable). 
•   All actions that require DBA authority. 

You cannot stop using a transaction log while auditing is enabled for a database. If you want to turn off the transaction log, you must first turn off auditing.

Mark

[Former Member · Accepted solution]

Hi Mark ,

So does this mean that always if i need to see the audit file i need to run

dbtran -g mydb.log mydb.sql

Also i have very less insights about the transnational log file management , It would be great if you can share the best practices .

Thanks,

Razal

[markmumy · Accepted solution]

Yes, unfortunately, to see the audit trail it requires using the dbtran utility.  The same information is also available via the SCC GUI.

You will also want to add the -d option to your dbtran command line.  This orders the dbtran output by date.

"Specifies that transactions are written in order from earliest to latest. This feature is intended for auditing database activity: do not apply dbtran output against a database."

From a log file management perspective there isn't much to do.  The log files generally stay small.  What you will want to do, however, is make frequent passes at the tran log with dbtran so that you can extract and safeguard the audit information.  This is typically hourly or daily.  The frequency is up to you, however.

Once you have done that, you can then prune the transaction log with the dbbackup utility, per the docs.  This is typically something that is done weekly or monthly depending on the size and growth rate.  I prefer to keep the tran log rather small at just a few hundred MB.

Mark

[Former Member · Accepted solution]

Hi Mark,

Thanks a lot for your help and support here 

But does backup  transaction log really helps for Point in time recovery  like an incremental backup if we keep it for longer time .

- I think i will schedule daily Audit log extraction.

- Daily dbbackup for log (to make sure that we free up resources)

- we  already scheduled  daily  full data backup  and incremental backup  every 20 minutes , Because unlike other IQ used for bulk load in our case it is      uniform loading of data is happening .

-  So this  backup of transnational log backup may not be really used by us.

Correct if you have any suggestion  .

Also waiting for testing Point in time recovery  from SP08

Thanks,

Razal

[jens_bensing · Accepted solution]

Hi Mark,

IQ and HANA are coming closer and closer.

Is there anything planned like integrated audit logging of HANA and IQ while using HANA Extended Storage / Smart Data Access?

The described procedure above, extracting and safeguarding the audit information, pruning the transaction log, etc. isn't there any functionality running out of the box? Where do I need to file such a feature request to get audit logs we get it in HANA?

Besides all this, are the proposals for audit policies - do's and don't's?

Regards,

Jens

[Former Member]

Hi Mohamed

please have a look to the -zr option (http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00168.1601/doc/html/saiq-...)

Also -zn, -zs, -zo.

Dan

[former_member755915]

Hi,

We have enabled auditing in our environment, but customer needs the sql text along with the time stamp. we tried below command after shutting down the db server, but we got audit info of only DBA user. How to get the info of all the users?

dbtran -g transaction-log SQL-file