(MySQL) How to change the results of a view according to the user that's viewing it?

I have a table, named servers, with a single column id.

This table is accessed by various machines each with a different username for the database.

I want to make it so each server does not see all the information on this table, but only a subset I designate to it in order to partition the workload among the app servers.

My original idea is to rename the table (say, to "servers_table"), add another "user" column to it, and create a view (with the original name) that shows the results based on the username currently connected to the server.

My table currently looks like this:

id  user
 1  server1
 2  server1
 3  server2
 4  server1
 5  server2

My attempt was (as "server1"):

CREATE VIEW server AS 
SELECT id 
FROM server_orig 
WHERE usuario=SUBSTRING_INDEX(CURRENT_USER(),'@',1);

This, however, does not work, since CURRENT USER() is evaluated at CREATE VIEW time, not at SELECT time.

Therefore, running:

mysql -userver1 database -e "select * from server;"

yields:

+----+
| id |
+----+
|  1 | 
|  2 | 
|  4 |
+----+

BUT so does running it as server2..

I cannot (since the program is closed source) change the original select statement (and that would also incur eventually changing the insert/update statements too).

Is there a way this can be accomplished purely on the MySQL side?

A hint from the current_user() docs:


For stored procedures and functions and views defined with the SQL SECURITY INVOKER characteristic, CURRENT_USER() returns the object's invoker.

On the other hand, in PostgreSQL you can do the same (with the current_user function, which behaves exactly as desired), and with good design and config the performance wouldn't be any worse - however, I don't think that this feature would make migrating to PostgreSQL any sense (especially given that your app code is closed...)

StackExchange Database Administrators Q#25337, answer score: 3