访问控制与安全管理

访问控制与安全管理

数据库服务器通常包含有关键的数据,这些数据的安全和网站可通过访问控制来维护。MySQL中提供来访问控制,以此确保MySQL服务器的安全访问,即用户应该对他们需要的数据具有适当的访问权,既不能多也不能少。因此,MySQL的访问控制就是为用户提供且仅提供他们所需的访问权。

用户账号管理

MySQL的用户账号和相关信息存储在一个名为mysql的MySQL数据库中,这个数据库里有一个名为user的数据表,包含来所有用户账号,并且它用一个名为user的列存储用户的登陆名。这里,可以使用下面的SQL语句查看MySQL数据库的使用账号。

1
select user from mysql.user;

可以看到root用户拥有对整个MySQL服务器完全控制的权限。
在对MySQL的日常管理和实际操作中,为了避免恶意用户冒名使用root账号操纵数据库,通常需要创建一些列具备适当权限的账号,而尽可能地不用或少用root账号登陆系统,以此来确保数据的安全访问。

创建用户账号

可以使用create user语句来创建一个或多个MySQL账户,并且设置相应的口令,其语法格式为:

1
2
create user user_specification
[, user_specification]...

其中,user_specification的格式为:

1
2
3
4
5
user
[
identified by [password] 'password'
| identified with auth_plugin [as 'auth_string']
]

语法说明如下:

  • user:指定创建的用户账号。其格式为‘[email protected] name’。这里,user_name是用户名,host_name为主机名,即用户连接MySQL时所在主机的名字。如果在创建的过程中,只给出了账户中的用户名,而没有设定主机名,这主机名会被默认为是“%”,表示一组主机。
  • identified by 子句:用于指定用户账号对应的口令,若该用户账号无口令,则可省略此子句。
  • 可选项password:用于指定散列口令,即若使用明文设置口令,需忽略password关键字;如果不想以明文设置口令,且知道password()函数返回给密码的散列值,这可以在此口令设置语句中指定此散列值,但需要加上关键字password。
  • password:指定用户账号和口令,在identified by关键字或password关键字之后,给定的口令值可以是只由字母和数字组成的明文,也可以是通过password()函数得到的散列值。
  • identified with子句:用于指定验证用户账号的认证插件。
  • auth_plugin:指定认证插件的名称,紧跟在identified with关键字之后。
注意:在MySQL8.0中,password()函数已经被删除了!

例如,在MySQL服务器中添加两个新的用户,其用户名分别为zhangsan和lisi,他们的主机名均为localhost,密码分别为名为“123”和“456”。

1
2
3
4
5
6
# 在MySQL服务器中添加两个新的用户,其用户名分别为zhangsan和lisi,
# 他们的主机名均为localhost,密码分别为名为“123”和“456”。
select md5('456');
create user
'zhangsan'@'localhost' identified by '123',
'lisi'@'localhost' identified by '456';

create user语句的使用说明如下:

  • 要使用create user语句,必须拥有MySQL中mysql数据库的insert权限或全局create user权限。
  • 使用create user语句创建一个用户账号之后,会在系统自身的mysql数据库的user表中添加一条新纪录。如果创建的账户已经存在,则语句执行会出错。
  • 如果两个用户具有相同的用户名和不同的主机名,MySQL会将他们视为不同的用户,并允许为则两个用户分配不同的权限集合。
  • 如果create user语句的使用中没有为用户指定口令,那么MySQL允许用户可以不使用口令登陆账户。
  • 新茶创建的用户拥有的权限很少。他们可以登录到MySQL,只允许进行不需要权限的操作,如使用show语句查询所有存储引擎和字符集的列表等。不能使用use语句来让其他用户已经创建的任何数据库成为当前数据库,因而无法访问相关数据库的表。

删除用户

1
drop user user [, user] ...

语法说明如下:

  • drop user语句可以用于删除一个或多个MySQL账户,并消除其权限。
  • 要使用drop user语句,必须拥有MySQL中mysql数据库的delete权限或全局alter user权限。
  • 在drop user语句的使用中,如果没有明确地给出账户的主机名,则默认为“%”。
  • 用户的删除不会影响到他们之前所创建的表,索引或其他数据库对象,这是因为MySQL并没有记录是谁创建了这些对象。

例如,删除lisi:

1
drop user [email protected];

修改用户账号

1
rename user old_user to new_user [, old_user to new_user ] ...

例如,将前面的用户zhangsan的名字修改为jwen:

