wiki:db/DC3b/MySQLPermissions

Version 2 (modified by jbecla, 11 years ago) (diff)

updated related ticket number

MySQL Permissions in DC3

LSST Database

This is related to ticket #524

To prevent users from deleting other users' data, we could use the following scheme (assuming a database per run):

  • prepend each database with <username>_, for example jbecla_run123, or smm_myTestRun.
  • Official DC3 runs could be prepended with "DC3a_", or "DC3b_", for example DC3a_run0012. Only a designated account would be granted write access to these databases.
  • We could use wildcards to set mysql permissions. To do that, we could use the following commands:
-- Delete default annonymous account, otherwise people might default to it which is confusing.
-- It needs to be executed only once
DELETE FROM mysql.user WHERE User = "" AND Host = "";


-- then run this for each user (replacing 'jacek' with the real user name)
GRANT ALL ON `jacek\_%`.* TO 'jacek'@'localhost';
GRANT SELECT ON *.* TO 'jacek'@'localhost';

-- we will probably need to substitute 'localhost' with something like 
-- '%.ncsa.uiuc.edu' or maybe even '%' depending how much we want to open 
-- these databases for remote access.

For the record, it has been tested with the following:

-- only the owner can delete its own tables
GRANT ALL ON `jacek\_%`.* TO 'jacek'@'localhost';
GRANT SELECT ON *.* TO 'jacek'@'localhost';

GRANT ALL ON `serge\_%`.* TO 'serge'@'localhost';
GRANT SELECT ON *.* TO 'serge@'localhost';

mysql -ujacek

CREATE DATABASE malicious; -- this should fail
CREATE DATABASE jacek_r1;  -- this should succeed
USE jacek_r1;
CREATE TABLE t (i int);
INSERT INTO t VALUES(1), (2), (5);
exit;


mysql -u serge;
USE jacek_r1;     -- this should be ok
SELECT * from t;  -- this should be ok
insert into t values(1234); -- this should fail
CREATE DATABASE jacek_faked; -- this should fail
CREATE DATABASE serge_r1;
CREATE TABLE t (i int);
INSERT INTO t VALUES(10), (20), (50);
exit;