mysql的权限系统

6.1 mysql的权限系统

6.1.1 权限表

? 客户对mysql数据库的访问权限由权限表来控制，表位于mysql数据库中，

并在第一次安装mysql的过程中初始化。

? Mysql数据库权限表由6个表组成：user表、db表、host表、table_priv表、

columns_priv表、proc_priv表 1、权限表user、db和host的结构和作用 （1）user表

? Mysql中最重要的一个权限表，记录允许连接到服务器的账号信息。 ? 列出可以连接到服务器的用户及其口令，并指定他们有哪种全局权限 ? User表中启用的任何权限都是全局权限，适用于所有数据库

? Mysql5.5中，user表有42个字段，分为4类：用户列、权限列、安全列和

资源控制列

A、用户列：分别表示主机名、用户名和密码，其中user和host为联合主键。 B、权限列：包括以select_priv、insert_priv等以priv结尾的字段这些字段决定用户的权限，描述了在全局范围内允许对数据和数据库进行的操作。 C、安全列:安全列有6个字段其中2个和ssl有关（用于加密）、2个和x509有关（用于标识用户）、2个与授权插件有关（用于验证用户）。

D、资源控制列:用来限制用户使用的资源。认值为0，表示没有限制。 （2）db表和host表

? Db表中存储用户对某个数据库的操作权限，决定用户能从哪个数据库中存取

数据。

? Host表中存储了某个主机对数据库的操作权限，配合db权限表对给定主机

上数据库操作权限做更细致的控制。 注意：

? User表的权限是针对所有数据库的。

? 如果希望用户只对某个数据库有操作权限，需要将user表中对应的权限设置

为N，然后在db表中设置对应数据的操作权限。

? 用户先根据user表获取权限，然后再根据db表的内容获取权限 2、table_priv表、colunms_priv表和proc_priv表 ? table_priv表：对表设置操作权限

? colunms_priv表：对表的某一列设置操作权限 ? proc_priv表：对存储过程和存储函数设置操作权限 6.1.2 mysql权限系统的工作原理

? 当mysql允许一个用户执行各种操作时，首先核实用户向服务器发送的连接

请求，然后确认用户的操作请求是否被允许。

? Mysql的访问控制分为两个阶段：连接核实和请求核实阶段。 1、 连接核实阶段

使用user表的3个字段（host、user和password）进行身份验证，服务器只有在用户提供给主机名、用户名和密码并与user表中对应的字段值完全匹配时才能接受连接。 2、 请求核实阶段

接收到用户操作请求检查user表中权限检查db表和host表中权限检查table_priv表中权限检查column_priv表中权限是否允许用户执行该操作

6.1.3 账户管理 1、创建账户

? 创建新用户，必须有相应的权限 ? Mysql有三种方法创建新用户：

图形工具：navicat

SQL语句：create user或grant 直接操作权限表（不推荐） （1）使用navicat工具创建用户 （2）使用create user创建用户

例1使用CREATE USER创建一个用户，用户名是rjxy，密码是mypass，主机名是localhost

CREATE USER 'rjxy'@'localhost' IDENTIFIED BY 'mypass';

例2添加一个新用户，用户名为bana,密码为440432，不指定明文。 首先，使用password()获取密码的散列值： Select password(440432);

创建用户：

Create user 'bana'@'localhost'

Identified by password '*8896757F25D7B730D7A72894FE06257623CF99B9'; 验证：

A． 登录navicat界面查看用户：用户管理、user表。

B． 以bana用户登录mysql：

mysql–hlocalhost–ubana-p 打开数据库 Use stu_info;

（3）使用grant创建用户

例3使用GRANT语句创建一个新的用户testUser，密码为testuser。用户testUser对所有的数据有查询和更新权限，并授于对所有数据表的SELECT和UPDATE权限。 #创建账户并授予权限：

GRANT SELECT,UPDATE ON *.* TO 'testUser'@'localhost' IDENTIFIED BY 'testUser'; #查看账户权限信息（注意用户名区分大小写）：

selecthost,user,select_priv,update_priv from mysql.user where user='testUser';

（4）直接操作mysql用户表

例4使用INSERT创建一个新账户，其用户名称为student，主机名称为localhost，密码为student： #创建用户

INSERT INTO mysql.user (Host,User,Password) VALUES('localhost','student',PASSWORD('student')); #激活用户 Flush privileges;

#查看用户信息

Select host,user from mysql.user; 2、删除账户

（1）使用navicat图形工具删除用户 （2）使用drop user删除用户

例5 使用DROP USER删除用户'rjxy'@'localhost' DROP USER 'rjxy'@'localhost'; #查看用户信息

Select host,user from mysql.user; （3）使用delete删除用户

例6 使用DELETE删除用户'student'@'localhost'

Delete FROMmysql.userWHERE host='localhost' and user='student'; #查看用户信息

Select host,user from mysql.user; 3、 修改账户名称

