MySQL Temporary View possible?

Question

This is following up on:

Allow users only certain information from database

I am doing views as such (I am sure this could be optimized):

db.php that gets included:

$conn = mysql_connect("localhost","directory","dghgffhghf") or die(mysql_error());

mysql_select_db("directory", $conn) or die(mysql_error());  

mysql_query("CREATE or REPLACE VIEW {$user}_rooms AS SELECT * FROM rooms WHERE palace = '$user'") or die(mysql_error()); 
mysql_query("CREATE or REPLACE VIEW {$user}_users AS SELECT * FROM users WHERE palace = '$user'") or die(mysql_error()); 
mysql_query("CREATE or REPLACE VIEW {$user}_servers AS SELECT * FROM servers WHERE palace = '$user'") or die(mysql_error()); 
mysql_query("CREATE or REPLACE VIEW {$user}_online_servers AS SELECT * FROM online_servers WHERE palace = '$user'") or die(mysql_error()); 

Where the user "directory" has to have SELECT, CREATE VIEW and DROP permissions (DROP is required because of the or REPLACE). I do not want them to have DROP permissions , as the directory user will be in a PHP file owned by apache, and they arent restricted to it using dir- so they could just view the user and pass.

Also, I dont wan't a user to just use bob_rooms when he is actually joe. I only want the view to be created for ONLY that user at that specific connection, and DROP as soon as the connection is gone. I cannot rely on the user to DROP the view on their own.

Also, having a temporary view that would auto-drop would eliminate the use of or REPLACE which means I can take odd DROP permissions for the user.

I guess in SQLlite it would be as easy as:

CREATE TEMP VIEW ...

SqlLite can do temporary views, but MySQL can't?

Answer 1

I don't know about temporary views, but there is support for temporary tables. So you could do something where you create a temporary tables with the same structures as the tables they are mirroring, e.g. temp_bob_rooms for rooms. Then insert into the temp table a selection from the real table, with your appropriate restrictions, e.g.:

INSERT INTO temp_bob_rooms (SELECT * FROM rooms WHERE user='bob');

Then when the user is done with his session, the temp table will be dropped automatically, so the directory user doesn't need DROP access any longer. You can read some more about temporary tables here:

http://www.tutorialspoint.com/mysql/mysql-temporary-tables.htm

The downside to this approach is that the temporary table won't be updated with data inserted into the 'real' table during the time the session with the temporary table is open.

Another approach might be to simply write a script that would generate a mysql user for each real user, the appropriate views, and grant permissions to those views to the appropriate users. The only downside to that approach is that your db.php file wouldn't be useful to anyone anymore, because each user would have to create his or her own connection with the correct password and user name.