More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
How do I audit the SQL sent to the server?
This does not seem to be well documented, so here is a quick means of auditing the SQL text that is sent to the server. Note that this simply audits the SQL sent to the server. So, if your user process executes a big stored procedure, all you will see here is a call to the stored procedure. None of the SQL that is executed as part of the stored procedure will be listed.
Firstly, you need to have installed Sybase security (which involves installing the sybsecurity database and loading it using the script $SYBASE/scripts/installsecurity). Read the Sybase Security Administration Manual, you may want to enable a threshold procedure to toggle between a couple of audit tables. Be warned, that the default configuration option "suspend auditing when device full" is set to 1. This means that the server will suspend all normal SQL operations if the audit database becomes full and the sso logs in and gets rid of some data. You might want to consider changing this to 0 unless yours is a particularly sensitive installation.
Once that is done, you need to enable auditing. If you haven't already, you will need to restart ASE in order to start the audit subsystem. Then comes the bit that does not seem well documented, you need to select an appropriate audit option, and the one for the SQL text is "cmdtext". From the sybsecurity database, issue
sp_audit "cmdtext",,"all","on"
for each user on the system that wish to collect the SQL for. sp_audit seems to imply that you can replace "" with all, but I get the error message "'all' is not a valid user name". Finally, enable auditing for the system as a whole using
sp_configure "auditing",1
go
If someone knows where in the manuals this is well documented, I will add a link/reference.
Note: The stored procedure sp_audit had a different name under previous releases. I think that it was called sp_auditoption. Also, to get a full list of the options and their names, go into sybsecurity and simply run sp_audit with no arguments.
(Continued on next question...)
