Re: MySQL user privilages
Alright I have tried to do the grant like the following: GRANT ALTER,CREATE,DELETE,DROP,INDEX,SELECT,UPDATE ON user%.* TO user@% IDENTIFIED BY password; GRANT ALTER,CREATE,DELETE,DROP,INDEX,SELECT,UPDATE ON user%.* TO user@% IDENTIFIED BY password; With no luck I keep getting an error message... ERRO 1064: You have an error in you SQL syntax near 'usre%.* To user@% IDENTIFIED BY password at line 2 Any help would be appreciated. TIA, Ryan --- Paul DuBois [EMAIL PROTECTED] wrote: At 9:25 -0800 12/8/02, Ryan McDougall wrote: Hi thanx for the details... Ok I know I am thick... and for some reason I cannot get my head around this, let me try to explain what I want to do then maybe you can tell me its possible and how it is possible. I want to create a user that can be creative as he wants... allow him to create ANY databases he wants and delete them if he no longer needs them, but at that same time not be able to mess with ANY other privileges or anyone elses DBs. That's not how it works. You can grant privileges for any database, but you can't grant privileges for any database except certain databases. One thing that may help is that you can use a pattern to specify the database name in the GRANT statement. Then the user can create any database that has a name matching the pattern. For example, GRANT ALL ON xyz%.* ... will allow the user to create and mess with any database having a name that begins with xyz. The only other way I can think of it to compare it to a windows situation. Lets say I'm in Windows and I start up MS Access... Now with me being a Windows user I can create any DB I want and do whatever I want to the DBs I've created (as long as its not in a read only folder) but I can't see or do anything to to the other peoples creations becuase I don't have permissions to do so. Am I going to have to, as root or another super user, create his DBs for him and then give him privileges to them? I just don't get how that sort of situation works in a production environment. But then again in a production environment you don't usually have people just creating DBs at will. Again sorry for my complete lack of understanding on this and stupidity, but my brain just will not wrap itself around this issue. You're probably trying to understand the MySQL privilege system as analogous to some other type of privilege system. Try to understand it as itself and you'll probably get farther. Thanx for all the help so far, Ryan McDougall __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Re: MySQL user privilages
On Thursday 12 December 2002 10:03, Ryan McDougall wrote: Alright I have tried to do the grant like the following: GRANT ALTER,CREATE,DELETE,DROP,INDEX,SELECT,UPDATE ON user%.* TO user@% IDENTIFIED BY password; GRANT ALTER,CREATE,DELETE,DROP,INDEX,SELECT,UPDATE ON user%.* TO user@% IDENTIFIED BY password; With no luck I keep getting an error message... ERRO 1064: You have an error in you SQL syntax near 'usre%.* To user@% IDENTIFIED BY password at line 2 Use backticks. mysql GRANT ALTER,CREATE,DELETE,DROP,INDEX,SELECT,UPDATE ON `user%`.* TO 'user'@'%' IDENTIFIED BY password; Query OK, 0 rows affected (0.01 sec) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL user privilages
Dear Ryan, have a look at the manual: http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Database_Adm inistration.html#User_Account_Management With the exception of Paul's book on MySQL, you probably won't find a better explanation of the MySQL privilege system. PLEASE I'M GOING CRAZY trying to figure this out... my book doesn't say anything about what EXACTLY a user can/can't touch with which privilages Just in short: Put this in my.cnf / my.ini: [mysqld] safe-show-database This will prevent MySQL users to see databases to which they have no access privilege. Starting with MySQL 4.0.5a, this have changed. Now, there is a special privilege show_database which is set to N by default. safe-show-database will still work, though. grant select,insert,update,delete,create,drop on somedb.* to someusr@% identified by 'passwrd'; This means, someusr can access the somedb database and all its tables from anywhere (%). someusr can create and drop tables, and might even drop database somedb, but cannot add or drop any other databases. So this should be exactly what you want. If applicable, you may want to restrict the access to something which is not %, but rather something like 192.168.% (e. g. intranet users) or even 62.115.92.78 (if your users have a static ip address). HTH! -- Stefan Hinz [EMAIL PROTECTED] CEO / Geschäftsleitung iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Ryan McDougall [EMAIL PROTECTED] To: mysql [EMAIL PROTECTED] Sent: Sunday, December 08, 2002 6:39 AM Subject: MySQL user privilages Hey everyone, Ok if I issue this command: mysql grant select,insert,update,delete,create,drop - on *.* to someusr@% identified by 'passwrd'; I already know that this statement creates the user someusr w/ password=passwrd and it can connect from anywhere. What I don't know: Can this user now mess up the privilage tables? Can this user mess with other users DBs and tables? I want to create a normal user for me that can create my own DBs and delete them if I want (it my stuff anyway ) but I don't want me to be able to see or do anything to anybody elses stuff. Would I then have to run this command??? mysql grant select,insert,update,delete,create,drop - on somedb.* to someusr@% identified by 'passwrd'; And create this test DB so that this user can have initial privilages on some DB then that user will be ale to create their own DBs at will?? I just don't get the whole user aspect of MySQL... I want it to be like a normal *nix user create their own crap and delete it if they want... but don't touch whats not theirs! Is this accomplished with either of those commands, if so which one, if not how do I do that? PLEASE I'M GOING CRAZY trying to figure this out... my book doesn't say anything about what EXACTLY a user can/can't touch with which privilages __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL user privilages
Hi thanx for the details... Ok I know I am thick... and for some reason I cannot get my head around this, let me try to explain what I want to do then maybe you can tell me its possible and how it is possible. I want to create a user that can be creative as he wants... allow him to create ANY databases he wants and delete them if he no longer needs them, but at that same time not be able to mess with ANY other privileges or anyone elses DBs. The only other way I can think of it to compare it to a windows situation. Lets say I'm in Windows and I start up MS Access... Now with me being a Windows user I can create any DB I want and do whatever I want to the DBs I've created (as long as its not in a read only folder) but I can't see or do anything to to the other peoples creations becuase I don't have permissions to do so. Am I going to have to, as root or another super user, create his DBs for him and then give him privileges to them? I just don't get how that sort of situation works in a production environment. But then again in a production environment you don't usually have people just creating DBs at will. Again sorry for my complete lack of understanding on this and stupidity, but my brain just will not wrap itself around this issue. Thanx for all the help so far, Ryan McDougall --- Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED] wrote: Dear Ryan, have a look at the manual: http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Database_Adm inistration.html#User_Account_Management With the exception of Paul's book on MySQL, you probably won't find a better explanation of the MySQL privilege system. PLEASE I'M GOING CRAZY trying to figure this out... my book doesn't say anything about what EXACTLY a user can/can't touch with which privilages Just in short: Put this in my.cnf / my.ini: [mysqld] safe-show-database This will prevent MySQL users to see databases to which they have no access privilege. Starting with MySQL 4.0.5a, this have changed. Now, there is a special privilege show_database which is set to N by default. safe-show-database will still work, though. grant select,insert,update,delete,create,drop on somedb.* to someusr@% identified by 'passwrd'; This means, someusr can access the somedb database and all its tables from anywhere (%). someusr can create and drop tables, and might even drop database somedb, but cannot add or drop any other databases. So this should be exactly what you want. If applicable, you may want to restrict the access to something which is not %, but rather something like 192.168.% (e. g. intranet users) or even 62.115.92.78 (if your users have a static ip address). HTH! -- Stefan Hinz [EMAIL PROTECTED] CEO / Geschäftsleitung iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL user privilages
Hey everyone, Ok if I issue this command: mysql grant select,insert,update,delete,create,drop - on *.* to someusr@% identified by 'passwrd'; I already know that this statement creates the user someusr w/ password=passwrd and it can connect from anywhere. What I don't know: Can this user now mess up the privilage tables? Can this user mess with other users DBs and tables? I want to create a normal user for me that can create my own DBs and delete them if I want (it my stuff anyway ) but I don't want me to be able to see or do anything to anybody elses stuff. Would I then have to run this command??? mysql grant select,insert,update,delete,create,drop - on somedb.* to someusr@% identified by 'passwrd'; And create this test DB so that this user can have initial privilages on some DB then that user will be ale to create their own DBs at will?? I just don't get the whole user aspect of MySQL... I want it to be like a normal *nix user create their own crap and delete it if they want... but don't touch whats not theirs! Is this accomplished with either of those commands, if so which one, if not how do I do that? PLEASE I'M GOING CRAZY trying to figure this out... my book doesn't say anything about what EXACTLY a user can/can't touch with which privilages __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL user privilages
At 21:39 -0800 12/7/02, Ryan McDougall wrote: Hey everyone, Ok if I issue this command: mysql grant select,insert,update,delete,create,drop - on *.* to someusr@% identified by 'passwrd'; I already know that this statement creates the user someusr w/ password=passwrd and it can connect from anywhere. What I don't know: Can this user now mess up the privilage tables? Can this user mess with other users DBs and tables? Sure. By using ON *.*, you've granted global privileges. They apply to all databases -- which includes the mysql database that contains the grant tables. I want to create a normal user for me that can create my own DBs and delete them if I want (it my stuff anyway ) but I don't want me to be able to see or do anything to anybody elses stuff. Would I then have to run this command??? mysql grant select,insert,update,delete,create,drop - on somedb.* to someusr@% identified by 'passwrd'; And create this test DB so that this user can have initial privilages on some DB then that user will be ale to create their own DBs at will?? I just don't get the whole user aspect of MySQL... I want it to be like a normal *nix user create their own crap and delete it if they want... but don't touch whats not theirs! Is this accomplished with either of those commands, if so which one, if not how do I do that? PLEASE I'M GOING CRAZY trying to figure this out... my book doesn't say anything about what EXACTLY a user can/can't touch with which privilages - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php