Re: mysql privileges
I would like to thank everyone who gave suggestions about how to fix the mysql privileges. Here's what we did: We did a mysqldump on the mysql table on the old machine. We brought the mysqldump into the mysql table on the new machine We ran mysql_fix_privilege_tables We gave the commandflush privileges Everything worked perfectly after that. Thanks again to everyone who helped. -- Malka Cymbalista Webmaster, Weizmann Institute of Science [EMAIL PROTECTED] 08-934-3036 On 3/19/2008 at 6:10 PM, in message [EMAIL PROTECTED], Sebastian Mendel [EMAIL PROTECTED] wrote: Brown, Charles schrieb: Here is a follow-up question: Using mysqldump, I'm about to dump all databases and import to another instance - new . My question is do I need to define all security and users in the new mysql or the security definitions and privileges will be included in the dump file created by mysqldump. i am not sure if mysqldump does include `mysql` database, but you will see if you look into it, you should run mysql_fix_privilege_tables after importing `mysql` database and FLUSH PRIVILEGES; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql privileges
Malka Cymbalista schrieb: Thanks for your reply. When I do show grants, I get back GRANT SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'super' @ 'localhost' ( mailto:super'@ 'localhost' ) identified by password... So it looks like super doesn't have rights to select from the hr table. But why not? According to the tables_priv table, super should have right to select. did you export/import your data, or just copied the data files from your old to the new MySQL? did you reload privileges after changes? FLUSH PRIVILEGES; -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql privileges
Here is a follow-up question: Using mysqldump, I'm about to dump all databases and import to another instance - new . My question is do I need to define all security and users in the new mysql or the security definitions and privileges will be included in the dump file created by mysqldump. Please help me! -Original Message- From: Sebastian Mendel [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 19, 2008 1:24 AM To: mysql@lists.mysql.com Subject: Re: mysql privileges Malka Cymbalista schrieb: Thanks for your reply. When I do show grants, I get back GRANT SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'super' @ 'localhost' ( mailto:super'@ 'localhost' ) identified by password... So it looks like super doesn't have rights to select from the hr table. But why not? According to the tables_priv table, super should have right to select. did you export/import your data, or just copied the data files from your old to the new MySQL? did you reload privileges after changes? FLUSH PRIVILEGES; -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql privileges
Brown, Charles schrieb: Here is a follow-up question: Using mysqldump, I'm about to dump all databases and import to another instance - new . My question is do I need to define all security and users in the new mysql or the security definitions and privileges will be included in the dump file created by mysqldump. i am not sure if mysqldump does include `mysql` database, but you will see if you look into it, you should run mysql_fix_privilege_tables after importing `mysql` database and FLUSH PRIVILEGES; -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql privileges
Yes you will have all the GRANTS for every user sitting in the 'mysql' schema
(from mysq.user) if you use the --all-databases option of mysqldump.
Here is something radical if you want to record the grants yourself:
Run the following query
SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') UserGrants
FROM mysql.user WHERE user IS NOT NULL AND user '' AND host IS NOT NULL AND
host '';
This query will create a SHOW GRANTS FOR command for all users in the mysqld
instance. Pipe the output to a ShowGrants.sql file
Mysql --skip-column-names -hhost -uusername -ppassword -A -eSELECT
CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') UserGrants FROM mysql.user
WHERE user IS NOT NULL AND user '' AND host IS NOT NULL AND host ''
ShowGrants.sql
Then execute the script:
Mysql -hhost -uusername -ppassword -A ShowGrants.sql AllGrants.sql
AllGrants.sql will have all GRANTS but each line has no semicolon at the end
Just append a semicolon at the end of every line like this:
sed -i 's/$/;/' AllGrants.sql
Give it a try !!!
-Original Message-
From: Brown, Charles [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 19, 2008 11:37 AM
To: Sebastian Mendel; mysql@lists.mysql.com
Subject: RE: mysql privileges
Here is a follow-up question: Using mysqldump, I'm about to dump all
databases and import to another instance - new . My question is do I
need to define all security and users in the new mysql or the security
definitions and privileges will be included in the dump file created by
mysqldump.
Please help me!
-Original Message-
From: Sebastian Mendel [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 19, 2008 1:24 AM
To: mysql@lists.mysql.com
Subject: Re: mysql privileges
Malka Cymbalista schrieb:
Thanks for your reply. When I do show grants, I get back
GRANT SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO
'super' @ 'localhost' ( mailto:super'@ 'localhost' ) identified by
password...
So it looks like super doesn't have rights to select from the hr
table. But why not? According to the tables_priv table, super should
have right to select.
did you export/import your data, or just copied the data files from your
old
to the new MySQL?
did you reload privileges after changes?
FLUSH PRIVILEGES;
--
Sebastian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
This message is intended only for the use of the Addressee and
may contain information that is PRIVILEGED and CONFIDENTIAL.
If you are not the intended recipient, you are hereby notified
that any dissemination of this communication is strictly prohibited.
If you have received this communication in error, please erase
all copies of the message and its attachments and notify us
immediately.
Thank you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql privileges
On Wed, 19 Mar 2008, Rolando Edwards [EMAIL PROTECTED] wrote: Yes you will have all the GRANTS for every user sitting in the 'mysql' schema (from mysq.user) if you use the --all-databases option of mysqldump. Can I safely assume that dumping the database named mysql dumps just as well as using --all-databases? Our backup script does a mysqldump for each separate database, and soon will separately dump every table in every database. (Easier to do a diff when I change just one table.) -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql privileges
Yes !!! mysqldump -h... -u... -p... mysql MySQLSchema.sql Give it a try !!! -Original Message- From: Tim McDaniel [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 19, 2008 2:17 PM Cc: mysql@lists.mysql.com Subject: RE: mysql privileges On Wed, 19 Mar 2008, Rolando Edwards [EMAIL PROTECTED] wrote: Yes you will have all the GRANTS for every user sitting in the 'mysql' schema (from mysq.user) if you use the --all-databases option of mysqldump. Can I safely assume that dumping the database named mysql dumps just as well as using --all-databases? Our backup script does a mysqldump for each separate database, and soon will separately dump every table in every database. (Easier to do a diff when I change just one table.) -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql privileges
Here is something radical if you want to record the grants yourself:
Run the following query
SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') UserGrants FROM
mysql.user WHERE user IS NOT NULL AND user '' AND host IS NOT NULL AND host
'';
This query will create a SHOW GRANTS FOR command for all users in the mysqld
instance. Pipe the output to a ShowGrants.sql file
Mysql --skip-column-names -hhost -uusername -ppassword -A -eSELECT
CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') UserGrants FROM mysql.user
WHERE user IS NOT NULL AND user '' AND host IS NOT NULL AND host ''
ShowGrants.sql
Then execute the script:
Mysql -hhost -uusername -ppassword -A ShowGrants.sql AllGrants.sql
AllGrants.sql will have all GRANTS but each line has no semicolon at the end
Just append a semicolon at the end of every line like this:
sed -i 's/$/;/' AllGrants.sql
Give it a try !!!
-Original Message-
From: Tim McDaniel [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 19, 2008 2:17 PM
Cc: mysql@lists.mysql.com
Subject: RE: mysql privileges
On Wed, 19 Mar 2008, Rolando Edwards [EMAIL PROTECTED] wrote:
Yes you will have all the GRANTS for every user sitting in the
'mysql' schema (from mysq.user) if you use the --all-databases
option of mysqldump.
Can I safely assume that dumping the database named mysql dumps just
as well as using --all-databases? Our backup script does a mysqldump
for each separate database, and soon will separately dump every table
in every database. (Easier to do a diff when I change just one
table.)
--
Tim McDaniel, [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql privileges
Hi, On Tue, Mar 18, 2008 at 8:49 AM, Malka Cymbalista [EMAIL PROTECTED] wrote: We are currently running MySQL 4.0.15a on a Sun Solaris server. We are moving to a Linux machine running MySQL 5.0.45. I am having a problem with permissions in MySQL. On the current machine running 4.0.15a, when I connect to MySQL as the user super and give the command: select lname from hr where fname = shlomit; I get the expected result. On the new machine running MySQL 5.0.45, when I connect as the user super and give the same command, I get the following error: ERROR 1142 (42000): SELECT command denied to user 'super'@'localhost' for table 'hr' The MySQL permissions are the same on both machines. When I give the following command: select * from tables_priv where user=super and db =web_positions and table_name = hr; I get the following result on both machines: | Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv +--+---+---+++-++-+ | %| web_positions | super | hr | [EMAIL PROTECTED] | 2002-07-21 15:07:17 | Select | | When I give the following command, I aslo get the same results on both machines: select * from user where user =super; The results are N for all the different privileges. Has anything changed in MySQL 5.0.45 that would cause this behavior? Probably not. You are probably not logged in as the user you think you are. Instead of checking privileges by selecting from the mysql system tables, use SHOW GRANTS to see what your privileges are and who you're logged in as. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql privileges
Thanks for your reply. When I do show grants, I get back GRANT SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'super' @ 'localhost' ( mailto:super'@ 'localhost' ) identified by password... So it looks like super doesn't have rights to select from the hr table. But why not? According to the tables_priv table, super should have right to select. I tried giving the command (as root) grant select on web_positions.hr to [EMAIL PROTECTED] identified by . I get back ERROR 2013 (HY000): Lost connection to MySQL server during query I can give any other command but when I try to give the grant command I keep getting the same error. Malka Cymbalista Webmaster, Weizmann Institute of Science [EMAIL PROTECTED] 08-934-3036 On 3/18/2008 at 3:20 PM, in message [EMAIL PROTECTED], Baron Schwartz [EMAIL PROTECTED] wrote: Hi, On Tue, Mar 18, 2008 at 8:49 AM, Malka Cymbalista [EMAIL PROTECTED] wrote: We are currently running MySQL 4.0.15a on a Sun Solaris server. We are moving to a Linux machine running MySQL 5.0.45. I am having a problem with permissions in MySQL. On the current machine running 4.0.15a, when I connect to MySQL as the user super and give the command: select lname from hr where fname = shlomit; I get the expected result. On the new machine running MySQL 5.0.45, when I connect as the user super and give the same command, I get the following error: ERROR 1142 (42000): SELECT command denied to user 'super'@'localhost' for table 'hr' The MySQL permissions are the same on both machines. When I give the following command: select * from tables_priv where user=super and db =web_positions and table_name = hr; I get the following result on both machines: | Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv +--+---+---+++-++-+ | %| web_positions | super | hr | [EMAIL PROTECTED] | 2002-07-21 15:07:17 | Select | | When I give the following command, I aslo get the same results on both machines: select * from user where user =super; The results are N for all the different privileges. Has anything changed in MySQL 5.0.45 that would cause this behavior? Probably not. You are probably not logged in as the user you think you are. Instead of checking privileges by selecting from the mysql system tables, use SHOW GRANTS to see what your privileges are and who you're logged in as. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql privileges
* now i can access with [EMAIL PROTECTED] but i can't create databases What does show grants display when you login as root? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Privileges - table privileges question
Greetings, I am sorry if I am asking a question that has already been answered somewhere. If it was and you know where - please let me know. I am trying to give access to a user, who should have access to a specific database with over 200 tables and should not have access to one or two of them. Reading MySQL's manual only suggests that I have to add ALL tables that I want the user to have access to into the tables_priv and exclude the ones that the uses should not have accesss to. That is correct. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Privileges - table privileges question
I am afraid you have read the docs correctly. Privileges exist at 4 levels: Global, Database, Table, and Column. So, for someone to only see part of a database, you have to GRANT permissions to the specific tables that user gets rights to work with. No other way around it. However, you may be able to quickly write a batch SQL script using a spread sheet to help speed up the process. Run the SHOW TABLES command then copy hose results into a spreadsheet. Add a formula to take a table name and embed it inside the correct GRANT phraseology for your situation. Now you should have a single cell that looks like a valid GRANT statement. Copy the formula so that it processes every table name in the list (select the formula cell, copy it to the clipboard, highlight the rows around your formula that are next to the rest of the table names then hit paste). You end up mass creating a list of GRANT statements to run for the user. Since each row has a different table name, each GRANT statement ends up affecting a different table. Highlight all of the formula results, copy them to the clipboard then past them into the mysql client and you should be done in no time flat. Don't forget to end each GRANT statement with a semicolon (;) This works very well for me using mysql in a Windoze command shell and M$ Excel. I hope it works with what you have, too. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Mihail Manolov [EMAIL PROTECTED] wrote on 12/02/2004 02:30:51 PM: Greetings, I am sorry if I am asking a question that has already been answered somewhere. If it was and you know where - please let me know. I am trying to give access to a user, who should have access to a specific database with over 200 tables and should not have access to one or two of them. Reading MySQL's manual only suggests that I have to add ALL tables that I want the user to have access to into the tables_priv and exclude the ones that the uses should not have accesss to. Any better solutions than this one? Thanks! Mihail Manolov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Privileges - table privileges question
Is this a typical situation? If those 1 or 2 tables have higher security requirements than the rest, so some users should have access to all the tables except them, another option would be to move them to a separate db. Then you could grant the average user access to the db with the rest of the tables, but only privileged users get access to both dbs. This would be easier to maintain, at the cost of slightly complicating queries that use tables in both dbs. That is, you'd have to qualify those 1 or 2 tables with db names every time you want to join them to another table. On the other hand, avoiding table-level privileges can have performance benefits http://dev.mysql.com/doc/mysql/en/Query_Speed.html. Michael [EMAIL PROTECTED] wrote: I am afraid you have read the docs correctly. Privileges exist at 4 levels: Global, Database, Table, and Column. So, for someone to only see part of a database, you have to GRANT permissions to the specific tables that user gets rights to work with. No other way around it. However, you may be able to quickly write a batch SQL script using a spread sheet to help speed up the process. Run the SHOW TABLES command then copy hose results into a spreadsheet. Add a formula to take a table name and embed it inside the correct GRANT phraseology for your situation. Now you should have a single cell that looks like a valid GRANT statement. Copy the formula so that it processes every table name in the list (select the formula cell, copy it to the clipboard, highlight the rows around your formula that are next to the rest of the table names then hit paste). You end up mass creating a list of GRANT statements to run for the user. Since each row has a different table name, each GRANT statement ends up affecting a different table. Highlight all of the formula results, copy them to the clipboard then past them into the mysql client and you should be done in no time flat. Don't forget to end each GRANT statement with a semicolon (;) This works very well for me using mysql in a Windoze command shell and M$ Excel. I hope it works with what you have, too. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Mihail Manolov [EMAIL PROTECTED] wrote on 12/02/2004 02:30:51 PM: Greetings, I am sorry if I am asking a question that has already been answered somewhere. If it was and you know where - please let me know. I am trying to give access to a user, who should have access to a specific database with over 200 tables and should not have access to one or two of them. Reading MySQL's manual only suggests that I have to add ALL tables that I want the user to have access to into the tables_priv and exclude the ones that the uses should not have accesss to. Any better solutions than this one? Thanks! Mihail Manolov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Privileges
[EMAIL PROTECTED] wrote: I am running MySQL 4.1.1-alpha-standard on RH Linux 9. I've found strange problem with privileges: mysql grant reload on *.* to 'fabackup'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql grant create, insert, drop on mysql.ibbackup_binlog_marker to 'fabackup'@localhost; Query OK, 0 rows affected (0.01 sec) mysql show grants for [EMAIL PROTECTED]; +--+ | Grants for [EMAIL PROTECTED] | +--+ | GRANT RELOAD ON *.* TO 'fabackup'@'localhost' | | GRANT INSERT, CREATE, DROP ON `mysql`.`ibbackup_binlog_marker` TO 'fabackup'@'localhost' | +--+ 2 rows in set (0.00 sec) mysql select * from tables_priv; +---+---+--+++-++-+ | Host | Db| User | Table_name | Grantor| Timestamp | Table_priv | Column_priv | +---+---+--+++-++-+ | localhost | mysql | fabackup | ibbackup_binlog_marker | [EMAIL PROTECTED] | 2004-05-17 13:28:02 | Insert,Create,Drop | | +---+---+--+++-++-+ 1 row in set (0.01 sec) After shutting down the database with: [EMAIL PROTECTED] init.d]# mysqladmin -p shutdown Enter password: 040517 13:32:44 mysqld ended and restarting it with: [EMAIL PROTECTED] init.d]# /etc/rc.d/init.d/mysql start [EMAIL PROTECTED] init.d]# Starting mysqld daemon with databases from /var/lib/mysql [EMAIL PROTECTED] init.d]# mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.1-alpha-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show grants for [EMAIL PROTECTED]; +---+ | Grants for [EMAIL PROTECTED] | +---+ | GRANT RELOAD ON *.* TO 'fabackup'@'localhost' | +---+ 1 row in set (0.00 sec) The create, insert, drop privilege on mysql.ibbackup_binlog_marker has disappeared but still reported in mysql.tables_priv table.??!!! mysql use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql select * from tables_priv; +---+---+--+++-++-+ | Host | Db| User | Table_name | Grantor| Timestamp | Table_priv | Column_priv | +---+---+--+++-++-+ | localhost | mysql | fabackup | ibbackup_binlog_marker | [EMAIL PROTECTED] | 2004-05-17 13:28:02 | Insert,Create,Drop | | +---+---+--+++-++-+ 1 row in set (0.00 sec) mysql exit Bye When logon as fabackup cannot even access mysql database. [EMAIL PROTECTED] init.d]# mysql -ufabackup Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 4.1.1-alpha-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql use mysql ERROR 1044 (42000): Access denied for user: 'fabackup'@'localhost' to database 'mysql' mysql Am I doing something wrong here, or this is a bug. I've searched bug database, but could not find this reported. Any help will be apprecieated. It's a known bug. Fixed in version 4.1.2: http://bugs.mysql.com/bug.php?id=2546 http://dev.mysql.com/doc/mysql/en/News-4.1.2.html -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL privileges System
At 12:24 -0700 6/8/03, Mike Walth wrote: Hello: I was wondering if anyone had any advice to a couple questions. 1) Can I add additional columns to the user and db tables of mysql. These would be for administrative purposes only. You can, but doing so is unsupported, and the server won't do anything with them. You also introduce the possibility of conflict with changes made the the grant tables in future releases. 2) I'm trying to figure out how the privileges effect the system. I understand what each one does, but haven't found a clear explanation of what the difference is in the db table and the user table. If any one can explain this I would appreciate it. user = global privileges db = db-specific privileges 3) I'm looking to come up with the most secure set of privileges for the user and db tables. I want the users to be able to select, insert, delete, create tables, just the basic functions on their database only. Multiple databases are hosted on the same mysql server. Don't grant them privileges in the user table, because those are global. Grant them on a database-specific level. Thank you for your help in these questions. Mike Walth CinoFusion -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL Privileges
Hi Mike, there are two reports on devshed which I believe are an excellent source. There is really nothing to add. Print them off read them carefully on your way home and tomorrrow you will be 2 steps further. http://www.devshed.com/Server_Side/MySQL/Access/page1.html http://www.devshed.com/Server_Side/MySQL/Grant_Tables/page1.html http://www.devshed.com/Server_Side/MySQL/Administration/page1.html They also offer printer friendly formats. I hope you dont think ... hmm just another document ;-). They are really worth the time. Best regards Nils Valentin Tokyo/Japan 2003 6 3 11:09Mike Walth : Hello all: I have gone through the documentation, and also the archives, and still can't grasp why things happen with privileges. Here is what I am trying to understand and accomplish. For my own benefit I'm trying ot understand the user and db tables of mysql. If I setup a user with no privileges, but in the db table they have privileges, that user can still select, insert, etc even when it is set to no in the user table. A couple questions I have: What are the differences between the user/db table? Also for the best security what shoudl I set the privileges to for everyone. There are multiple db's on the server, and only one user per db, and they only need the basic privilges, i.e. create tables, select, insert, etc. Your help is greatly appreciated. Mike Walth CinoFusion -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL Privileges
Mike Walth [EMAIL PROTECTED] wrote: I have gone through the documentation, and also the archives, and still can't grasp why things happen with privileges. Here is what I am trying to understand and accomplish. For my own benefit I'm trying ot understand the user and db tables of mysql. If I setup a user with no privileges, but in the db table they have privileges, that user can still select, insert, etc even when it is set to no in the user table. A couple questions I have: What are the differences between the user/db table? In the table 'user' global level privileges are stored, i.e. privileges on all databases. In the table 'db' only privileges on the certain database are stored. Also for the best security what shoudl I set the privileges to for everyone. There are multiple db's on the server, and only one user per db, and they only need the basic privilges, i.e. create tables, select, insert, etc. Give to the users only privileges on the needed databases. For more info look at: http://www.mysql.com/doc/en/GRANT.html http://www.mysql.com/doc/en/Privileges.html -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Privileges Question
Hi Mihail, I have done the flushing before trying to connect and it didn't work. I try the show command and nothing unusal from what it reported. Regards, Michael - Original Message - From: [EMAIL PROTECTED] To: Yat-Shing Tam [EMAIL PROTECTED] Cc: MySQL Mailing List [EMAIL PROTECTED] Sent: Wednesday, July 17, 2002 10:23 PM Subject: Re: MySQL Privileges Question Did you try: FLUSH PRIVILEGES; Also, to see what user can do with his permissions you can try this: SHOW GRANTS FOR username; Mihail Quoting Yat-Shing Tam [EMAIL PROTECTED]: Hi fellows, I have a MySQL privileges question and hope someone can provide some input on it. I have setup a user who can access only one db, say 'DB1'. About the privileges, I set the account privilege like this: 1) In user table, this account has Host='%' User='user_name' Password='pwd' and all other privileges are set to 'N'. 2) In db table, this account has Host='%' Db='DB1' User='user_name' and all other privileges are set to 'Y' EXCEPT grant_priv. Through MySQL Front GUI, the user login and is allowed to access 'DB1' only and other dbs are not visible to this user. Very things look good at this point, however, when I have a piece of java code try to access 'DB1' with this user account through MM.MySQL JDBC I receive an access deny. Questions: 1) Why it behaves like this? I thought if I can access through GUI application with this account, I should have the same access through JDBC. 2) Does this mean I have to use an account which has a global access privilege(s) in order to access the db through JDBC?? Any input would be appreciated. Best regards, Michael - 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 Privileges Question
As a little addition to that: localhost needs to be given extra rights, because localhost will always use socket connections instead of TCP. Yat-Shing Tam wrote: Hi Mihail, I have done the flushing before trying to connect and it didn't work. I try the show command and nothing unusal from what it reported. Regards, Michael - Original Message - From: [EMAIL PROTECTED] To: Yat-Shing Tam [EMAIL PROTECTED] Cc: MySQL Mailing List [EMAIL PROTECTED] Sent: Wednesday, July 17, 2002 10:23 PM Subject: Re: MySQL Privileges Question Did you try: FLUSH PRIVILEGES; Also, to see what user can do with his permissions you can try this: SHOW GRANTS FOR username; Mihail Quoting Yat-Shing Tam [EMAIL PROTECTED]: Hi fellows, I have a MySQL privileges question and hope someone can provide some input on it. I have setup a user who can access only one db, say 'DB1'. About the privileges, I set the account privilege like this: 1) In user table, this account has Host='%' User='user_name' Password='pwd' and all other privileges are set to 'N'. 2) In db table, this account has Host='%' Db='DB1' User='user_name' and all other privileges are set to 'Y' EXCEPT grant_priv. Through MySQL Front GUI, the user login and is allowed to access 'DB1' only and other dbs are not visible to this user. Very things look good at this point, however, when I have a piece of java code try to access 'DB1' with this user account through MM.MySQL JDBC I receive an access deny. Questions: 1) Why it behaves like this? I thought if I can access through GUI application with this account, I should have the same access through JDBC. 2) Does this mean I have to use an account which has a global access privilege(s) in order to access the db through JDBC?? Any input would be appreciated. Best regards, Michael - 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 -- Ralf Narozny SPLENDID Internet GmbH Co KG Skandinaviendamm 212, 24109 Kiel, Germany fon: +49 431 660 97 0, fax: +49 431 660 97 20 mailto:[EMAIL PROTECTED], http://www.splendid.de - 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 Privileges Question
Did you try: FLUSH PRIVILEGES; Also, to see what user can do with his permissions you can try this: SHOW GRANTS FOR username; Mihail Quoting Yat-Shing Tam [EMAIL PROTECTED]: Hi fellows, I have a MySQL privileges question and hope someone can provide some input on it. I have setup a user who can access only one db, say 'DB1'. About the privileges, I set the account privilege like this: 1) In user table, this account has Host='%' User='user_name' Password='pwd' and all other privileges are set to 'N'. 2) In db table, this account has Host='%' Db='DB1' User='user_name' and all other privileges are set to 'Y' EXCEPT grant_priv. Through MySQL Front GUI, the user login and is allowed to access 'DB1' only and other dbs are not visible to this user. Very things look good at this point, however, when I have a piece of java code try to access 'DB1' with this user account through MM.MySQL JDBC I receive an access deny. Questions: 1) Why it behaves like this? I thought if I can access through GUI application with this account, I should have the same access through JDBC. 2) Does this mean I have to use an account which has a global access privilege(s) in order to access the db through JDBC?? Any input would be appreciated. Best regards, Michael - 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 privileges problem
Chee-Siong Cheong writes: Hi everyone, Recently I accidentally modify the user permission of "root" from "localhost" to "any" with webmin. After I modified, I can no longer connect to the database with my machine, the error is ERROR 1045: Access denied for user: '@localhost' What can I do to change it back to the original mode? Thanks in advance. Hi! Restart your mysqld with '--skip-grant-tables', you will not need a password, nor will you need to be on the bogus host "any". :) Once in, correct the problem, and then restart mysqld (without the '--skip-grant-tables'). Note that this is basically an FAQ. You should have searched the MySQL Manual and/or the Mailing List Archive before posting: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) If you *did* do this before posting, my apologies... It was not very apparant that you had. :( Regards, Matt -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Matt Wagner [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ River Falls, Wisconsin, USA ___/ Developer - 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 privileges problem
Look up skip_grants in the manual - Original Message - From: "Chee-Siong Cheong" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, January 14, 2001 23:02 Subject: Mysql privileges problem Hi everyone, Recently I accidentally modify the user permission of "root" from "localhost" to "any" with webmin. After I modified, I can no longer connect to the database with my machine, the error is ERROR 1045: Access denied for user: '@localhost' What can I do to change it back to the original mode? Thanks in advance. - 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