1
2
rename user [email protected] to [email protected];
select user from mysql.user;

  • rename user语句用于对原有MySQL账户进行重命名。
  • 要使用rename user语句,必须拥有MySQL中mysql数据库的update权限或create user权限。
  • 倘若系统中就账户不存在或者新账户已存在,则语句执行会出错。

修改用户口令

1
set password [for user] = ' password'

例如:

1
set password for [email protected] = '12345';

或者使用下面的语句:

1
alter user [email protected] identified by '123';

  • 若是不加上for子句,表示修改当前用户的口令。

账户权限管理

成功创建用户账号后,需要为用户分配适当的访问权限,因为新创建的用户没有访问权限,只能登录MySQL服务器,不能执行任何数据库操作。例如,show grants for语句就能查看某个用户的授权表:

1
2
3
show grants for [email protected];
-- 结果:
GRANT USAGE ON *.* TO `jwen`@`localhost`

输出结果表明该用户只有一个权限usage on *.*,表示该用户对任何数据库和任何表都没有权限。

权限的授予

grant语句的语法格式为:

1
2
3
4
5
6
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的格式为:

1
table | function | procedure

priv_level的格式为:

1
* | *.* | db_name.* | db_name.tbl_name | tbl_name | db_name.routine_time

user_specification的格式为:

1
2
3
4
5
user
[
identified by 'password'
| identified with auth_plugin [as 'auth_string']
]

with_option的格式为:

1
2
3
4
5
grant option
| max_queries_per_hour count
| max_updates_per_hour cout
| max_connections_per_hour count
| max_user_connections count

语法说明如下:

  1. priv_type:用于指定授予权限的名称,如select、update、delete等操作;
  2. 可选项column_list:用于指定权限要授予给表中那些具体的列;
  3. on子句:用于指定权限授予的对象和级别,如可在on关键字后面给出要授予权限的数据库名或表名等;
  4. 可选项object_type:用于指定权限授予的对象类型,包括表、函数和存储过程,分别用关键字table、function和procedure标识。
  5. priv_level:用于指定权限的级别。可以授予的权限有如下几组:

    • 列权限,其和表中的一个具体的列有关。例如,可以使用update语句更新表tb_student中的studentName列的值的权限;
    • 表权限,其和一个具体表中的所有数据相关。例如,可以使用select语句查询表tb_student的所有数据的权限。
    • 数据库权限,其和一个具体的数据中所有表相关。例如,可以在已有的数据库db_school中创建新表的权限。
    • 用户权限,其和MySQL中所有的数据库相关。例如,可以删除已有的数据库或者创建一个新的数据库的权限。
    • 对应地,在grant语句中可以用于指定权限级别的值有如下几类格式:
      • *:表示当前数据库中的所有表;
      • *.*:表示所有数据库中的所有表;
      • db_name.*:表示某个数据库中的所有表;
      • db_name.tbl_name:表示某个数据库中的某个表或视图;
      • tbl_name:表示某个表或视图;
      • db_name.routine_time:表示某个数据库中的某个存储过程或函数。
  6. to子句:用来设定用户的口令,以及指定授予权限的用户user。若to子句中给系统中存在的用户指定口令,则新密码会覆盖原密码;如果权限被授予给一个不存在的用户,MySQL会自动执行一条create user语句创建这个用户,但同时必须为该用户指定口令。由此可见,grant语句也可以用来创建用户账号。

  7. user_specification:to子句中的具体描述部分,其与create user语句汇总的user_specification部分一样。
  8. with子句:grant语句的最后可以使用with子句,为可选项,其用于实现权限的转移和限制。

【例如】 授予用户jwen在数据库db_school的表tb_student上拥有对studentNo和列studentName的select权限:

1
2
3
4
use db_school;
grant select(studentNo, studentName)
on db_school.tb_student
to [email protected];

【例如】 当前系统中不存在用户liming和huang,要求创建这两个用户,并为其设置登录口令,同时授予他们在数据库db_school的表tb_student上拥有select和update的权限。

1
2
3
4
5
-- 错误!
grant select, update
on db_school.tb_student
to 'liming'@'localhost' identified by '123',
'huang'@'localhost' identified by '789';

Error!:上面的语句无法正常允许,似乎是最新版本的MySQL已经丢弃了这种用法,所以还是先创建用户再授予权限吧!
1
2
3
4
5
6
create user if not exists
[email protected] identified by '123',
[email protected] identified by '123';
grant select, update
on db_school.tb_student
to [email protected], [email protected];

