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?