3

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?

1
  • also, I know this sounds incredibly insane for such a simple question, but IF it solves this problem, the database can also be migrated to Postgre. It's small enough, and this gain would certainly justify the hurdles around this. Performance's not critical at this point, this database has most configuration data, and will only get a thousand or so queries an hour.. Oct 3, 2012 at 7:47

3 Answers 3

3

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...)

1
  • I've tried it with SQL SECURITY INVOKER and it works perfectly! Thank you for the pointer! Oct 4, 2012 at 4:36
1

Please run this SQL Query:

SELECT USER(),CURRENT_USER();
  • USER() reports how you attempted to authenticate in MySQL
  • CURRENT_USER() reports how you were allowed to authenticate in MySQL

In reality, USER() is probably better because it reveals the DB Connection's point of original. You should probably create a Stored Procedure or Stored Function (that returns a Comma-Separated List of ids with SELECT GROUP_CONCAT(id) FROM server_orig WHERE usuario=SUBSTRING_INDEX(CURRENT_USER(),'@',1);)

That way, you can make a Stored Procedure or Stored Function with SQL SECURITY DEFINER and still know who originated the Query. CURRENT_USER() would be the definer. You will need to use the USER() function instead.

0

Use USER() instead of CURRENT_USER(). Also leave SQL SECURITY DEFINER. Using INVOKER you won't be able to select view without granting permissions for every table it uses.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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