MySQL用户及权限管理

01.了解mysql的默认用户
02.权限的分类
03.用户的创建
04.给用户授权
05.废除用户权限

mysql认证方式:
root(用户名)@localhost(登录主机的地址–客户端) 密码
客户端: 要通过什么用户通过什么密码登录服务器
我们要关注:用户名 密码 要登录的服务器的ip地址 服务器提供的mysql服务的端口号
服务器: 验证客户端的登录信息是否在用户表里
验证:用户名(user) 密码(authentication_string) 客户端的登陆主机(host登陆地址)
如果以上三个信息都在mysql.user(涉及到客户端连接)表中,登陆成功
01.了解mysql的默认用户
1.在低版本(5.6及之前版本)中,有匿名用户存在,不存在用户=匿名用户,匿名用户的权限很低,有时候正常用户登陆进去会被认为是匿名用户,导致什么也做不了,所以在低版本中登陆进去先操作delect from mysql.user where user=’’;删除匿名用户.
2.当全新的MySQL数据库安装完毕后，系统就为我们默认地创建了几个用户：管理员用户root以及匿名用户。通过查看名为mysql的系统数据库中的user表，可以看到所有的用户名及其全局权限（Global Privileges）.
mysql> select user,host from mysql.user;
±--------------±----------+
| user | host |
±--------------±----------+
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
±--------------±----------+
6 rows in set (0.00 sec)
mysql>
查看某个用户有那些权限:
mysql> show grants for ‘mysql.sys’@‘localhost’;
±--------------------------------------------------------------+
| Grants for [email protected] |
±--------------------------------------------------------------+
| GRANT USAGE ON . TO ‘mysql.sys’@‘localhost’ |
| GRANT TRIGGER ON sys.* TO ‘mysql.sys’@‘localhost’ |
| GRANT SELECT ON sys.sys_config TO ‘mysql.sys’@‘localhost’ |
±--------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql>
Usage是什么权限呢?
mysql> show privileges;
Usage | Server Admin | No privileges - allow connect only 没有权限,只允许连接

想看一个用户是否有全局权限,可以
mysql> select * from mysql.user\G;全是y的有全局权限
3.mysql的默认用户
3.1‘mysql.sys’@’localhost’：
用于 sys schema中对象的定义。使用 mysql.sys 用户可避免DBA重命名或者删除 root用户时发生的问题。该用户已被锁定，客户端无法连接。
3.2‘mysql.session’@’localhost’：
插件内部使用来访问服务器。该用户已被锁定，客户端无法连接。
3.3‘root’@’localhost’：
其用于管理。该用户拥有所有权限，可执行任何操作。
注意:
权限:
不要轻易的授权给用户超级权限
授予给用户的权限只要满足用户的操作即可(层层审核),不要赋予多余的权限
db1 t1 t2 dml(select/insert/delete/update)
db1.* t3(财务数据) delete
3.4了解不存在用户(匿名用户)的默认行为
匿名用户风险:可能真实存在在数据库的用户,在登录时被当做匿名用户,这时 候访问数据库受到限制
匿名用户对于数据库来说没有任何有价值的操作
真实存在的用户有生产库/业务库的真实权限
但是匿名用户对于数据库自身来说是没有任何风险的
登录数据库后:
mysql> select user,host from mysql.user;
检查下数据库中是否有匿名用户
匿名用户的特点是mysql.user表中user列值=’’;是空不是null.
示例：下面在本机以一个不存在的用户12345来连接MySQL，看他能做些什么

可见，匿名用户有权看到两个数据库：一个名为information_schema的系统数据库和一个test数据库。它对test数据库拥有全部权限，可以在里面创建表，进行DML操作等。对information_schema系统数据库中的表拥有查询权限，但是不能执行DML操作
低版本数据库存在，高版本已经不允许匿名用户登录数据库，系统中不存在匿名用户

