SQL用户和授权

用户

MySQL用户包括普通用户和root用户。两种用户的权限是不一样的。

• root用户是超级管理员，拥有所有的权限；

• root用户的权限包括创建用户、删除用户、修改普通用户的密码等管理权限；

• 而普通用户只拥有创建该用户时赋予它的权限，用户管理包括管理用户的账户、权限等。

权限表

安装MySQL时会自动安装一个名为mysql 的数据库，存储的都是权限表。用户登录之后，MySQL会根据这些权限表的内容为每个用户赋予相应的权限。最重要的是user 表和db 表。

除此之外，还有tables_priv 表、columns_priv 表、proc_priv 表等。

用户表User

user 表是MySQL中最重要的一个权限表，使用desc 语句来查看user 表的基本结构。

db表

db表也是非常重要的权限表，存储了某个用户对一个数据库的权限。

Tables_priv表和Columns_priv表

• tables_priv表可以对单个表进行权限设置。

• columns_priv表可以对单个数据列进行权限设置。

新建普通用户

用create user 语句新建普通用户

CREATE USER <username> [IDENTIFIED BY ‘password’] 
[, username[IDENTIFIED BY ‘password’]]…

username由用户名(User)和主机名(Host)构成; IDENTIFIED BY关键字用来设置用户的密码。

参数说明：

• user_name ：指定创建用户账号，格式为user@host_name

  这里的user_name 是用户名，host_name 是主机名，即用户连接MySQL时所用主机的名字。如果在创建过程中，只给出了用户名，而没指定主机名，那么主机名默认为% ，表示一组主机，即对所有主机开放权限。

• IDENTIFIED BY 子句：用于指定用户密码，新用户可以没有初始密码，若该用户不设密码，可以忽略此子句。

• password ：password 表示用户登录时使用的密码，需要用单引号括起来。

用insert 语句新建普通用户

insert into mysql.user(host, user, authentication_string, ssl_cipher, x509_issuer, 
x509_subject)
Values(‘hostname’,’username’,PASSWORD(‘password’),’’,’’,’’);

注意：由于 mysql 数据库user表中，ssl_cipher、x509_issuer 和 x509_subject 这 3 个字段没有默认值，所以向 user 表插入新记录时，一定要设置这 3 个字段的值，否则 INSERT 语句将不能执行。

用grant 语句新建普通用户

GRANT priv_type ON database.table
TO user[IDENTIFIED BY ‘password’]
[,user[IDENTIFIED BY ‘password’]]…

参数说明：

• priv_type ：表示新用户的权限

• database.table ：表示新用户的权限范围，即只能在指定的数据库和表上使用自己的权限；

删除普通用户

用drop user 语句删除普通用户

drop user user_name [,user_name]...

user_name 参数是需要删除的用户，由用户的用户名（User）和主机名（Host）组成。

用delete 语句删除普通用户

delete from mysql.user where host='hostname' and user='username'

Host 和user 这两个字段都是mysql.user表的主键，这两个字段才能唯一的确定一条记录。

分配权限

MySQL中权限分配是按照user 表、db 表、table_priv 表和columns_priv 表的顺序进行分配的。

• 数据库系统中，先判断user 表中的值是否为Y 。

• 如果user 表中的值为Y ，就不需要检查后面的表。

• 如果user 表的为N ，则依次检查db 表、table_priv 表和columns_priv 表。

授权

授权就是为某个用户赋予某些权限。例如，可以为新建的用户赋予查询所有数据库和表的权限。合理的授权能够保证数据库的安全，不合理的授权会使得数据库存在安全隐患。

• MySQL中使用grant 关键字来为用户设置权限。

• MySQL中，必须拥有grant 权限的用户才可以执行grant 语句。

GRANT priv_type [(column_list)] ON database.table
TO user [ IDENTIFIED BY [PASSWORD] 'password' ] 
[, user [ IDENTIFIED BY [PASSWORD] 'password'] ] ...
[WITH with_option [with_option] ...]

参数说明：

• priv_type ：表示权限类型；

• columns_list ：表示权限作用于哪些列上，省略该参数时，表示作用于整个表；

• database.table ：用于指定权限的级别；

• user ：用户账户，由用户名和主机名构成，格式是username@hostname

• identified by ：用来为用户设置密码；

• password ：用户的新密码；

• WITH 关键字后带有一个或多个with_option 参数，有五个选项：

  • grant option ：被授权的用户可以将这些权限赋予给别的用户；

  • max_queries_per_hour count ：设置每个小时可以允许count次查询；

  • max_updates_per_hour count ：设置每个小时可以允许count次更新；

  • max_connextions_per_hour count ：设置每个小时可以建立count次连接；

  • max_user_connections count ：设置单个用户可以同时具有的count个连接；

收回权限

REVOKE priv_type [(column_list)]...
ON database.table
FROM user [, user] ...

查看权限

MySQL中可以使用select 语句来查询user 表中各个用户的权限，也可以直接使用show grants 来查看权限。mysql数据库下的user 表中存储着用户的基本权限，可以使用select 语句来查看：

SELECT * FROM mysql.user ;

或者

Show grants for ‘username’@’hostname’;