Learning Mysql

Removing Users
The previous section explained how to remove privileges, but the user is not actually deleted from the server you can check this using the SHOW GRANTS
statement:
mysql> SHOW GRANTS FOR 'partmusic'@'localhost';
+-------------------------------------------------------------+
| Grants for partmusic@localhost |
+-------------------------------------------------------------+
| GRANT USAGE ONTO 'partmusic'@'localhost' IDENTIFIED BY |
| PASSWORD '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7' |
+-------------------------------------------------------------+
1 row inset sec)
This means the user can still connect, but has no privileges when she does.
You can remove access to the MySQL server by removing a user. The DROP USER
state- ment (available since MySQL 4.1.1) removes a user who has no privileges. Here’s an example that completes the removal of 'partmusic'@'localhost'
that we began in the previous section:
mysql> DROP USER 'partmusic'@'localhost';
Query OK, 0 rows affected (0.00 sec)
Prior to MySQL version 5.02, the DROP USER
statement reported an error if any privileges remained fora user:
mysql> DROP USER 'selina'@'localhost';
ERROR 1268 (HY000): Can't drop one or more of the requested users
In such a case, you must first revoke all privileges fora user before trying to
DROP
them.
Prior to MySQL version 4.1.1, you needed to use the SQL
DELETE
statement to remove a user. Here’s how you remove the user 'partmusic'@'localhost'
in these versions:
mysql> DELETE FROM mysql.user WHERE User='partmusic' and Host='localhost';
Query OK, 1 row affected (0.00 sec)
Whenever you update the grant tables in the mysql database directly, you have to use the FLUSH PRIVILEGES
instruction to tell the server to read in the updated data:
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
The structure of the mysql database and the FLUSH PRIVILEGES
statement are explained later in Managing Privileges with SQL

Download 4.24 Mb.

Share with your friends: