MySQL Temporary View possible?
Solution 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.
Solution 2
You can create a temporary table from a query in one step with the syntax:
CREATE TEMPORARY TABLE temp_table_name SELECT ... ;
The caveats to temporary tables:
- They aren't indexed-- large temporary tables don't perform well.
- They're only exist in the scope of the session which they're created.
ParoX
Updated on June 04, 2022Comments
-
ParoX over 1 year
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
andDROP
permissions (DROP
is required because of theor REPLACE
). I do not want them to haveDROP
permissions , as the directory user will be in a PHP file owned by apache, and they arent restricted to it usingdir
- so they could just view the user and pass.Also, I dont wan't a user to just use
bob_rooms
when he is actuallyjoe
. 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 toDROP
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 oddDROP
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?