0

I'm constructing a website.

In this website, people will be able to manipulate several DB tables data.

Everytime someone wants to make a CUD operation I want to log it (like and audit).

The way I see it, I should use triggers for CUD operations, but I can't understand how do I log the user, since triggers don't accept any input parameter.

NOTE: the user I want to log is the network user. I know this user when they access the website (user to log <> user logged to DB).

ANOTHER NOTE: None of my tables saves creation date, creator, update date and updator. Just don't know why they should when I have the audit tables.

FEST
  • 813
  • 2
  • 14
  • 37
  • Are your application users logging in to the DB with their own accounts (even if proxied through another connection); or do you just have a connection pool and there's no connection between the application and DB user? – Alex Poole Feb 05 '15 at 19:10
  • I have a connection string with a fixed user, so every user that goes to the website, in the DB appears has that user. That is the reason why I can't use the DB user executing the command. Hope this answers your question :P – FEST Feb 05 '15 at 21:41

1 Answers1

1

So this is the basic problem with web apps. If you have a huge user base ( more then say 500 ), then provisioning them in the database, while this is very easily doable, it is something most web programmers, sadly, don't want to deal with and want only ONE connection user for the database. You have already shot yourself in the foot because you don't have the created_by,modified_by, created_date, modified_date in the tables. To fix this you really only have one choice:

  1. Put the columns on the tables and force the UI people to push the "network" user name through. The rest of the columns can be handled by one very simple trigger.

Why DB audit will not help you:

The DB audit feature ONLY deals with users defined as actual users in the database, sorry that is just the way it is.

Here are some things to look at when dealing with a front end system.

You can write SP's or Packages that execute as the schema owner, but can be run by ANYONE who is defined in the database and those can handle all the INSERT, UPDATE, DELETE operations on the schema they are defined in by simply giving other users the EXECUTE privilege on that set of SP's. This give the DB fine grain control over how tables are manipulated and you only have to grans the select privilege to all the users.

You can write a SP or Package in the SYSTEM schema that allows a group of people to provision users on the system by granting the execute privilege on that SP. Within that SP you define what ROLES they are assigned and therefor can control all their access.

FlyingGuy
  • 333
  • 1
  • 9
  • Hi @FlyingGuy. I'm still in time for adding those columns to my tables, is that, it's only more logically for such data to be apart from the real information. I really got interested in the "giving permissions" you talked in PCK and SPs, I was developing all "actions" inside a PCK. I'm going to search a bit more about those objects and roles. Just a note, i don't know who and how many people will be using the website but I would say very few, at least for most of the website. I'll not mark you answer as the most correct yet, please allow me to search a bit more about the subject. – FEST Feb 09 '15 at 12:40
  • After searching a bit about roles, I understood this should be the right way to do it, unfortunately, in my case, this would lift up to much confusion so I'll go with the "Put the columns on the tables and force the UI people to push the "network" user name through. The rest of the columns can be handled by one very simple trigger". – FEST Feb 09 '15 at 14:14
  • Hey Fest, if you audience is going to be that small, then just make them authenticate using the DB credential. That way you will know for sure who did what and when AND at that point you can turn on auditing per user. – FlyingGuy Feb 10 '15 at 02:07