User Session Info... 1

[mynwo]

Hi All,

How to keep track of users session info. Our Informix user is shared by 4 dba's who simultenously work. If someone has shutdown down the server or changed the parameters in onconfig file, how can i come to know which particular session has done it. Also onstat -g sql <session id> gives the last sql statement fired by the user. Is there a way we can find out the entire series of sql statement fired by them. Appreciate you feedback.

Regards,

lloyd

 

[vpshriyan]

Hi,

If an user's login is shared among multiple people, chances of tracing activities done by those people looks far remote. All tracing would point to the login and will NOT pin point any one out of those bunch of people.

If you are looking for separation of dba activities, you need to have different users granted with DBA privileges. That means you need to have, in your case 4 different logins, granted with DBA authorization for a particular database. It can be granted by SQL:

GRANT DBA TO user_name ;

Shutting down an instance using onmode, or editing onconfig file using some editor are not database related, but instance related activities; so they are not logged anywhere within an instance. At Operating System level perhaps we may trace such activities using shell history files, for example HP-UX uses .sh_history at user's home directory to store all the OS commands fired by an user.

You are right when you said onstat -g sql shows the last/current sql statements executed by an user. You may able to get a complete list of SQLs (including SELECTs) fired by logged users using:

select * from sysmaster:syssqexplain ;

However, pseudo table syssqexplain does not store user information. You may trace database related manipulations (DML, insert, update, delete) through &quot;onlog&quot; log browsing utility.

Once DBA role separation is enforced using &quot;GRANT&quot; statement for different users, you may use &quot;onaudit&quot; trusted auditing features of the IDS to trace successful / unsuccessful activities made at a database / instance.

Thread below puts some light on onaudit usage etc.
thread179-666979

Following is a link to informix Trusted Facility Manual wherein you will find more about onaudit facility:

http://elib.cs.berkeley.edu/admin/informix/doc/answers/pubs/pdf/4808.pdf

Regards,
Shriyan
&quot;The hidden flaw never remains hidden.&quot;

 

[mynwo]

Hi Shriyan,

Thanks once again for the excellent post.

Best Regards,

lloyd