I am running MySQL 3.23.42 on a Win32 platform, specifically, Windows98. (I
will
upgrade to Win2000 Server as soon as I can afford to, but at the moment
it's not
an option.)
I have an existing database containing 28 tables, and I want to add a user
to
that database. I want this user to have SELECT, INSERT and UPDATE
privileges on
every table in the database, and DELETE privileges on ONLY 6 of those 28
tables.
So I go about it with the following commands:
GRANT INSERT, SELECT, UPDATE ON abcdefg.* TO vwxyz IDENTIFIED BY
"xxxxxxxx";
GRANT DELETE ON abcdefg.tbl23 TO vwxyz;
GRANT DELETE ON abcdefg.tbl24 TO vwxyz;
GRANT DELETE ON abcdefg.tbl25 TO vwxyz;
GRANT DELETE ON abcdefg.tbl26 TO vwxyz;
GRANT DELETE ON abcdefg.tbl27 TO vwxyz;
GRANT DELETE ON abcdefg.tbl28 TO vwxyz;
Then I look at the tables in the mysql database to verify that permissions
have
indeed been set correctly, and they appear to have been, as follows:
mysql> select * from user where User = "vwxyz";
+------+-------+------------------+-------------+-------------+---------
----+-------------+-------------+-----------+-------------+-------------
--+--------------+-----------+------------+-----------------+-----------
-+------------+
| Host | User | Password | Select_priv | Insert_priv | Update_priv
| Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv |
Process_priv | File_priv | Grant_priv | References_priv | Index_priv |
Alter_priv |
+------+-------+------------------+-------------+-------------+---------
----+-------------+-------------+-----------+-------------+-------------
--+--------------+-----------+------------+-----------------+-----------
-+------------+
| % | vwxyz | xxxxxxxxxxxxxxxx | N | N | N
| N | N | N | N | N
| N | N | N | N | N
| N |
+------+-------+------------------+-------------+-------------+---------
----+-------------+-------------+-----------+-------------+-------------
--+--------------+-----------+------------+-----------------+-----------
-+------------+
1 row in set (0.00 sec)
mysql> select * from db where User = "vwxyz";
+------+---------+-------+-------------+-------------+-------------+----
---------+-------------+-----------+------------+-----------------+-----
-------+------------+
| Host | Db | User | Select_priv | Insert_priv | Update_priv |
Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv |
Index_priv | Alter_priv |
+------+---------+-------+-------------+-------------+-------------+----
---------+-------------+-----------+------------+-----------------+-----
-------+------------+
| % | abcdefg | vwxyz | Y | Y | Y | N
| N | N | N | N | N
| N |
+------+---------+-------+-------------+-------------+-------------+----
---------+-------------+-----------+------------+-----------------+-----
-------+------------+
1 row in set (0.00 sec)
mysql> select * from host;
Empty set (0.00 sec)
mysql> select * from tables_priv;
+------+---------+-------+------------+----------------+----------------
+------------+-------------+
| Host | Db | User | Table_name | Grantor | Timestamp |
Table_priv | Column_priv |
+------+---------+-------+------------+----------------+----------------
+------------+-------------+
| % | abcdefg | vwxyz | tbl23 | root@localhost | 20020122151327 |
Delete | |
| % | abcdefg | vwxyz | tbl24 | root@localhost | 20020122151512 |
Delete | |
| % | abcdefg | vwxyz | tbl25 | root@localhost | 20020122151603 |
Delete | |
| % | abcdefg | vwxyz | tbl26 | root@localhost | 20020122151625 |
Delete | |
| % | abcdefg | vwxyz | tbl27 | root@localhost | 20020122151742 |
Delete | |
| % | abcdefg | vwxyz | tbl28 | root@localhost | 20020122151758 |
Delete | |
+------+---------+-------+------------+----------------+----------------
+------------+-------------+
6 rows in set (0.00 sec)
mysql> show grants for vwxyz;
+-----------------------------------------------------------------------
------+
| Grants for vwxyz@%
|
+-----------------------------------------------------------------------
------+
| GRANT USAGE ON *.* TO 'vwxyz'@'%' IDENTIFIED BY PASSWORD
'xxxxxxxxxxxxxxxx' |
| GRANT SELECT, INSERT, UPDATE ON abcdefg.* TO 'vwxyz'@'%'
|
| GRANT DELETE ON abcdefg.tbl23 TO 'vwxyz'@'%'
|
| GRANT DELETE ON abcdefg.tbl24 TO 'vwxyz'@'%'
|
| GRANT DELETE ON abcdefg.tbl25 TO 'vwxyz'@'%'
|
| GRANT DELETE ON abcdefg.tbl26 TO 'vwxyz'@'%'
|
| GRANT DELETE ON abcdefg.tbl27 TO 'vwxyz'@'%'
|
| GRANT DELETE ON abcdefg.tbl28 TO 'vwxyz'@'%'
|
+-----------------------------------------------------------------------
------+
8 rows in set (0.00 sec)
Then I connect to this database from a Perl script using the Perl DBI
module
with DBD::MySQL installed. Select, insert and update SQL statements all
work
flawlessly. Then I try to execute the following statement using the do()
function of the DBI module:
DELETE FROM tbl23 WHERE field = 'data';
The function fails, and returns the following error string:
DBD::mysql::db do failed: delete command denied to user: 'vwxyz@localhost'
for table 'tbl23' at script.pl line 71.
I run the mysql tool (logging in as vwxyz) to directly enter this query,
and I
get the same error. When I run the mysql tool and log in as root, the
query
runs fine and deletes the appropriate records.
I cannot see why this should fail. My understanding of query access
verification is that MySQL will first check the user table, which I expect
to
not give permission. Then it should check the db table, which I again
expect to
be insufficient. Then it should check the tables_priv table, which I
expect
should indicate that the user does indeed have DELETE permission on the
given
table. It does not seem to do this last step, though. It seems to ignore
the
GRANT DELETE I did for this table. Why is this?
I have tried running myisamchk on the database, and deleting the user and
re-
adding him from scratch, to no avail.
Then I tried taking the reverse approach. I granted SELECT, INSERT, UPDATE
and
DELETE privileges on all tables within the database, then tried revoking
the
DELETE privilege on the 22 tables that I don't want this user to be able to
delete from. Here is the result I got:
mysql> show grants for ddweb;
+-----------------------------------------------------------------------
------+
| Grants for ddweb@%
|
+-----------------------------------------------------------------------
------+
| GRANT USAGE ON *.* TO 'vwxyz'@'%' IDENTIFIED BY PASSWORD
'xxxxxxxxxxxxxxxx' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON abcdefg.* TO 'vwxyz'@'%'
|
+-----------------------------------------------------------------------
------+
2 rows in set (0.05 sec)
mysql> REVOKE DELETE ON abcdefg.tbl01 FROM vwxyz;
ERROR 1147: There is no such grant defined for user 'vwxyz' on host '%' on
table 'tbl01'
On one hand, it will let me execute the GRANT DELETE command without
complaint
and appears to do it properly but then ignore it thereafter. On the other
hand,
it won't let me even attempt to REVOKE DELETE on a table. It appears to me
that
table-level permissions do not work, and this is a fundamental problem. Am
I
missing something?
Thanks.
Jerry Davis
