auditing for Sybase IQ

Posted on 2011-10-10
Last Modified: 2012-05-12

How to setup auditing for IQ database (sybsecurity equivalent in ASE)? Any tips on this
matter is very much appreciated.

Question by:ncshome170
    LVL 2

    Expert Comment

    Hi CS,

    There is no direct equivalent for Sybase IQ - but there are a couple of ways to do some of the same things.

    There are two ways of "auditing" in Sybase IQ depending on what you want to track.

    Turn on "request level logging" - if all you want to do is see SQL statements, then you can cause these to go to the iqmsg log.   See the -zr, -zo, and -zs commandline/configfile switches.  You can also toggle this kind of logging on/off/change settings on-the-fly by changing the properties directly from an isql/dbisql session.  (eg:   sa_server_option('request_level_logging','SQL');   -- sets request level logging to "SQL" only.  See admin guide (vol. I if you are on IQ 15.x))

    Or, if you are more interested in seeing "failed login attempts" or "changes to schema" or "permission violations" on schema...

    You can enable "auditing" by setting the option:  set option public.auditing = 'ON' ;
    This causes an "audit trail" to be written to the transaction log of the catalog.
    You can extract this "audit trail" by backing up the catalog on the fly, and then using dbtran -g to extract the audit records.  This can be done in conjunction with a regime of daily transaction log maintenance... something like:

    backup the current transaction log and "start a new one" using the dbbackup -t -r   etc, etc, etc
    and then on this backed up copy run dbtran -g to extract the audit trail to a text file.

    This "audit trail" can then be further post-processed to filter out what you want.

    There are some shortcomings of this audit trail, and if this level of auditing does not meet your needs I encourage you to ring up Sybase support and log a case stating what your specific requirements for auditing are (unfortunately Sybase don't seem to be very good at doing this proactively without a support case, so the more people that raise cases, the better the IQ product will become.)  


    - David
    LVL 2

    Accepted Solution

    Ah!  A slight correction to my first comment on request level logging...  the "request level logging" does not actually go to the iqmsg log, but to a user-defined text file specified by the -zo commandline switch -  or alternately this can be set dynamically via sa_server_option('request_level_log_file',


    - David

    Author Comment

    thanks for your comments David. I appreciate your help.


    Author Closing Comment

    thanks David.

    Warm Regards,

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Suggested Solutions

    As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
    Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now