0

Basically, whenever the user execute any query(for example: SELECT query), I want the user get instantly notified by email that he or she has executed this(the full query he just used) query. The reason I want this because the data can be confidential, so for the security purpose. Thank you.

S3S
  • 24,809
  • 5
  • 26
  • 45
matha_kharap
  • 41
  • 5
  • You'd want to let the user who executed the query to know that they did it? But no one else would know... so how is it for security purposes? This is where auditing comes in... you'd implode a mail server using it this way in an highly OLTP environment. I think you have an XY Problem: http://xyproblem.info/ – S3S Aug 20 '18 at 17:40
  • If it is confidential data why are you giving access to everyone? Just gave Read-Only access for all so that you don't need to worry. – CR241 Aug 20 '18 at 23:58
  • why `Read-Only` ? should't have access in the first place – Squirrel Aug 21 '18 at 03:59
  • let's say it is read-only that means user can use select query right, Then in that case I would like to know who perform what query. Can you please provide me with a solution?. Thank you. – matha_kharap Aug 23 '18 at 03:59

1 Answers1

3

I would advise against using this approach.

If the data is confidential sending the mail after it has been retrieved seems to be too late. During nights it can take hours until such a mail is read and during vacations it can take days or even weeks. Also imagine checking 100s of mails each day which mostly contain unsuspicious querys. It's a headache.

Better set your database up so that confidential data can not be retrieved by personal which is not authorized to do so.

If you really want to set up a trigger for sending E-Mails you might want to take a look at this answer: Send e-mail from a trigger

Christian Hoffmann
  • 170
  • 8
  • Thank you for your replay, but our company is small only 33 people and some of them are developer who needs to access the database to make dashboard. But my manager wants to see who is viewing which data by getting a notification and at the same time one e-mail to the user who viewed the data. – matha_kharap Aug 21 '18 at 03:42