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?
asked 21 Feb '14, 17:08
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...)
answered 21 Feb '14, 17:32
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
answered 25 Feb '14, 11:52