Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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.. –  Guto Andreollo Oct 3 '12 at 7:47
add comment

2 Answers

up vote 2 down vote accepted

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

share|improve this answer
    
I've tried it with SQL SECURITY INVOKER and it works perfectly! Thank you for the pointer! –  Guto Andreollo Oct 4 '12 at 4:36
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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