> -----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]