02.权限的分类
权限分类:
一.数据库作用范围权限分类:
授权语句:Grant 权限操作分类 on 数据库作用范围权限分类 to 某个用户;
1.全局权限Global Privileges
它是管理权限，应用到服务器上的所有数据库上。要授予全局权限，使用 ON .的语 法。 MySQL 把全局权限保存在 mysql.user 表中
作用在提供mysql服务的实例下所有数据库
授予:.:所有数据库的所有表
记录到mysql.user表中
2.数据库权限Database Privileges
应用到某个特定数据库的所有对象上。要授予数据库权限，使用 ON db_name.* 的语 法。 MySQL 把数据库权限保存在 mysql.db 表中
作用在某些特定数据库
拥有特定数据库的所有权限
授予:拥有db_name.* db_name数据库下的所有表(对象)的权限
记录到mysql.db表中
3.表权限Table Privileges
应用到某个特定表的所有列上。要授予表权限，使用 ON db_name.tbl_name 的语法。 MySQL 把表权限保存在 mysql.tables_priv 表中
作用在特定的表
授予:db_name.table_name
记录到mysql.table_priv表中
二.权限操作分类:逗号分隔
1.列权限Column Privileges
应用到某个特定表的单个列上。要授予列权限，必须在权限名称后面跟小括号，其中写 上列名。 MySQL 把列权限保存在 mysql.columns_priv 表中
grant select (id) on test.a to ‘ceshi2’@‘localhost’;
作用在特定表的特定列上
授予:在权限操作分类中体现列权限 select(col_name)
记录到mysql.columns_priv表中 filed(列,column)
2.存储过程(例程)权限Stored Routine Privileges
应用到存储过程和函数上。 MySQL 把存储例程权限保存在 mysql.procs_priv表中
作用在存储过程和函数
记录到mysql.procs_priv procedure
3.代理用户权限Proxy User Privileges
使一个用户成为另一个用户的代理。 MySQL 把代理用户权限保存在 mysql.proxies_priv 表中
作用是使一个用户成为另一个用户的代理
记录到mysql.proxies_priv表中 proxy
注意:
grant option:表示我自己有的权限可以授权给别人
All:谨慎对待,包含着管理数据库的权限
在mysql.user表中,除了grant_priv是N外,所有的_priv结尾的列值都是Y
Levels:表示级别
event:mysql的时间调度,linux下的crontab
在grant和revoke语句中可用的权限如下表所示：
权限名称 含义及授予级别
ALL [PRIVILEGES] 授予某个特定级别的所有权限，除了 GRANT OPTION权限
ALTER 允许使用ALTER TABLE语句。Levels: Global, database, table
ALTER ROUTINE 允许修改或删除存储例程。Levels: Global, database, procedure
CREATE 允许创建数据库和表。Levels: Global, database, table
CREATE ROUTINE 允许创建存储例程。Levels: Global, database.
CREATE TABLESPACE 允许创建、修改、删除表空间和日志文件组。Level: Global.
CREATE TEMPORARY TABLES 允许创建临时表。Levels: Global, database.
CREATE USER 允许使用 CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES语句。Level: Global.
CREATE VIEW 允许创建和修改视图。Levels: Global, database, table.
SHUTDOWN 允许使用 mysqladmin shutdown。Level: Global.
DELETE 允许删除表中的行。Level: Global, database, table.
DROP 允许删除数据库、表或者视图。Levels: Global, database, table.
EVENT 允许使用事件。Levels: Global, database.
EXECUTE 允许用户执行存储例程。Levels: Global, database, table.
FILE 允许用户使用LOAD DATA INFILE读写文件。 Level: Global.
GRANT OPTION 允许你把自己拥有的权限授给其它用户或者从其它用户那里废除。Levels: Global, database, table, procedure, proxy.
INDEX 允许创建或删除索引。Levels: Global, database, table.
INSERT 允许向表中插入行。Levels: Global, database, table, column.
LOCK TABLES 允许使用LOCK TABLES 语句。 Levels: Global, database.
PROCESS 允许使用SHOW PROCESSLIST语句查看正在运行的进程。 Level: Global.
PROXY 允许用户代理。Level: From user to user.
REFERENCES 允许创建外键。 Levels: Global, database, table, column.
RELOAD 允许使用 FLUSH 语句。 Level: Global.
SELECT 允许查询表。 Levels: Global, database, table, column.
SHOW DATABASES 允许使用SHOW DATABASES 语句。 Level: Global.
SHOW VIEW 允许使用SHOW CREATE VIEW语句。 Levels: Global, database, table.
SUPER 允许使用其它管理语句。例如：CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL和mysqladmin debug 等。Level: Global.
TRIGGER 允许触发器操作。Levels: Global, database, table.
UPDATE 允许更新表中的行。Levels: Global, database, table, column.
USAGE “no privileges”的同义词
权限检查顺序
Mysql数据库下的表user、db、tables_priv、columns_priv、procs_priv、proxies_priv共同构成授权表
权限检查顺序如下图：

