mysql用户权限

1、创建用户帐号：

CREATE USER user_name IDENTIFIED BY 'your_password';

2、用户改名：

RENAME USER old_name TO new_name;

3、删除用户帐号

DROP USER user_name;

4、创建用户并授权：

mysql> grant SELECT, INSERT, UPDATE, DELETE on *.* to lizhi@'%' identified by '123';

Query OK, 0 rows affected (0.00 sec)

mysql> show grants for lizhi@'%'\G;

*************************** 1. row ***************************

Grants for lizhi@%: GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'lizhi'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'

1 row in set (0.00 sec)

 

ERROR:

No query specified

5、查看用户权限

查看root用户权限：

mysql> show grants for root@'%'\G;

*************************** 1. row ***************************

Grants for root@%: GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' WITH GRANT OPTION

1 row in set (0.00 sec)

 

ERROR:

No query specified

查看普通用户权限（edu用户）：

mysql> show grants for edu@'%'\G;

*************************** 1. row ***************************

Grants for edu@%: GRANT USAGE ON *.* TO 'edu'@'%' IDENTIFIED BY PASSWORD '*07D7D0444BBFC047420EFDA928B0FF2DA214BC95'

*************************** 2. row ***************************

Grants for edu@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `dbedu`.* TO 'edu'@'%'

2 rows in set (0.00 sec)

 

ERROR:

No query specified

6、移除权限示例（移除lizhi用户的delete权限）：

mysql> show grants for lizhi@'%'\G;

*************************** 1. row ***************************

Grants for lizhi@%: GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'lizhi'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'

1 row in set (0.00 sec)

 

ERROR:

No query specified

 

mysql> revoke delete on *.*  from 'lizhi'@'%';

Query OK, 0 rows affected (0.00 sec)

 

mysql> show grants for lizhi@'%'\G;

*************************** 1. row ***************************

Grants for lizhi@%: GRANT SELECT, INSERT, UPDATE ON *.* TO 'lizhi'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'

1 row in set (0.00 sec)

 

ERROR:

No query specified

 

MySql的权限如下表所示：

 

ALL	除GRANT OPTION外的所有权限
ALTER	使用ALTER TABLE
ALTER ROUTING	使用ALTER PROCEDURE和DROP PROCEDURE
CREATE	使用CREATE TABLE
CREATE ROUTING	使用CREATE PROCEDURE
CREATE TEMPORARY TABLES	使用CREATE TEMPORARY TABLE
CREATE USER	使用CREATE USER、DROP USER、RENAME USER和REVOKE ALL PRIVILLEAGES
CREATE VIEW	使用CREATE VIEW
DELETE	使用DELETE
DROP	使用DROP TABLE
EXECUTE	使用CALL和存储过程
FILE	使用SELECT INTO OUTFILE和LOAD DATA INFILE
GRANT OPTION	使用GRANT和REVOKE
INDEX	使用CREATE INDEX和DROP INDEX
INSERT	使用INSERT
LOCK TABLES	使用LOCK TABLES
PROCESS	使用SHOW FULL PROCESSLIST
RELOAD	使用FFLUSH
REPLICATION CLIENT	服务器位置的访问
REPLICATION SLAVE	由复制从属使用
SELECT	使用SELECT
SHOW DATABASES	使用SHOW DATABASES
SHOW VIEW	使用SHOW CREATE VIEW
SHUTDOWN	使用mysqladmin shutdown(用来关闭MySQL)
SUPER	使用CHANGE MASTER、KILL、LOGS、PURGE、MASTER和SET GLOBAL。还允许mysqladmin调试登录
UPDATE	使用UPDATE
USAGE