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?
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.
Creating views on temporary tables is not allowed.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With