03.用户的创建
大写的直接复制
mysql> ? create user
Name: ‘CREATE USER’
Description:
Syntax:(语法)
CREATE USER syntax for MySQL 5.7.6 and higher:
The CREATE USER statement creates new MySQL accounts. It enables
account authentication properties to be established. As of MySQL 5.7.6,
it is also possible to establish authentication, SSL/TLS, and
resource-limit properties, account password expiration, and account
locking and unlocking.
To use CREATE USER, you must have the global CREATE USER privilege or
the INSERT privilege for the mysql database. When the read_only system
variable is enabled, CREATE USER additionally requires the SUPER
privilege.
An error occurs if you try to create an account that already exists.
As of MySQL 5.7.8, the IF NOT EXISTS clause can be used, which causes
the statement to produce a warning for each named account that already
exists, rather than an error.
For each account, CREATE USER creates a new row in the mysql.user
table. The row reflects the properties specified in the statement.
Unspecified properties are set to their default values.
Example 1: Create an account that uses the default authentication
plugin and the given password. Mark the password expired so that the
user must choose a new one at the first connection to the server:
CREATE USER ‘jeffrey’@‘localhost’
IDENTIFIED BY ‘new_password’ PASSWORD EXPIRE;
Example 2: Create an account that uses the sha256_password
authentication plugin and the given password. Require that a new
password be chosen every 180 days:
CREATE USER ‘jeffrey’@‘localhost’
IDENTIFIED WITH sha256_password BY ‘new_password’
PASSWORD EXPIRE INTERVAL 180 DAY;
mysql>

CREATE USER user_specification [, user_specification] …
[REQUIRE {NONE | ssl_option [[AND] ssl_option] …}]
[WITH resource_option [resource_option] …]
[password_option | lock_option] …
user_specification:
user_name [ auth_option ]
auth_option: {认证选项
IDENTIFIED BY ‘auth_string’
| IDENTIFIED BY PASSWORD ‘hash_string’
| IDENTIFIED WITH auth_plugin
| IDENTIFIED WITH auth_plugin AS ‘hash_string’
| IDENTIFIED WITH auth_plugin BY ‘auth_string’
}
ssl_option: {加密连接选项
SSL | X509 | CIPHER ‘cipher’
| ISSUER 'issuer‘ | SUBJECT ‘subject’
}
resource_option: {使用资源选项
MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
}
password_option: {密码过期选项
PASSWORD EXPIRE --立即过期
| PASSWORD EXPIRE DEFAULT --360天
| PASSWORD EXPIRE NEVER
| PASSWORD EXPIRE INTERVAL N DAY
}
lock_option: {账户锁定选项
ACCOUNT LOCK
| ACCOUNT UNLOCK
}

Mysql登录方式:
1.本地socket方式
mysql -uroot -p -S /tmp/mysql.sock
2.网络方式
127.0.0.1-----识别成了localhost ::1
IP地址-------网络

注意：上述语法中，在MySQL 5.7.6之前，只有第1行的create user子句
和其它数据库不同，MySQL使用用户名、密码和登录位置来验证用户
示例1：创建一个新用户demo，密码为demo，允许从本机登录
create user ‘demo’@‘localhost’ identified by ‘demo’;
注意：用户名和主机名要分别用引号括起来(如果用户名没有特殊字符，主机名没有特殊字符和通配符%，也可以不加引号)。密码必须用引号括起来
如果不指定主机名，默认的主机名是百分号%，它代表任意主机
**示例2：*创建一个新用户demo1，密码为demo1，允许从任意主机登录
create user ‘demo1’ identified by ‘demo1’;
或者
create user ‘demo1’ @’%’ identified by ‘demo1’;
示例3：创建一个新用户demo2，没有密码，允许从任意主机登录
create user ‘demo2’;
注意：如果两个用户具有相同的用户名但是主机名不同，MySQL把他们看做不同的用户
如果创建的用户已经存在，则抛出异常
注意:select user();查看当前用户
当create user语句执行成功后，便在授权表mysql.user中写上一行
示例4：查询所有的用户账户
select user,host,authentication_string from mysql.user;
一个新用户创建出来后，它可以连接到数据库，并有权看到information_schema的系统数据库。对information_schema系统数据库中的表拥有查询权限，但是不能执行DML操作。如果还想进行其它操作，必须给他授权
修改用户名
语法：该语句不影响用户的密码
RENAME USER old_user TO new_user
[, old_user TO new_user] …
示例5：将用户demo1和demo2分别改名为newdemo1和newdemo2
rename user
‘demo1’@‘localhost‘ to newdemo1’@‘127.0.0.1’,
‘demo3’@’%’ to ‘newdemo2’@‘127.0.0.1’;
注意:修改主机名的时候一定要写上@主机名,不然系统会删除原来的用户,新建一个用户.
修改密码
SET PASSWORD [FOR user_name] = password_option;
password_option: {
PASSWORD(‘auth_string’)
| ‘auth_string‘ --只适合5.7.6及其之后的版本
| ‘hash_string‘ --只适合5.7.6之前的版本
}
‘auth_string’：未加密的明文字符串密码。PASSWORD函数将其加密后保存。 ‘hash_string’：已经加密的密码
注意：从5.7.6 版本开始，该语句不提倡使用（ is deprecated ）。使用ALTER USER语句代替：
ALTER USER user_name IDENTIFIED BY 'auth_string‘;
示例6：修改自己的密码
set password=password(‘123’);
如果你具有mysql数据库的update权限，就可以修改别人的密码
示例7：修改demo用户的密码
set password for [email protected] =password(‘123’);
04.给用户授权
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 {GRANT OPTION | resource_option} …]
object_type: {
TABLE | FUNCTION | PROCEDURE
}
priv_level: {
* | . | db_name. | db_name.tbl_name
| tbl_name | db_name.routine_name
}
user_specification:
user_name [ auth_option ]
auth_option: { # As of MySQL 5.7.6
IDENTIFIED BY ‘auth_string’
| IDENTIFIED BY PASSWORD ‘hash_string’
| IDENTIFIED WITH auth_plugin
| IDENTIFIED WITH auth_plugin AS ‘hash_string’
| IDENTIFIED WITH auth_plugin BY ‘auth_string’
}
ssl_option: {
SSL | X509 | CIPHER 'cipher’ | ISSUER ‘issuer’
| SUBJECT ‘subject’
}
resource_option: {
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
}
授予表权限
注意：如果被授权的用户不存在，那么grant语句会自动创建新的账户，除非设置参数sql_mode包含“NO_AUTO_CREATE_USER” 。

