MySQL用户与权限管理简介
发布时间：2022-01-16 11:30:00 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要介绍MySQL用户与权限管理介绍，在日常操作中，相信很多人在MySQL用户与权限管理介绍问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答MySQL用户与权限管理介绍的疑惑有所帮助！接下来，请跟着小编一起来学习吧！ M
|
这篇文章主要介绍“MySQL用户与权限管理介绍”，在日常操作中，相信很多人在MySQL用户与权限管理介绍问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MySQL用户与权限管理介绍”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！ MySQL权限系统的主要功能是证实连接到一台给定主机的用户，并且赋予该用户在数据库上的相关DML、DQL权限。MySQL存取控制包含2个阶段，一是服务器检查是否允许连接；二是假定能连接，服务器检查发出的每个请求。看是否有足够的权限实施它。本文主要描述MySQL权限系统相关的用户创建、授权、撤销权限等等。 1、获取有关权限管理的帮助 root@localhost[(none)]> help Account Management For more information, type 'help ', where is one of the following topics: You asked for help about help category: "Account Management" CREATE USER DROP USER GRANT RENAME USER REVOKE SET PASSWORD  2、创建mysql数据库用户 --创建用户的语法 root@localhost[(none)]> help create user; Name: 'CREATE USER' Description: Syntax: CREATE USER user_specification [, user_specification] ... user_specification: user [ | IDENTIFIED WITH auth_plugin [AS 'auth_string'] IDENTIFIED BY [PASSWORD] 'password' ] create user命令会创建一个新帐户，同时也可以为其指定密码。该命令将添加一条记录到user表。 该命令仅仅授予usage权限。需要再使用grant命令进行进一步授权。也可以使用grant命令直接来创建账户见后续的相关演示。 下面是mysql官方手册对usage的解释。 The USAGE privilege specifier stands for “no privileges.” It is used at the global level with GRANT to modify account attributes such as resource limits or SSL characteristics without affecting existing account privileges. --当前演示环境 root@localhost[(none)]> show variables like 'version'; +---------------+------------+ | Variable_name | Value | +---------------+------------+ | version | 5.5.39-log | +---------------+------------+ --创建新用户(未指定密码) root@localhost[(none)]> create user 'fred'@'localhost'; Query OK, 0 rows affected (0.00 sec) --指定密码创建新用户,%表示任意，即frank可以从任意主机访问数据库 root@localhost[(none)]> create user 'frank'@'%' identified by 'frank'; Query OK, 0 rows affected (0.00 sec) --查看刚刚添加的账户 root@localhost[(none)]> select host,user,password from mysql.user where user like 'fr%'; +-----------+-------+-------------------------------------------+ | host | user | password | +-----------+-------+-------------------------------------------+ | % | frank | *63DAA25989C7E01EB96570FA4DBE154711BEB361 | | localhost | fred | | +-----------+-------+-------------------------------------------+ 3、使用grant授予权限 --grant命令语法 root@localhost[mysql]> help grant Name: 'GRANT' Description: Syntax: 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 ...] GRANT PROXY ON user_specification TO user_specification [, user_specification] ... [WITH GRANT OPTION] object_type: TABLE | FUNCTION | PROCEDURE priv_level: * | *.* | db_name.* | db_name.tbl_name | tbl_name | db_name.routine_name user_specification: user [ | IDENTIFIED WITH auth_plugin [AS 'auth_string'] IDENTIFIED BY [PASSWORD] 'password' ] 如何授权 a、需要指定授予哪些权限 b、权限应用在那些对象上(全局，特定对象等) c、授予给哪个帐户 d、可以指定密码(可选项,用此方式会自动创建用户) 授权权限的范围： ON *.* ON db_name.* ON db_name.table_name ON db_name.table_name.column_name ON db_name.routine_name --权限一览表，我们直接查询root账户所有的权限，如下 --mysql的权限相对于Oracle而言，相对简单，而且也没有涉及到角色方面的定义与配置 root@localhost[(none)]> select * from mysql.user where user='root' and host='localhost'G *************************** 1. row *************************** Host: localhost User: root Password: Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y Event_priv: Y Trigger_priv: Y Create_tablespace_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: authentication_string: 1 row in set (0.00 sec) --说明，本文中描述的mysql提示符为user@hostname[(dbname)]，不同的帐户，不同的主机登录会显示不同。 --其次，不同的提示符下所代表的用户身份及权限。 --查看当前的连接用户 root@localhost[(none)]> select current_user(); +----------------+ | current_user() | +----------------+ | root@localhost | +----------------+ --查看当前帐户的权限 root@localhost[(none)]> show grants; --该账户用于最高权限，带有WITH GRANT OPTION +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+ SUSE11b:~ # mysql -ufred -p Enter password: fred@localhost[(none)]> show grants; +------------------------------------------+ | Grants for fred@localhost | +------------------------------------------+ | GRANT USAGE ON *.* TO 'fred'@'localhost' | +------------------------------------------+ --下面使用root账户给fred赋予权限all privileges root@localhost[(none)]> grant all privileges on *.* to 'fred'@'localhost'; Query OK, 0 rows affected (0.01 sec) root@localhost[(none)]> flush privileges; Query OK, 0 rows affected (0.00 sec) fred@localhost[(none)]> show grants; +---------------------------------------------------+ | Grants for fred@localhost | +---------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'fred'@'localhost' | +---------------------------------------------------+ fred@localhost[(none)]> use tempdb fred@localhost[tempdb]> create table tb_isam(id int,value varchar(20)) engine=myisam; Query OK, 0 rows affected (0.10 sec) fred@localhost[tempdb]> insert into tb_isam values (1,'jack'),(2,'robin'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 fred@localhost[tempdb]> commit; --下面的授权收到了错误提示，不能授权 fred@localhost[tempdb]> grant select on tempdb.* to 'frank'@'%'; ERROR 1044 (42000): Access denied for user 'fred'@'localhost' to database 'tempdb' --下面从root session来给之前创建的frank授权 --授予frank在数据库tempdb上所有对象的select权限 root@localhost[(none)]> grant select on tempdb.* to 'frank'@'%'; Query OK, 0 rows affected (0.00 sec) --更新cache中的权限 root@localhost[(none)]> flush privileges; Query OK, 0 rows affected (0.00 sec) --从另外的主机使用frank账户登录 suse11a:~ # mysql -ufrank -p -h272.16.6.89 Enter password: --此时frank，此时已经可以访问了tempdb上的表tb_isam frank@172.16.6.89[(none)]> select * from tempdb.tb_isam; +------+-------+ | id | value | +------+-------+ | 1 | jack | | 2 | robin | +------+-------+ frank@172.16.6.89[(none)]> show grants; +------------------------------------------------------------------------------------------------------+ | Grants for frank@% | +------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'frank'@'%' IDENTIFIED BY PASSWORD '*63DAA25989C7E01EB96570FA4DBE154711BEB361' | | GRANT SELECT ON `tempdb`.* TO 'frank'@'%' --可以看到多出了select权限 | +------------------------------------------------------------------------------------------------------+ --下面是一个授予最大权限的例子，授予的同时会自动创建用户，由于我们没有设置密码，所以password列查询结果为空 root@localhost[(none)]> grant all privileges on *.* to 'jack'@'localhost'; Query OK, 0 rows affected (0.00 sec) --第一个*号代表任意数据库，第二个*号代表数据库上的任意对象 root@localhost[(none)]> select user,host,Password from mysql.user where user='jack'; +------+-----------+----------+ | user | host | Password | +------+-----------+----------+ | jack | localhost | | +------+-----------+----------+ --下面演示基于对象列级别的授权 --首先revoke之前的select权限 root@localhost[(none)]> revoke select on tempdb.* from 'frank'@'%'; Query OK, 0 rows affected (0.00 sec) fred@localhost[tempdb]> create table tb_user as select * from mysql.user; Query OK, 9 rows affected (0.15 sec) Records: 9 Duplicates: 0 Warnings: 0 fred@localhost[tempdb]> grant select(user,host),update(host) on tempdb.tb_user to 'frank'@'%'; ERROR 1142 (42000): GRANT command denied to user 'fred'@'localhost' for table 'tb_user' --授权失败 --下面使用root来授权 root@localhost[(none)]> grant select(user,host),update(host) on tempdb.tb_user to 'frank'@'%'; Query OK, 0 rows affected (0.00 sec) root@localhost[(none)]> flush privileges; Query OK, 0 rows affected (0.00 sec) --下面检查一下frank所拥有的权限 root@localhost[(none)]> show grants for 'frank'; +------------------------------------------------------------------------------------------------------+ | Grants for frank@% | +------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'frank'@'%' IDENTIFIED BY PASSWORD '*63DAA25989C7E01EB96570FA4DBE154711BEB361' | | GRANT SELECT (user, host), UPDATE (host) ON `tempdb`.`tb_user` TO 'frank'@'%' | +------------------------------------------------------------------------------------------------------+ --下面使用frank身份来验证所授予的权限 frank@172.16.6.89[(none)]> desc tempdb.tb_user; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | Host | char(60) | NO | | | | | User | char(16) | NO | | | | +-------+----------+------+-----+---------+-------+ frank@172.16.6.89[(none)]> select * from tempdb.tb_user; --访问时不支持通配符，必须指定列名 ERROR 1142 (42000): SELECT command denied to user 'frank'@'suse11a.site' for table 'tb_user' frank@172.16.6.89[(none)]> select host,user from tempdb.tb_user where user='frank'; +------+-------+ | host | user | +------+-------+ | % | frank | +------+-------+ --需要注意的是，如果你的对象创建在test相关数据库下，权限限制可能会失效。 --下面这个查询用于查看db的授权表 root@localhost[(none)]> select host,db,user from mysql.db; +------+---------+------+ | host | db | user | +------+---------+------+ | % | test | | | % | test_% | | +------+---------+------+ --根据前面的权限授予,列host可以被更新，而列user不行，如下面的2条SQL语句执行的结果 frank@172.16.6.89[(none)]> update tempdb.tb_user set host='localhost' where user='frank'; Query OK, 1 row affected (0.12 sec) Rows matched: 1 Changed: 1 Warnings: 0 frank@172.16.6.89[(none)]> update tempdb.tb_user set user='jason' where user='jack'; ERROR 1143 (42000): UPDATE command denied to user 'frank'@'suse11a.site' for column 'user' in table 'tb_user' --关于WITH GRANT OPTION root@localhost[(none)]> show grants; --注意root下有WITH GRANT OPTION +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+ root@localhost[(none)]> show grants for 'jack'@'localhost'; --注意jack下没有WITH GRANT OPTION +---------------------------------------------------+ --这就是前面为什么用户自身创建的对象而无法授权的问题 | Grants for jack@localhost | +---------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'jack'@'localhost' | +---------------------------------------------------+ （编辑：好传媒网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
