Author |
Message |
vicenttan
Joined: 15 Nov 2008 Posts: 7 Country: Singapore |
|
SQL statement report is disabled |
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
vicenttan
Joined: 15 Nov 2008 Posts: 7 Country: Singapore |
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
vicenttan
Joined: 15 Nov 2008 Posts: 7 Country: Singapore |
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
vicenttan
Joined: 15 Nov 2008 Posts: 7 Country: Singapore |
|
|
Sat Nov 15, 2008 10:27 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
vicenttan
Joined: 15 Nov 2008 Posts: 7 Country: Singapore |
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
vicenttan
Joined: 15 Nov 2008 Posts: 7 Country: Singapore |
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
vicenttan
Joined: 15 Nov 2008 Posts: 7 Country: Singapore |
|
|
|
Mysql version = 5.0.67-community-nt-log
OS = Windows Vista
THANKS!!!
|
|
Sun Nov 16, 2008 1:07 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
|