mysql的权限系统.docx

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、 请求核实阶段6.1.3 账户管理1、创建账户 创建新用户，必须有相应的权限 Mysql有三种方法创建新用户：图形工具：navicatSQL语句：create user或grant直接操作权限表（不推荐）（1）使用navicat工具创建用户（2）使用create user创建用户例1使用CREATE USER创建一个用户，用户名是rjxy，密码是mypass，主机名是localhostCREATE USER rjxylocalhost IDENTIFIED BY mypass;例2 添加一个新用户，用户名为bana,密码为440432，不指定明文。首先，使用password()获取密码的散列值：Select password(440432);创建用户： Create user banalocalhost Identified by password *8896757F25D7B730D7A72894FE06257623CF99B9;验证：A 登录navicat界面查看用户：用户管理、user表。B 以bana用户登录mysql：mysql h localhost u bana -p 打开数据库Use stu_info;（3）使用grant创建用户例3 使用GRANT语句创建一个新的用户testUser，密码为testuser。用户 testUser对所有的数据有查询和更新权限，并授于对所有数据表的SELECT和UPDATE权限。#创建账户并授予权限：GRANT SELECT,UPDATE ON *.* TO testUserlocalhost IDENTIFIED BY testUser; #查看账户权限信息（注意用户名区分大小写）：select host,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删除用户rjxylocalhostDROP USER rjxylocalhost;#查看用户信息Select host,user from mysql.user; （3）使用delete删除用户例6 使用DELETE删除用户studentlocalhostDelete FROM mysql.user WHERE host=localhost and user=student;#查看用户信息Select host,user from mysql.user; 3、 修改账户名称（1）使用navicat图形工具修改用户（2）使用rename user修改用户例7 将用户bana的名字改为stu。Rename user banalocalhost to stulocalhost;#查看用户信息Select host,user from mysql.user; 4、修改账户密码（1）root用户修改密码 使用mysqladmin命令修改密码例8使用mysqladmin将root用户的密码修改为“rootpwd”mysqladmin -u root -p password 123456 使用update命令修改密码例9使用UPDATE语句将root用户的密码修改为“123456”：UPDATE mysql.user set Password=password(123456)WHERE User=root and Host=localhost;#重新加载权限表Flush privileges;例10 使用UPDATE语句将testUser用户的密码修改为“newpwd2”：UPDATE mysql.user set Password=PASSWORD(newpwd2)WHERE User=testUser and Host=localhost;#重新加载权限表Flush privileges; 使用GRANT命令修改密码例11 使用GRANT语句将testUser用户的密码修改为“123456”：GRANT USAGE ON *.* TO testUserlocalhost IDENTIFIED BY 123456;（2）使用set语句修改密码例12 使用SET语句将root用户的密码修改为“123456789”：SET PASSWORD=password(123456789);例13 使用SET语句将testUser用户的密码修改为“123456789”：SET PASSWORD FOR testUserlocalhost=password(123456789);6.1.4 权限管理1、mysql的权限类型权限名称对应user表列权限范围SelectSelect_priv表或列insertInsert_priv表UpdateUpdate_priv表或列DeleteDelete_priv表CreateCreate_priv数据库、表、索引DropDrop_priv数据库、表、视图ReloadReload_priv服务器上的文件ShutdownShutdown_priv服务器管理ProcessProcess_priv存储过程或函数FileFile_priv服务器上的文件Grant optionGrant_priv数据库、表、存储过程ReferencesReferences_priv数据库、表IndexIndex_priv索引查询的表AlterAlter_priv数据库Show databaseShow_db_priv服务器管理SuperSuper_priv服务器管理Create tmp_tableCreate_tmp_table_priv表Lock tablesLock_tables_priv表ExecuteExecute_priv存储过程或函数Replication slaveRepl_slave_priv服务器管理Replication clientRepl_client_priv服务器管理Create viewCreate_view_priv视图Show viewShow_view_priv视图Create routineCreate_routine_priv存储过程或函数Alter routineAlter_routine_priv存储过程或函数Create userCreate_user_priv服务器管理eventEvent_priv数据库2、授权（1）权限级别 全局层级：grant all on *.* 数据库层级：grant all on db_name.* 表层级：grant on table_name或database_name.table_name 列层级 子程序层级（2）授权语句例14 使用GRANT语句创建一个新的用户aaaa，密码为“123456”。用户aaaa对所有的数据有查询、插入权限，并授于GRANT权限。GRANT语句：GRANT SELECT,INSERT ON *.* TO aaaalocalhost 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 delete ON test.student TO testUserlocalhost 例16 使用grant语句将test数据库中的sc表的degree列的update权限授予用户testUser。GRANT update(degree) ON test.sc TO testUserlocalhost 3、收回权限（1）收回所有权限例17 收回aaaa用户的所有权限，包括grant权限。REVOKE all privileges,grant option FROM aaaalocalhost;（2）收回指定权限例18 使用REVOKE语句取消用户testUser的更新权限。#REVOKE语句：MySQL REVOKE UPDATE ON *.* FROM testUserlocalhost;#使用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 testUserlocalhost;4、查看权限例20 使用SHOW GRANTS语句查询用户testUser的权限信息。SHOW GRANTS语句及其执行结果如下：MySQL SHOW GRANTS FOR testUserlocalhost; 案例操作过程 打开MySQL客户端工具，输入登录命令，登录MySQL。C:mysql -u root -pEnter 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 newAdminlocalhost 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 newAdminlocalhost;执行结果如下：+-+| Grants for newAdminlocalhost |+-+| GRANT USAGE ON *.* TO newAdminlocalhost IDENTIFIED BY PASSWORD *2B602296A79E0A8784ACC5C88D92E46588CCA3C3 WITH MAX_CONNECTIONS_PER_HOUR 30 | GRANT SELECT, UPDATE (age, id, name) ON test.person TO newAdminlocalhost |+-+2 rows in set (0.00 sec) 使用newAdmin用户登录MySQL。退出当前登录，使用EXIT命令，语句如下：MySQL exitBye使用newAdmin账户登录MySQL，语句如下：C:MySQL -u newAdmin -pEnter password: *输入密码正确后，出现“mysql”提示符，登录成功。 使用newAdmin用户查看test_db数据库中person_dd表中的数据。newAdmin用户被授予test数据库中person表中3个字段上的查询权限，因此可以执行SELECT语句查看这几个字段的值，执行过程如下：MySQL SELECT * FROM test_db.person_dd LIMIT 5;+-+-+-+-+| id | name | age | info |+-+-+-+-+| 1 | Green |