首页 > 代码库 > MYSQL的账号管理和授权
MYSQL的账号管理和授权
用户账号：‘user‘@‘host‘
创建格式：CREATE USER ‘username‘@‘host‘ [IDENTIFIED BY ‘password‘]
mysql> create user ‘test‘@‘%‘;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT User,Host,Password FROM user;
+------+-----------+-------------------------------------------+
| User | Host | Password |
+------+-----------+-------------------------------------------+
| root | localhost | *9F69E47E519D9CA02116BF5796684F7D0D45F8FA |
| test | % | |
+------+-----------+-------------------------------------------+
删除用户：DROP USER ‘username‘@‘host‘;
mysql> DROP USER ‘test‘@‘%‘;
Query OK, 0 rows affected (0.00 sec)
授权权限：GRANT priv_type,... ON [object_type] db_name.tb_name TO ‘user‘@‘host‘ [IDENTIFIED BY ‘password‘] [WITH GRANT OPTION];
如果用户不存在GRANT直接创建
mysql> HELP GRANT;
Name: ‘GRANT‘
Description:
Syntax:
GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user_specification [, user_specification] ...
[REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
[WITH with_option ...]
object_type:
TABLE
| FUNCTION
| PROCEDURE
priv_level:
*
| *.* 所有库的所有表
| db_name.* 指定库的所有表
| db_name.tbl_name 指定库的指定表
| tbl_name
| db_name.routine_name 指定库的存储例程
user_specification:
user [IDENTIFIED BY [PASSWORD] ‘password‘]
ssl_option:
SSL
| X509
| CIPHER ‘cipher‘
| ISSUER ‘issuer‘
| SUBJECT ‘subject‘
with_option:
GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
查看指定用户授权：SHOW GRANTS FOR ‘user‘@‘host‘;
mysql> SHOW GRANTS FOR ‘root‘@‘localhost‘;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*9F69E47E519D9CA02116BF5796684F7D0D45F8FA‘ WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
查看当前用户授权：SHOW GRANTS FOR CURRENT_USER;
回收授权：REVOKE priv_type, ... ON db_name.tb_name FROM ‘user‘@‘host‘;
mysql> HELP REVOKE;
Name: ‘REVOKE‘
Description:
Syntax:
REVOKE
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
FROM user [, user] ...
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM user [, user] ...
MYSQL的账号管理和授权