Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

auditing for Sybase IQ

Posted on 2011-10-10
4
Medium Priority
?
1,234 Views
Last Modified: 2012-05-12
Hi

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

thanks
CS
0
Comment
Question by:CHAN
  • 2
  • 2
4 Comments
 
LVL 2

Expert Comment

by:drittenh
ID: 36947277
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.)  

HTH,

- David
0
 
LVL 2

Accepted Solution

by:
drittenh earned 750 total points
ID: 36947800
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',
'sqllog.txt');  

HTH,

- David
0
 

Author Comment

by:CHAN
ID: 36972349
thanks for your comments David. I appreciate your help.

Regards
CS
0
 

Author Closing Comment

by:CHAN
ID: 37011166
thanks David.

Warm Regards,
CS
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Will the future of mobile app development include the same trends as those most relevant today? Find out by checking out the 7 trends of the future.
If anyone asked you to network diagram of the internet, it was drawn in the form of a fluffy cloud which further became known as cloud computing. Popularly cloud computing is defined as workloads that run over the internet in a commercial provider’s…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Loops Section Overview
Suggested Courses

810 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