mysql> grant all on test.a to ‘bbb’@‘localhost’;
ERROR 1133 (42000): Can’t find any matching row in the user table
mysql> grant all on test.a to ‘bbb’@‘localhost’ identified by ‘123’;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
±--------±-----±--------------------------------------------------------------------------------------+
| Level | Code | Message |
±--------±-----±----------------------------------------------------------------------------------------+
| Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |
±--------±-----±---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

授予列权限
示例9：授予用户bob可以对test.a表的id和name列进行更新
grant update(id,name)
on test.a
to ‘bob’@‘localhost’;
注意：权限都是单独授予的，对某个表具有update权限不会导致自动得到select权限
授予数据库权限
表权限只针对某一张表，可针对整个数据库授权
示例10：授予bob可以对test数据库中的所有表进行查询
grant select on test.* to bob;
示例11：授予jim在 test数据库中创建、修改、删除表的权限以及创建视图的权限
grant create,alter,drop,create view on test.* to jim;
授予全局权限
示例12：授予jim可以对当前数据库中的所有表进行查询
grant select on * to jim;
*号表示当前数据库
授予全局权限，应用到所有数据库上
示例13：授予jim可以创建、删除数据库以及对所有数据库中的所有表进行create、alter和drop
grant create,alter,drop on . to jim;
示例14：授予jim可以创建新用户
grant create user on . to jim;
示例15：授予newroot1具有和[email protected]一样的权限
grant all on . to ‘newroot1’@’localhost’ with grant option;
查看自己的权限：
show grants;
权限的传递
with grant option子句
通过在grant语句的最后使用该子句，就允许被授权的用户把得到的权限继续授给其它用户
示例16：授予jim对ceshi表具有select权限，并允许他把权限授给其它用户
Grant select on test.ceshi to jim with grant option;
以jim连接，执行：
grant select on test.ceshi to bob;
05.废除用户权限
废除权限
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] …
示例17：废除aaa对ceshi表的查询权限
REVOKE ALL PRIVILEGES ON test.ceshi from ‘aaa’@‘localhost’;
再以aaa执行查询：
mysql> select * from test.ceshi;
ERROR 1142 (42000): SELECT command denied to user ‘aaa’@‘localhost’ for table ‘ceshi’
mysql>
示例18：废除jim对test.ceshi 表的select权限
revoke select on test.ceshi from jim;
因为jim通过with grant option把该权限又授给了bob，因此bob也失去了对test.ceshi 表的select权限
示例19：要废除jim得到的with grant权限，使用以下语句：
revoke grant option on test.ceshi from jim;
常见问题
1.主机名使用localhost还是127.0.0.1？
使用localhost，是通过socket来连接；
使用127.0.0.1，是通过tcp/ip来连接
2.不要把mysql系统数据库的权限授给用户
3.不要随便授予super权限
4. show privileges;
5.用户具有usage权限意味着“没有权限”，它只表示该用户可以连接到数据库。无法废除该权限