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 | root@localhost | 2002-07-2115: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?

Thanks for any .



Malka Cymbalista
Webmaster, Weizmann Institute of Science
malki.cymbalista@weizmann.ac.il
08-934-3036

Hi,

On Tue, Mar 18, 2008 at 8:49 AM, Malka Cymbalista
<Malki.Cymbalista@weizmann.ac.il> 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 | root@localhost | 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.

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 super@localhost 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
malki.cymbalista@weizmann.ac.il
08-934-3036


>>> On 3/18/2008 at 3:20 PM, in message <4cfa0b030803180620j4696ed57m684003a74023064@mail. gmail.com>, "Baron Schwartz" <baron@xaprb.com> wrote:
Hi,

On Tue, Mar 18, 2008 at 8:49 AM, Malka Cymbalista
<Malki.Cymbalista@weizmann.ac.il> 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 | root@localhost | 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/mysql?unsub=M...weizmann.ac.il

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

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 me!


-----Original Message-----
From: Sebastian Mendel [mailto:lists@sebastianmendel.de]
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/mysql?unsub=cbrown@bmi.com

********************************************
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.
********************************************

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

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 command flush privileges
Everything worked perfectly after that.
Thanks again to everyone who ed.
--

Malka Cymbalista
Webmaster, Weizmann Institute of Science
malki.cymbalista@weizmann.ac.il
08-934-3036


>>> On 3/19/2008 at 6:10 PM, in message <47E13B07.6040000@sebastianmendel.de>,
Sebastian Mendel <lists@sebastianmendel.de> 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;