Auditing group membership

[Former Member]

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?

[VolkerBarth · Accepted solution]

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...)

[Former Member · Accepted solution]

@Volker: Is there a way to query the audit information using a SQL statement?

[VolkerBarth · Accepted solution]

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.

[Former Member · Accepted solution]

@Volker: That's what I thought. The problem with this is two-fold.

1. I can't give access to this to others that do not know how to perform these tasks on the database. I would like to put together a simple little program that just shows a user when other users were granted or revoked membership.

2. I assume this information doesn't stay around forever. I am not sure how much history is kept in the transaction logs, but I believe it is very limited. I wouldn't be able to look back a few months and see the information (granting and revoking rights) I am looking for.

What have others implemented as solutions for this? I am assuming that such requests must be quite common?

[VolkerBarth · Accepted solution]

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.

[Former Member · Accepted solution]

This answers all my questions, even if I don't like the answers one bit. Thank you!

[Breck_Carter]

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

[VolkerBarth]

Yes, that's what I meant by the last paragraph in my answer:)

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...

I surely second the "CREATE EVENT on DDL" suggestion.

[Former Member]

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.

[Breck_Carter]

Yes, indeed you did say that first!