1

Writing an application that will require a user to login.

I would like to use a table to store my user information; however, the user's ID will not be available to me when I create audits for all my tables.

Conversely, adding the user to the database itself will give me what I need but it's going to be A LOT of users.

What is the best practice here? I need to be able to create table audits with a column that holds the id of the user that changed it.

0
2

If I understand your question correctly, you are asking how to pass an application user name to MySQL to be recorded in an audit table.

You can use a user-defined variable, which is local to the session in which it is created. Your application code will set it to the name of the logged-in user once it is authenticated. Your audit code can then reference that variable and retrieve the user name.

dbfiddle

1
  • Brilliant. Thank you!
    – Elcid_91
    Oct 30 '20 at 15:48
0

I do not recommend it instead of creating users, because your table and index size will grow.

But, some companies doing what you said. Adding a new column on their tables like CreatedBy and ModifiedBy.

After the data change, they search their audit table with the table which is holding user information.

2
  • 1
    Even a billion different users won't be a problem if the is a suitable INDEX.
    – Rick James
    Nov 2 '20 at 16:22
  • Yes, I know. I just mention that the growing data size. Nov 2 '20 at 16:35
0

How are you doing the audit? With Stored routines? Consider using the USER() function. But be careful about how you define the functions; it may give you root@localhost instead of the actual "user".

1
  • Thanks, but I believe you missed the context of the question
    – Elcid_91
    Nov 2 '20 at 14:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.