【例如】 授予系统中已存在用户jwen可以在数据库中执行所有数据库操作的权限。

1
2
grant all on db_school.*
to [email protected];

【例如】 授予用户jwen拥有创建用户的权限。

1
2
grant create user on *.*
to [email protected];

grant语句中priv_type的使用说明如下:

  1. 授予表时,priv_type可以指定为以下值:
    • select: 授予用户可以使用select语句访问特定表的权限;
    • insert:授予用户可以使用insert语句向一个特定表中添加数据行的权限;
    • delete:授予用户可以使用delete语句从一个特定表中删除数据行的权限;
    • update:授予用户可以使用update语句从一个特定表中删除数据行的权限;
    • reference:授予用户可以创建一个外键来参照特定数据表的权限;
    • create:授予用户可以使用特定名字创建一个数据表的权限;
    • alter:授予用户可以使用alter table语句修改数据表的权限;
    • index:授予用户可以在表上定义索引的权限;
    • drop:授予用户可以删除数据表的权限;
    • all或all privileges:表示所有权限。
  2. 授予列权限时,priv_type可以被指定为以下值:

    • select:授予用户使用select语句访问特定数据库中所有表和视图的权限;
    • insert:授予用户可以使用insert语句向特定数据库中所有表添加数据行的权限;
    • delete:授予用户可以使用delete语句删除特定数据库中所有表的数据行的权限;
    • update:授予用户可以使用update语句更新特定数据库中的所有数据表的值的权限;
    • references:授予用户可以创建指向特定数据库中的表外键的权限;
    • create:授予用户可以使用create table语句在特定数据库中创建新表的权限;
    • alter:授予用户可以使用alter table语句在特定数据库中修改所有数据表的权限;
    • index:授予用户可以在特定数据库中的所有数据表上定义和删除索引的权限;
    • drop:授予用户可以删除特定数据库中所有表和视图的权限;
    • create temporary tables:授予用户可以在特定数据库中创建临时表的权限;
    • create view:授予用户可以在特定数据库中创建新的视图的权限;
    • show view:授予用户可以查看特定数据库中已有视图的视图定义的权限;
    • create routine:授予用户可以为特定数据库创建存储过程和存储函数等的权限;
    • alter routine:授予用户可以更新和删除数据库中已有的存储过程和存储函数的权限;
    • execute routine:授予用户可以调用特定数据库的存储过程和存储函数的权限;
    • lock tables:授予用户可以锁定特定数据库的已有数据表的权限;
    • all / all privileges:授予用户所有权限.
  3. 最有效率的权限是用户权限。授予用户权限时,priv_type除了可以指定为授予数据库权限时的所有值之外,还可以是下面的值:

    • create user:授予用户可以创建和删除新用户的权限;
    • show databases:授予用户可以使用show databases语句查看所有已有的数据库的定义的权限。

权限的转移和限制

权限的转移和限制可以通过grant语句中的with子句来实现。

  1. 转移权限
    如果with子句指定为with grant option,则表示to子句中所有的用户都具有把子句所拥有的权限授予其他用户的权利,而无论那些其他用户是否具有该权限。

【例如】 授予当前系统中的liming在数据库db_school的表tb_student上拥有select和update的权限,并允许其可以将自身的这个权限授予其他用户。

1
2
3
4
grant select, update
on db_school.tb_student
to [email protected]
with grant option;

  1. 限制权限
    如果with子句中with关键字后面紧跟的是max_queries_per_hour等,则该grant语句可用于限制权限。
    【例如】 授予系统中的用户huang在数据库db_school的表tb_student上每个小时只能处理一条delete语句的权限:
    书上的代码允许会出错,我用了下面的语句:
    1
    2
    3
    4
    grant delete
    on db_school.tb_student
    to [email protected];
    alter user [email protected] with max_queries_per_hour 1;

权限的撤销

语法格式为:

1
2
3
revoke priv_type [(column_list)] [, priv_type[(column_list)]] ...
on [object_type] priv_level
from user [, user] ...

1
2
revoke all privileges, grant option
from user [, user]

要使用revoke语句,必须具有mysql数据库的全局create user权限或update权限。

【例如】 回收jwen在数据库db_school的表tb_student上的select权限.

1
2
3
revoke select
on db_school.tb_student
from [email protected];

# MySQL

评论

程振兴

程振兴 @czxa.top
截止今天,我已经在本博客上写了659.4k个字了!

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×