Is there any way to identify application logged-in users from MySQL triggers? Here I'm doing MySQL trigger-based auditing. I need to identify which user is doing CRUD operations to the database. By using USER() can get the database user. But here I'm trying to identify the application logged-in user.

I tried with @user but it is given null.

CodePudding user response：

Application users are not a MySQL resource. Any application can store them as data in a MySQL table, but MySQL has no way of using that to know which application user is currently authenticated. How the application uses that data is custom to your application.

You might even have exception cases. For example, it's not uncommon for an administrator to have a privilege to temporarily adopt the identity of another user. This also depends on your application, and there's no way MySQL can know about this unless you tell it.

You could implement code in your application to set @user='...'; in the database session after the application user is authenticated. If you do that consistently, then the variable would be available in triggers.

But that's not very secure. Any client could set the value to something else.

Otherwise there's no way for the database session to know anything about your application. All it knows is the mysql user.