用户管理 创建普通用户 创建普通用户，并完全授权访问特定的数据库，且允许远程登录
1 2 3 4 5 create user 'uatOption' @'%' identified by '123456' ;grant all privileges on mysql_db.* to 'uatOption' @'%' ;flush privileges;
提示
若希望不允许用户远程登录，可以使用 grant all privileges on mysql_db.* to 'uatOption'@'localhost' 命令。 若希望授权远程访问所有数据库，可以使用 grant all privileges on *.* to 'uatOption'@'%'，但这会增加数据库被攻击的风险。 创建只读用户 创建普通用户，并授予特定数据库的只读权限，且允许远程登录
1 2 3 4 5 create user 'uatOption' @'%' identified by '123456' ;grant select on mysql_db.* to 'uatOption' @'%' identified by '123456' ;flush privileges;
查询所有用户 查询数据库中的所有用户，请确保拥有 mysql.user 表的操作权限。
1 select user , host, plugin from mysql.user;
1 2 3 4 5 6 7 8 9 +------------------+-----------+-----------------------+ | user | host | plugin | +------------------+-----------+-----------------------+ | root | % | mysql_native_password | | uatOption | % | mysql_native_password | | mysql.session | localhost | mysql_native_password | | mysql.sys | localhost | mysql_native_password | | root | localhost | mysql_native_password | +------------------+-----------+-----------------------+
删除特定用户 删除特定用户及其拥有的权限
1 drop user 'uatOption' @'%' ;
权限管理 查询用户的登录权限 1 select user, host from mysql.user where user = 'uatOption' ;
1 2 3 4 5 +-----------+------+ | user | host | +-----------+------+ | uatOption | % | +-----------+------+
查看用户的数据库权限 1 show grants for 'uatOption' @'%' ;
1 2 3 4 5 6 7 +------------------------------------------------------------+ | Grants for uatOption@% | +------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'uatOption'@'%' | | GRANT ALL PRIVILEGES ON `test_demo_1`.* TO 'uatOption'@'%' | | GRANT ALL PRIVILEGES ON `test_demo_2`.* TO 'uatOption'@'%' | +------------------------------------------------------------+
查看用户详细的数据库权限 查询特定用户在每一个数据库的每一个对象上的全部权限。
1 select * from information_schema.user_privileges where GRANTEE = "'root'@'%'";
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 +------------+---------------+-------------------------+--------------+ | GRANTEE | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE | +------------+---------------+-------------------------+--------------+ | 'root'@'%' | def | SELECT | YES | | 'root'@'%' | def | INSERT | YES | | 'root'@'%' | def | UPDATE | YES | | 'root'@'%' | def | DELETE | YES | | 'root'@'%' | def | CREATE | YES | | 'root'@'%' | def | DROP | YES | | 'root'@'%' | def | RELOAD | YES | | 'root'@'%' | def | SHUTDOWN | YES | | 'root'@'%' | def | PROCESS | YES | | 'root'@'%' | def | FILE | YES | | 'root'@'%' | def | REFERENCES | YES | | 'root'@'%' | def | INDEX | YES | | 'root'@'%' | def | ALTER | YES | | 'root'@'%' | def | SHOW DATABASES | YES | | 'root'@'%' | def | SUPER | YES | | 'root'@'%' | def | CREATE TEMPORARY TABLES | YES | | 'root'@'%' | def | LOCK TABLES | YES | | 'root'@'%' | def | EXECUTE | YES | | 'root'@'%' | def | REPLICATION SLAVE | YES | | 'root'@'%' | def | REPLICATION CLIENT | YES | | 'root'@'%' | def | CREATE VIEW | YES | | 'root'@'%' | def | SHOW VIEW | YES | | 'root'@'%' | def | CREATE ROUTINE | YES | | 'root'@'%' | def | ALTER ROUTINE | YES | | 'root'@'%' | def | CREATE USER | YES | | 'root'@'%' | def | EVENT | YES | | 'root'@'%' | def | TRIGGER | YES | | 'root'@'%' | def | CREATE TABLESPACE | YES | +------------+---------------+-------------------------+--------------+
授权用户远程登录的权限 下述命令用于授权 root 用户远程登录的权限，其中 *.* 代表所有数据库所有权限，'root'@'%' 中的 root 代表用户名，% 代表所有的来源 IP 地址，123456 是登录密码。
1 2 3 grant all privileges on * .* to 'root' @'%' identified by '123456' with grant option;flush privileges;
