Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL Temporary View possible?

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?

like image 496
ParoX Avatar asked Mar 30 '11 22:03

ParoX


People also ask

Can we CREATE VIEW on temporary table in MySQL?

The definition cannot refer to a TEMPORARY table, and you cannot create a TEMPORARY view. It makes sense if you think about it. A view should be usable by other sessions. But a temporary table is limited to the current session where it is created.

Can view be created on temporary table?

Creating views on temporary tables is not allowed.

Are views in SQL temporary?

If specified, the view is created as a temporary view. Temporary views are automatically dropped at the end of the current session. Existing permanent relations with the same name are not visible to the current session while the temporary view exists, unless they are referenced with schema-qualified names.

What is the difference between view and temporary table in MySQL?

Temporary tables are just the tables in tempdb. Views are stored queries for existing data in existing tables. Temporary table needs to be populated first with data, and population is the main preformance-concerned issue. So the data in views already exists and so views are faster than temporary table.


1 Answers

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.

like image 145
user470714 Avatar answered Sep 26 '22 00:09

user470714