SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
SQL statement report is disabled

 
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools View previous topic
View next topic
SQL statement report is disabled
Author Message
vicenttan



Joined: 15 Nov 2008
Posts: 7
Country: Singapore

Post SQL statement report is disabled Reply with quote
I have been able to enable the system audit on mysql database but the report for SQL statement and audit operation is still disabled.
My intention is to audit/see the full SQL statement (query, insert) to my database.

Why the report option for 'Enabled SQL Statement and Operations Audit Options' is disabled?
Sat Nov 15, 2008 6:49 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6515

Post Reply with quote
This particular report is not applicable to MYSQL systems. This report only applies to Oracle and Sybase ASE systems.

Use "Enabled Global audit Options" report to report on effective auditing settings for MySQL.


Basically, if you see certain report disabled, that only means that that report is not applicable to your system. It doesn't mean that something is wrong. Please keep in mid that DB Audit is a multi-database and multi-platform products, certain things apply to specific platforms and systems only.
Sat Nov 15, 2008 12:40 pm View user's profile Send private message
vicenttan



Joined: 15 Nov 2008
Posts: 7
Country: Singapore

Post Reply with quote
Thanks for the response
Btw, the "Enabled Global audit Options" is only to list all audit options that you have entered

How can I audit/see the full SQL statement (query, insert) to my database?
Sat Nov 15, 2008 9:41 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6515

Post Reply with quote
Try Report/System and Audit Activities Reports/Text of SQL Queries


Text of queries is also available in many other reports along with filtered and aggregated information. Have you tried reports in "System and Audit Activities Reports" group, "Compliance Reports", and "Behavior Analysis Reports" report groups?
Sat Nov 15, 2008 9:50 pm View user's profile Send private message
vicenttan



Joined: 15 Nov 2008
Posts: 7
Country: Singapore

Post Reply with quote
I have tried Report/System and Audit Activities Reports/Text of SQL Queries, "Compliance Reports", and "Behavior Analysis Reports" report groups

NONE of the report display any audit information

I have set my audit options to audit all
Login (successful), Login (failed), Logout, Replication, Table Maintenance, Account Management, Privileged Operation (CREATE/DROP/ ALTER/RENAME), Grant/Revoke Privilege, Database Administration, Data Load From Files, Table Locks, Dynamic SQL, Schema Object Access, Set Variables and Modes, User Password Change, Transactions Control, Show Variables or Statistics, Use Database, Error

I also executed few queries to my audit database.

But still, no audit information is reported.

Why no audit information is captured?
Sat Nov 15, 2008 10:16 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6515

Post Reply with quote
This means that the auditing is not running. When you click System Audit/Set Audit Options menu, on the Set Audit Options dialog do you see word "Enabled" at the bottom of this dialog?

If it says "Enabled", please post screenshots of your audit filters (screenshots of that dialog tab pages) so I can suggest what is not configured correctly.
.
Sat Nov 15, 2008 10:20 pm View user's profile Send private message
vicenttan



Joined: 15 Nov 2008
Posts: 7
Country: Singapore

Post Reply with quote



Sat Nov 15, 2008 10:27 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6515

Post Reply with quote
With such restrictive filters you are not going to get anything good. Here is what I suggest. Clear ALL filters, update settings using the "Update Audit Settings" button. Perform some activities in the database. Run report and ensure you get something in these reports. Then go back, and put back most important filters, one filter at a time, after each filter change, run reports and check what you get. Find a balance of right filters and types of captured events and data.


Filters should be meaningful. Please note that filters are applied as complex logical operation with elements joined using logical AND (not OR). You cannot audit failed logins and have filters set by database or by object. It just doesn't make any sense.

For example, if you filter audit events by hosts, don't filter them by users. If you filter by users, don't filter by hosts. Please keep in mind that filters are used to limit the amount of audited events, not to choose users or objects to be audited.

Lastly, please do not include "db_audit" in filters, this will cause troubles.
Sat Nov 15, 2008 10:50 pm View user's profile Send private message
vicenttan



Joined: 15 Nov 2008
Posts: 7
Country: Singapore

Post Reply with quote
Thanks for the filter setup tips.

I have cleared ALL filters and update the setting. I executed few queries and update sql statement as well
But still no audit information is captured.






Not sure where goes wrong. :(
Sat Nov 15, 2008 11:32 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6515

Post Reply with quote
Please let us know if you see any data in db_audit.sys_audit_trail table.

If your MySQL server is running on a Windows system, please check which DB Audit messages you get in the Windows Application Event log. Please look for messages with "DB Audit" in the source column.
If your MySQL server is running on a Linux system, please check which DB Audit messages you get in the syslog.

Thanks.
Sun Nov 16, 2008 12:03 am View user's profile Send private message
vicenttan



Joined: 15 Nov 2008
Posts: 7
Country: Singapore

Post Reply with quote
NO record in the sys_audit_trail table. After a restart on my machine, the table is now populated with audit data.

In the Event Application Log, I got 1 warning log from DB Audit
The description for Event ID 1 from source DB Audit cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

If the event originated on another computer, the display information had to be saved with the event.

The following information was included with the event:

Warning: Can't set shared library unloading hook, to safely stop server or uninstall audit, call `db_audit_stop` funtion

The specified resource type cannot be found in the image file

2. And another error log from mysql
Error writing file 'querylog' (General Query Log)

Why error occurs? Any major impact?
Sun Nov 16, 2008 12:47 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6515

Post Reply with quote
As long as you can normally shutdown the server and data appears in db_audit.sys_audit_trail data, you don't need to worry about these warnings. They are not good, but we can investigate them later. However, if you have problems shutting down MySQL server, you should remove the auditing and contact our support for further instructions.


In any case, I am going to log this issue in our bug tracking database and ask development team to take a look. Please let me know which version of MySQL you are running (full number please) and on which platform because this is the first question they are going to ask.
Sun Nov 16, 2008 12:57 am View user's profile Send private message
vicenttan



Joined: 15 Nov 2008
Posts: 7
Country: Singapore

Post Reply with quote
Mysql version = 5.0.67-community-nt-log
OS = Windows Vista

THANKS!!!
Sun Nov 16, 2008 1:07 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6515

Post Reply with quote
I checked release notes and the last version we tested from 5.0 branch of MySQL was 5.0.65. There must be some anomaly in 5.0.67 that is leading to side effect. I will let developers know and ask them to take a look.

I hope you can use the auditing as is while we are investigating this issue.
Sun Nov 16, 2008 2:04 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.