Revoke all privileges on *.* doesn't work
Hi,
the documentation reads:
REVOKE /|priv_type|/ [(/|column_list|/)] [, /|priv_type|/ [(/|column_list|/)]]
...
ON {/|tbl_name|/ | * | *.* | /|db_name|/.*}
FROM /|user|/ [, /|user|/] ...
REVOKE ALL PRIVILEGES, GRANT OPTION FROM /|user|/ [, /|user|/] ...
but it doesn't work, as you can see below:
grant all privileges on *.* to user3;
Query OK, 0 rows affected (0.00 sec)
mysql show grants for user3;
++
| Grants for [EMAIL PROTECTED] |
++
| GRANT ALL PRIVILEGES ON *.* TO 'user3'@'%' |
++
1 row in set (0.00 sec)mysql show variables like 'version';
+---++
| Variable_name | Value |
+---++
| version | 4.0.21-Max-log |
+---++
1 row in set (0.00 sec)
mysql revoke all privileges on *.* from user3;
Query OK, 0 rows affected (0.00 sec)
mysql show grants for user3;
+---+
| Grants for [EMAIL PROTECTED]|
+---+
| GRANT USAGE ON *.* TO 'user3'@'%' |
+---+
1 row in set (0.00 sec)
mysql flush privileges;
Query OK, 0 rows affected (0.09 sec)
mysql show grants for user3;
+---+
| Grants for [EMAIL PROTECTED]|
+---+
| GRANT USAGE ON *.* TO 'user3'@'%' |
+---+
1 row in set (0.00 sec)
mysql show variables like 'version';
+---++
| Variable_name | Value |
+---++
| version | 4.0.21-Max-log |
+---++
1 row in set (0.00 sec)
what am I doing wrong?
Thanks,
--
Nico Sabbi - Officine Digitali - Bologna
Tel. 051 - 4187565
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Revoke all privileges on *.* doesn't work
From: Nico Sabbi mysql show grants for user3; +---+ | Grants for [EMAIL PROTECTED]| +---+ | GRANT USAGE ON *.* TO 'user3'@'%' | +---+ 1 row in set (0.00 sec) what am I doing wrong? You're not reading carefully ;-P Look at the table at http://dev.mysql.com/doc/mysql/en/grant.html Near the bottom it says: USAGE ||Synonym for ``no privileges'' So, you succesfully removed all privileges! Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Revoke all privileges on *.* doesn't work
Nico Sabbi [EMAIL PROTECTED] wrote on 03/10/2005 10:46:37 AM:
Hi,
the documentation reads:
REVOKE /|priv_type|/ [(/|column_list|/)] [, /|priv_type|/
[(/|column_list|/)]] ...
ON {/|tbl_name|/ | * | *.* | /|db_name|/.*}
FROM /|user|/ [, /|user|/] ...
REVOKE ALL PRIVILEGES, GRANT OPTION FROM /|user|/ [, /|user|/] ...
but it doesn't work, as you can see below:
grant all privileges on *.* to user3;
Query OK, 0 rows affected (0.00 sec)
mysql show grants for user3;
++
| Grants for [EMAIL PROTECTED] |
++
| GRANT ALL PRIVILEGES ON *.* TO 'user3'@'%' |
++
1 row in set (0.00 sec)mysql show variables like 'version';
+---++
| Variable_name | Value |
+---++
| version | 4.0.21-Max-log |
+---++
1 row in set (0.00 sec)
mysql revoke all privileges on *.* from user3;
Query OK, 0 rows affected (0.00 sec)
mysql show grants for user3;
+---+
| Grants for [EMAIL PROTECTED]|
+---+
| GRANT USAGE ON *.* TO 'user3'@'%' |
+---+
1 row in set (0.00 sec)
mysql flush privileges;
Query OK, 0 rows affected (0.09 sec)
mysql show grants for user3;
+---+
| Grants for [EMAIL PROTECTED]|
+---+
| GRANT USAGE ON *.* TO 'user3'@'%' |
+---+
1 row in set (0.00 sec)
mysql show variables like 'version';
+---++
| Variable_name | Value |
+---++
| version | 4.0.21-Max-log |
+---++
1 row in set (0.00 sec)
what am I doing wrong?
Thanks,
--
Nico Sabbi - Officine Digitali - Bologna
Tel. 051 - 4187565
'USAGE' is what permission you have when you can log into the server but
do nothing else. The table that controls global permissions also contains
the user's login and password. When you REVOKE ALL ... on a user you don't
delete that user's row from this table, you set every permission column to
'N'. If you had deleted the record for that user then you would not have
been able to do a SHOW GRANTS FOR user3 as it would no longer exist in
the system.
Run this query and you will see what I mean:
SELECT * from mysql.user where user='user3';
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
RE: Revoke all privileges on *.* doesn't work
mysql show grants for user3; +---+ | Grants for [EMAIL PROTECTED]| +---+ | GRANT USAGE ON *.* TO 'user3'@'%' | +---+ 1 row in set (0.00 sec) what am I doing wrong? Near the bottom it says: USAGE ||Synonym for ``no privileges'' So, you succesfully removed all privileges! That's something that always bugged me... If you really want to remove a user from your interface, you'll have to do it manually in the table. But why has it been implemented this way? If I want to remove a user, I don't want him/her to have no privileges, I want him/her to be completely out of the database... Anyone knows why they chose this route? Christian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Revoke all privileges on *.* doesn't work
At 11:01 -0500 3/10/05, Caron, Christian wrote: mysql show grants for user3; +---+ | Grants for [EMAIL PROTECTED]| +---+ | GRANT USAGE ON *.* TO 'user3'@'%' | +---+ 1 row in set (0.00 sec) what am I doing wrong? Near the bottom it says: USAGE ||Synonym for ``no privileges'' So, you succesfully removed all privileges! That's something that always bugged me... If you really want to remove a user from your interface, you'll have to do it manually in the table. But why has it been implemented this way? If I want to remove a user, I don't want him/her to have no privileges, I want him/her to be completely out of the database... Anyone knows why they chose this route? You might want to use DROP USER instead: http://dev.mysql.com/doc/mysql/en/drop-user.html -- 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: Revoke all privileges on *.* doesn't work
Caron, Christian wrote: mysql show grants for user3; +---+ | Grants for [EMAIL PROTECTED]| +---+ | GRANT USAGE ON *.* TO 'user3'@'%' | +---+ 1 row in set (0.00 sec) what am I doing wrong? Near the bottom it says: USAGE ||Synonym for ``no privileges'' So, you succesfully removed all privileges! good to know :) , but ... That's something that always bugged me... If you really want to remove a user from your interface, you'll have to do it manually in the table. But why has it been implemented this way? If I want to remove a user, I don't want him/her to have no privileges, I want him/her to be completely out of the database... Anyone knows why they chose this route? Christian I totally agree: I would like mysql to kill or forget the existence of that user. Besides, I'm not supposed to mess with a dbms' internal tables, nor to know how/where grants are stored. -- Nico Sabbi - Officine Digitali - Bologna Tel. 051 - 4187565 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