（1）使用navicat图形工具修改用户 （2）使用rename user修改用户 例7将用户bana的名字改为stu。

Rename user 'bana'@'localhost' to 'stu'@'localhost'; #查看用户信息

Select host,user from mysql.user; 4、修改账户密码 （1）root用户修改密码

? 使用mysqladmin命令修改密码

例8使用mysqladmin将root用户的密码修改为“rootpwd” mysqladmin -u root -p password \? 使用update命令修改密码

例9使用UPDATE语句将root用户的密码修改为“123456”： UPDATE mysql.user set Password=password(\WHERE User=\

#重新加载权限表 Flush privileges;

例10 使用UPDATE语句将testUser用户的密码修改为“newpwd2”： UPDATE mysql.user set Password=PASSWORD(\WHERE User=\#重新加载权限表 Flush privileges;

? 使用GRANT命令修改密码

例11 使用GRANT语句将testUser用户的密码修改为“123456”： GRANT USAGE ON *.* TO 'testUser'@'localhost' IDENTIFIED BY '123456'; （2）使用set语句修改密码

例12 使用SET语句将root用户的密码修改为“123456789”： SET PASSWORD=password(\

例13 使用SET语句将testUser用户的密码修改为“123456789”： SET PASSWORD FOR 'testUser'@'localhost'=password(\6.1.4 权限管理 1、mysql的权限类型 权限名称 Select insert Update Delete Create Drop Reload Shutdown Process File Grant option 对应user表列 Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv 权限范围 表或列 表 表或列 表 数据库、表、索引 数据库、表、视图 服务器上的文件 服务器管理 存储过程或函数 服务器上的文件 数据库、表、存储过程 References Index Alter Show database Super Createtmp_table Locktables Execute References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv 数据库、表 索引查询的表 数据库 服务器管理 服务器管理 表 表 存储过程或函数 服务器管理 服务器管理 视图 视图 存储过程或函数 存储过程或函数 服务器管理 数据库 Replication slave Repl_slave_priv Replication client Create view Show view Createroutine Alterroutine Create user event 2、授权 （1）权限级别

? 全局层级：grant all on *.*

? 数据库层级：grant all on db_name.*

Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv ? 表层级：grant on table_name或database_name.table_name ? 列层级 ? 子程序层级 （2）授权语句

例14 使用GRANT语句创建一个新的用户aaaa，密码为“123456”。用户aaaa对所有的数据有查询、插入权限，并授于GRANT权限。 GRANT语句：

GRANT SELECT,INSERT ON *.* TO 'aaaa'@'localhost' IDENTIFIED BY '123456'

WITH GRANT OPTION;

使用SELECT语句查询用户权限：

SELECT Host,User,Select_priv,Insert_priv, Grant_priv FROM mysql.user where user='aaaa';

例15 使用grant语句将test数据库中student表的delete权限授予用户testUser。 GRANT deleteON test.student TO 'testUser'@'localhost'

例16使用grant语句将test数据库中的sc表的degree列的update权限授予用户testUser。

GRANT update(degree) ON test.sc TO 'testUser'@'localhost' 3、收回权限 （1）收回所有权限

例17收回aaaa用户的所有权限，包括grant权限。 REVOKE all privileges,grantoption FROM 'aaaa'@'localhost'; （2）收回指定权限

例18使用REVOKE语句取消用户testUser的更新权限。 #REVOKE语句：

MySQL> REVOKE UPDATE ON *.* FROM 'testUser'@'localhost'; #使用SELECT语句查询用户test的权限：

MySQL> SELECT Host,User,Select_priv,Update_priv,Grant_priv FROM MySQL.user where user='testUser';

例19 收回testUser用户对sc表的degree列的uodate权限。

MySQL> REVOKE UPDATE(degree ) ON test.sc FROM 'testUser'@'localhost'; 4、查看权限

例20 使用SHOW GRANTS语句查询用户testUser的权限信息。SHOW GRANTS语句及其执行结果如下：

MySQL> SHOW GRANTS FOR 'testUser'@'localhost';

案例操作过程

打开MySQL客户端工具，输入登录命令，登录MySQL。 C:\\>mysql -u root -p Enter password: **

输入正确密码，按回车，出现欢迎信息表示登录成功。 选择mysql数据库为当前数据库。 MySQL> use mysql; Database changed

出现Database changed信息表明切换数据库成功。

创建新账户，用户名称为newAdmin，密码为pw1，允许其从本地主机访问MySQL。 使用GRANT语句创建新账户，创建过程如下： MySQL> GRANT SELECT, UPDATE(id, name, age) -> ON test_db.person_old

-> TO 'newAdmin'@'localhost' IDENTIFIED BY 'pw1' -> WITH MAX_CONNECTIONS_PER_HOUR 30; Query OK, 0 rows affected (0.06 sec) 提示消息可以看到，语句执行成功。

分别从user表中查看新账户的账户信息，从tables_priv和columns_priv表中查看权限信息。

用户账户创建完成之后，账户信息已经保存在user表，权限信息则分别保存在tables_priv和columns_priv中，查询user名称为newAdmin的账户信息，执行过程如下：

SELECT host, user, select_priv, update_priv FROM user WHERE user='newAdmin';

SELECT host, db, user, table_name, table_priv, column_priv FROM tables_priv WHERE user='newAdmin';

SELECT host, db, user, table_name, column_name, column_priv FROM columns_priv WHERE user='newAdmin';

3条SQL语句的查询结果分别如下：

MySQL> SELECT host, user, select_priv, update_priv FROM user WHERE user='newAdmin';

+-----------+--------------+-------------+--------------+ | host | user | select_priv | update_priv | +-----------+--------------+-------------+--------------+ | localhost | newAdmin | N | N | +-----------+--------------+-------------+--------------+ 1 row in set (0.00 sec)

MySQL> SELECT host, db, user, table_name, table_priv, column_priv -> FROM tables_priv WHERE user='newAdmin'; +-----------+-----+-------------+-------------+-------------+---------------+

| host | db | user | table_name | table_priv | column_priv | +-----------+-----+-------------+-------------+-------------+---------------+

| localhost | test | newAdmin | person | Select | Update | +-----------+-----+-------------+-------------+-------------+---------------+ 1 row in set (0.00 sec)

MySQL> SELECT host, db, user, table_name, column_name, column_priv -> FROM columns_priv WHERE user='newAdmin'; +-----------+-----+-------------+-------------+-----------------+----------------+

| host | db | user | table_name | column_name | column_priv | +-----------+-----+-------------+-------------+-----------------+----------------+

| localhost | test | newAdmin | person | id | Update | | localhost | test | newAdmin | person | name | Update | | localhost | test | newAdmin | person | age | Update | +-----------+-----+-------------+-------------+-----------------+----------------+ 3 rows in set (0.00 sec)

使用SHOW GRANTS语句查看newAdmin的权限信息。 查看newAdmin账户的权限信息，输入语句如下： SHOW GRANTS FOR 'newAdmin'@'localhost'; 执行结果如下：

+-----------------------------------------------------------------------------------------------------------------------+ | |

+-----------------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'newAdmin'@'localhost' IDENTIFIED BY PASSWORD '*2B602296

A79E0A8784ACC5C88D92E46588CCA3C3' WITH MAX_CONNECTIONS_PER_HOUR 30 |

| GRANT SELECT, UPDATE (age, id, name) ON `test`.`person` TO 'newAdmin'@'localhost' |

+-----------------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

使用newAdmin用户登录MySQL。

退出当前登录，使用EXIT命令，语句如下： MySQL> exit Bye

使用newAdmin账户登录MySQL，语句如下： C:\\>MySQL -u newAdmin -p Enter password: ***

输入密码正确后，出现“mysql>”提示符，登录成功。

使用newAdmin用户查看test_db数据库中person_dd表中的数据。

newAdmin用户被授予test数据库中person表中3个字段上的查询权限，因此

Grants

for

newAdmin@localhost

可以执行SELECT语句查看这几个字段的值，执行过程如下： MySQL> SELECT * FROM test_db.person_dd LIMIT 5; +----+----------+-----+--------------+ | id | name | age | info | +----+----------+-----+--------------+ | 1 | Green | 21 | Lawyer | | 2 | Suse | 22 | dancer | | 3 | Mary | 24 | Musician | | 4 | Willam | 20 | sports man | | 5 | Laura | 25 | NULL | +----+----------+-----+--------------+ 5 rows in set (0.00 sec)

可以看到，查询结果显示了表中的前5条记录。

使用newAdmin用户向person_dd表中插入一条新记录，查看语句执行结果。 插入新记录，输入语句如下：

INSERT INTO test_db.person_old(name, age,info) VALUES('gaga', 30); 执行结果如下：

ERROR 1142 (42000): INSERT command denied to user 'newAdmin'@'localhost' for table 'person'

可以看到，语句不能执行，错误信息表明newAdmin用户不能对person表进行插入操作。因此，用户不可以执行没有被授权的操作语句。

退出当前登录，使用root用户重新登录，收回newAdmin账户的权限。 输入退出命令： exit

重新以root用户登录MySQL，并选择mysql数据库为当前数据库。 输入语句收回newAdmin账户的权限，执行过程如下：

REVOKE SELECT, UPDATE ON test.person FROM 'newAdmin'@'localhost'; 执行结果如下：

MySQL> REVOKE SELECT, UPDATE ON test.person FROM 'newAdmin'@'localhost'; Query OK, 0 rows affected (0.00 sec)

删除newAdmin的账户信息。

删除指定账户，可以使用DROP USER语句，输入如下： DROP USER 'newAdmin'@'localhost';

语句执行成功之后，tables_priv和columns_priv中相关的记录将被删除。

本文来源：https://www.bwwdw.com/article/51ro.html