insert/update command inappropriately denied to user due to case of table name - reproducable
am 29.10.2002 15:47:37 von Ryan DelanoDescription:
When a grant is issued on a specifc table for a given user, specifically an
INSERT or UPDATE, the named user will not be able to use that granted
ability if the named table is specified in the grant statement with
uppercase characters.
How-To-Repeat:
Create a table named "junk". In that table define a table named "dr" and
define it to have one varchar column named "name". - Cheezy example, but
that is the table I used to reproduce after have big troubles in much bigger
tables. My user is "webaction" with a host of "localhost". I ran two
console sessions - one admin, and the account being denied - there were no
concurrency issues.
AS DB ADMIN>grant insert on junk.Dr to webaction@localhost;
The named user will likely be denied when they try to use it regarless of
whether or not they match the named case in their efforts to user that
right:
[This is the webaction user logged in with password supplied at start of
console session]
mysql> insert into dr values("more junk");
ERROR 1142: insert command denied to user: 'webaction@localhost' for table
'dr'
mysql> insert into Dr values("more junk");
ERROR 1142: insert command denied to user: 'webaction@localhost' for table
'dr'
mysql> insert into junk.Dr values("more junk");
ERROR 1142: insert command denied to user: 'webaction@localhost' for table
'dr'
Even though this user clearly has been given the right to insert into the
table:
mysql> show grants for webaction@localhost;
+----------------------------------------------------------- ----------------
----
-----------+
| Grants for webaction@localhost
|
+----------------------------------------------------------- ----------------
----
-----------+
| GRANT SELECT ON *.* TO 'webaction'@'localhost' IDENTIFIED BY PASSWORD
'30601e4
01843d081' |
| GRANT INSERT ON junk.Dr TO 'webaction'@'localhost'
|
....
Fix:
The cure as I have found it is to replace the grants with lowercase table
names: (note that the revoke MUST use the same case)
AS DB ADMIN> revoke all on junk.Dr from webaction@localhost;
Query OK, 0 rows affected (0.00 sec)
AS DB ADMIN> grant insert on junk.dr to webaction@localhost;
Query OK, 0 rows affected (0.00 sec)
Which then allows the user to do it's thing:
mysql> insert into junk.Dr values("more junk");
Query OK, 1 row affected (0.00 sec)
I would be happy to supply any additional information. If this is the
expected behavior I'm perplexed but appologize for a false bug-report and
thank you for an excellent open-source product anyway.
Synopsis: user unable to use granted rights when grant command was issued
with uppercase tablename
Submitter-Id: rdelano@maine.rr.com
Originator: Ryan Delano
Organization: RyDel Software, Inc.
MySQL support: none
Severity: serious
Priority: medium
Category: mysql kernel?
Class: sw-bug
Release: mysql-3.23.52
Exectutable: mysqld-nt
Environment: Athlon 2100+ 1GB RAM, whatever
System: WinXP
Compiler: Binary from mysql.org
Architecture: i
------------------------------------------------------------ ---------
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 bugs-thread12872@lists.mysql.com
To unsubscribe, e-mail