|
Our auditors request that we start keeping track of when users get added and removed from database groups. Is there a way to see this in SQL Anywhere 12? For important tables we keep a log of changes through triggers. When a user updates a record the old record values get inserted into a "shadow table". That's pretty foolproof. Is there a way to put a trigger on a system table somewhere that would allow us to keep track of adding and removing users from groups? |
|
AFAIK, SQL Anywhere does not offer DDL triggers, i.e. triggers on system tables. As you ask for "auditing", I guess you could use the builtin auditing feature to audit (only) DDL statements, and that would include the user/group management - cf. the sa_enable_auditing_type system procedure with type "DDL". Or you could build a set of wrapper STPs/functions around the user/group management and give (non-DBA) users access to manage users and groups only that way. Within these STPs, you could apparantly log the actions as desired. Note, however, that DBAs could still use the normal DDL statements for user management, so those operations would go unnoticed... (On the other hand, a DBA could drop DDL triggers as well, if they would exist...) @Volker: Is there a way to query the audit information using a SQL statement?
(24 Feb '14, 09:02)
RobertDD
Replies hidden
1
No, I don't think so: The autit information is contained in the transaction log, and as such, you have to translate the transaction log to get to know its contents - see this doc page. However, that's no difficult task, either.
(24 Feb '14, 15:45)
Volker Barth
@Volker: That's what I thought. The problem with this is two-fold.
What have others implemented as solutions for this? I am assuming that such requests must be quite common?
(24 Feb '14, 16:55)
RobertDD
Replies hidden
1
FWIW, the log translation tools (both within Sybase Central and the DBTRAN utility) are based on the DBTools API (namely the DBTranslateLog() function), so you can certainly build a small (C-based) program that can as well read the translog - and you could then surely filter the output to only show user management-related statements like GRANT MEMBERSHIP. So by customizing such a tool, your users would not need to be able to translate every log and to be able to understand the contents... To you 2nd point: Yes, the audit information will be unavailable when the according transaction log is deleted. Note, it's not necessary that the information is contained in the active log, an off-line log (left by log renaming) would do as well. However, if you need a permanently stored "audit", I would suggest the wrapper around the user-mangement statements, as hinted at in my answer.
(25 Feb '14, 07:31)
Volker Barth
This answers all my questions, even if I don't like the answers one bit. Thank you!
(26 Feb '14, 16:34)
RobertDD
|
|
A (perhaps distasteful) workaround might be to push all the GRANT functionality into a stored procedure and only GRANT EXECUTE to that procedure to the (presumably various, local) administrators. That procedure could perform all the logging you could possibly dream of... with the downside that all that DIY granting logic might be complex. Personally, I would rather see http://sqlanywhere.blogspot.ca/2014/02/product-suggestion-create-event-type.html 2
Yes, that's what I meant by the last paragraph in my answer:)
I surely second the "CREATE EVENT on DDL" suggestion.
(26 Feb '14, 06:31)
Volker Barth
Replies hidden
1
This answers all my questions, even if I don't like the answers one bit. Chalk me up as another supporter of the "CREATE EVENT on DDL" suggestion, and thank you for the very clear link.
(26 Feb '14, 16:34)
RobertDD
Yes, indeed you did say that first!
(26 Feb '14, 17:12)
Breck Carter
|
