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

RobertDD's gravatar image

RobertDD
489161719
accept rate: 42%


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

permanent link

answered 21 Feb '14, 17:32

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665
accept rate: 32%

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

  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?

(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

permanent link

answered 25 Feb '14, 11:52

Breck%20Carter's gravatar image

Breck Carter
26.2k430600863
accept rate: 20%

2

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.

(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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×5

question asked: 21 Feb '14, 17:08

question was seen: 1,210 times

last updated: 26 Feb '14, 17:12