Database creation privileges
Hi all, ok, I thought I had it figured out. I am using 4.1.9 now, and it looks like it behaves a little bit differently (or maybe not) than the previous 4.0.20 did when it comes to privileges. I want to create a user that does not have the ability to create databases. But, I do want them to be able to create tables in a specific database. Currently, I create the user in the global user table, and give then no privileges. Then, when I create a database, I assign them the privileges to that database by using a command like this : GRANT select,insert,update,delete,create,drop ON dbname.* to username@'%' identified by 'userpass'; This seems to work, but when that user logs in, they are able to create a database ! If I don't have the create privilege specified, then they aren't able to create tables, which I want them to be able to do... Is there a way to assign a user to a database, and give them the ability to do anything within that database, but not create another database Thanks, Tim. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database creation privileges
Hi all, ok, I thought I had it figured out. I am using 4.1.9 now, and it looks like it behaves a little bit differently (or maybe not) than the previous 4.0.20 did when it comes to privileges. I want to create a user that does not have the ability to create databases. But, I do want them to be able to create tables in a specific database. Currently, I create the user in the global user table, and give then no privileges. Then, when I create a database, I assign them the privileges to that database by using a command like this : GRANT select,insert,update,delete,create,drop ON dbname.* to username@'%' identified by 'userpass'; This seems to work, but when that user logs in, they are able to create a database ! If I don't have the create privilege specified, then they aren't able to create tables, which I want them to be able to do... Is there a way to assign a user to a database, and give them the ability to do anything within that database, but not create another database Thanks, Tim. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database creation privileges
-Original Message- From: Tim Traver Sent: Tuesday, February 15, 2005 19:30 To: mysql@lists.mysql.com Subject: Database creation privileges Hi all, ok, I thought I had it figured out. I am using 4.1.9 now, and it looks like it behaves a little bit differently (or maybe not) than the previous 4.0.20 did when it comes to privileges. I want to create a user that does not have the ability to create databases. But, I do want them to be able to create tables in a specific database. Currently, I create the user in the global user table, and give then no privileges. Then, when I create a database, I assign them the privileges to that database by using a command like this : GRANT select,insert,update,delete,create,drop ON dbname.* to username@'%' identified by 'userpass'; This seems to work, but when that user logs in, they are able to create a database ! Your grant statement should work fine on 4.1.9. Check the permissions with: SHOW GRANTS FOR 'username'@'%'; It should return the following: GRANT USAGE ON *.* TO 'username'@'%' IDENTIFIED BY PASSWORD 'password_hash' GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `dbname`.* TO 'username'@'%' They will be able to drop the database dbname, but I doubt this is an issue since you want them to be able to have the ability to drop tables from this db anyway. Also when you connect as this user, try: SELECT CURRENT_USER(); Make sure that it returns [EMAIL PROTECTED], to verify that this connection is not falling under a different grant that does not have a wildcard. If you are connected as that user, then SHOW GRANTS FOR CURRENT_USER() will accomplish both of the above in one step. If I don't have the create privilege specified, then they aren't able to create tables, which I want them to be able to do... Is there a way to assign a user to a database, and give them the ability to do anything within that database, but not create another database Thanks, Tim. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
