MySQL管理之用户权限

MySQL用户管理

mysql授权级别

·全局级别

·数据库级别 （能否删除创建库

·表级别

·字段级别

·储存过程、存储函数级别

需要了解的三种权限类型

·管理类

·数据库访问类

·字段类

如果某用户访问数据库服务器的时候频率非常大，每秒N个查询并发，这样会消耗太多服务器资源，也有可能恶意访问数据库服务器，所以为了避免类似情况出现，一般可以在授权的同时限定其操作的次数，这些在执行用户授权时则可以定义的

用户的创建

创建用户命令

mysql> help create user;
Name: ‘CREATE USER‘
Description:
Syntax:
CREATE USER user_specification                          使用create user 直接跟用户名以及可选项

   [, user_specification] ...

user_specification:
   user
   [
       IDENTIFIED BY [PASSWORD] ‘password‘         #创建用户的同时，直接加identified by 即可创建用户密码

     | IDENTIFIED WITH auth_plugin [AS‘auth_string‘]
   ]

创建用户过程中，IDENTIFIED BY可以省略

授权用户只有select权限，并查看其用户能否创建数据库，一般以这种方式创建完后的用户的权限只有use权限，和简单的show 等权限

比如

mysql> create user test1@‘%‘ identified by ‘123456‘;
Query OK, 0 rows affected (0.00 sec)

凡是使用create user方式创建用户则可以默认触发flush privileges

或使用insert into mysql.user

但是不会主动刷新权限，必须手动执行flush privileges;

使用创建好的用户进入数据库

[root@test2 data]# mysql -utest1 -p123456

mysql> show databases;          #show命令不需要单独授权，但是其他数据库都不能看到

+--------------------+
| Database       |
+--------------------+
| information_schema |
| test         |
+--------------------+
2 rows in set (0.00 sec)

用户授权

mysql> help grant;
Name: ‘GRANT‘
Description:
Syntax:
GRANT
   priv_type [(column_list)]                           #指明权限类型，比如select insert 等
     [, 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]

有可能表跟函数重名的，如果重名我们的授权只授权于数据库的表，如果授权的是存储函数，一定加FUNCTION并指定函数名

object_type:

   TABLE
 | FUNCTION
 | PROCEDURE

创建用户并指定函数

grant execute on function db.abc to username@‘host‘;

既有表有bac 也有存储函数名为abc 那么如果想定义函数的话 则需要加function参数：

grant execute on function function db.abc to username@‘host‘;

继续往下看

user_specification:
   user
   [
       IDENTIFIED BY [PASSWORD] ‘password‘            #指定用户密码，如果授权密码时，用户不存在，则自动创建用户
     | IDENTIFIED WITH auth_plugin [AS ‘auth_string‘]
   ]

with_option:              

   GRANT OPTION                           #权限可以转授予其他用户，不到万不得已不要使用
 | MAX_QUERIES_PER_HOUR count             #每小时最多允许发出的查询次数，如果不想限定将其值改为0即可
 | MAX_UPDATES_PER_HOUR count             #每个小时最多允许执行的更新的次数
 | MAX_CONNECTIONS_PER_HOUR count         #每小时允许建立几次连接
 | MAX_USER_CONNECTIONS count             #同一个用户账号允许并发连接个数

授权方式

格式

GRANT priv(指定权限),priv2..ON[{TABLE|function|PROCEDURE}] {db.tb| routine} to user@host,可定义多个  with option identfied by ‘password‘

授权test1对test1数据库有创建的权限

mysql> grant create on test1.* to ‘test1‘@‘%‘;

Query OK, 0 rows affected (0.00 sec)

切换至test1登录数据库

[root@test2 data]# mysql -utest1 -p123456;

mysql> create database test1;
Query OK, 1 row affected (0.01 sec)

测试创建表

mysql>create table testtb (id intunsigned auto_increment not null,name char(20),primary key(id));
Query OK, 0 rows affected (0.06 sec)

创建没问题，接下来测试插入数据

mysql>insert into testtb(name) values(‘to‘);
ERROR 1142 (42000): INSERT command denied to user ‘test1‘@‘localhost‘ for table‘testtb‘

可显是不行的 因为只给了创建的权限但并没有赋予写入表的权限

于是再次授权于其用户insert权限

mysql>grant insert on test1.* to ‘test1‘@‘%‘;
Query OK, 0 rows affected (0.00 sec)

#这里直接写insert表示直接插入表即可

但是插入数据缺报错了

mysql>insert into testtb(name) values(‘to‘);
ERROR 1142 (42000): INSERT command denied to user ‘test1‘@‘localhost‘ for table‘testtb‘

重启会话再次执行

插入成功，说明必要时必须要重新建立会话才可以

[root@test2data]# mysql -utest1 -p123456;

mysql>insert into testtb(name) values(‘to‘);
Query OK, 1 row affected (0.01 sec)

查看用户权限表

格式： SHOW GRANTS FORUSER;

mysql> SHOW GRANTS FOR ‘test1‘@‘%‘ ;
+------------------------------------------------------------------------------------------------------+
| Grants fortest1@%                                                                                    |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO ‘test1‘@‘%‘ IDENTIFIED BY PASSWORD‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘  |
| GRANT CREATE ON `wpdb`.* TO‘test1‘@‘%‘                                                               |
| GRANT CREATE ON `abc`.* TO‘test1‘@‘%‘                                                                |
| GRANT INSERT, CREATE ON `test1`.* TO‘test1‘@‘%‘                                                      |
| GRANT CREATE ON `mysql`.`wpdb` TO‘test1‘@‘%‘                                                         |    
+------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

赋予其select权限

mysql>grant select on test1.* to ‘test1‘@‘%‘;
Query OK, 0 rows affected (0.00 sec)    

mysql> show grants for ‘test1‘@‘%‘;
+------------------------------------------------------------------------------------------------------+
| Grants fortest1@%                                                                                    |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO ‘test1‘@‘%‘ IDENTIFIED BY PASSWORD‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘  |
| GRANT CREATE ON `wpdb`.* TO‘test1‘@‘%‘                                                               |
| GRANT CREATE ON `abc`.* TO‘test1‘@‘%‘                                                                |
| GRANT SELECT, INSERT, CREATE ON `test1`.* TO‘test1‘@‘%‘                                              |
| GRANT CREATE ON `mysql`.`wpdb` TO ‘test1‘@‘%‘                                                        |
+------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

授权具有执行alter权限

我们要在testtb表中加入一个字段，如下所示

mysql>use test1;
Database changed
mysql> select * from testtb;
+----+------+
| id | name |
+----+------+
|  1 | to   |
+----+------+
1 row in set (0.00 sec)

授权其alter权限

mysql>grant alter on test1.* to ‘test1‘@‘%‘;

重启会话

mysql>alter table testtb add age tinyint unsigned;
Query OK, 1 row affected (0.07 sec)
Records: 1  Duplicates: 0  Warnings: 0

如下所示可以看到我们新加的字段了

mysql>select * from testtb;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | to  | NULL  |
+----+------+------+
1 row in set (0.00 sec)

只授权某表中的某个字段的权限

mysql>grant update(age) on test1.testtb to ‘test1‘@‘%‘;
Query OK, 0 rows affected (0.01 sec)

更新age某字段

mysql>update testtb set age=30 where id=1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from testtb;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | to |  30    |
+----+------+------+
1 row in set (0.00 sec)

更改name字段出现了错误

mysql>update testtb set name=ot where id=1;
ERROR 1143 (42000): UPDATE command denied to user ‘test1‘@‘localhost‘ forcolumn ‘name‘ in table ‘testtb‘

再次showgrants for 查看其用户权限

在字段上是单独写的，其他权限全部合并起来了

#只要在表级别存储过程级别或存储函数、库级别 全都合并在了一起

在字段或列级别而都以字段级别和列级别合并在一起

mysql>show grants for test1@‘%‘;
+------------------------------------------------------------------------------------------------------+
| Grants fortest1@%                                                                                    |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO ‘test1‘@‘%‘ IDENTIFIED BY PASSWORD‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘  |
| GRANT CREATE ON `wpdb`.* TO‘test1‘@‘%‘                                                               |
| GRANT CREATE ON `abc`.* TO‘test1‘@‘%‘                                                                |
| GRANT SELECT, INSERT, CREATE, ALTER ON `test1`.* TO‘test1‘@‘%‘                                       |
| GRANT UPDATE (age) ON `test1`.`testtb` TO‘test1‘@‘%‘                                                 |
| GRANT CREATE ON `mysql`.`wpdb` TO‘test1‘@‘%‘                                                         |  
+------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

删除用户

格式 ：

mysql>drop user@host;

用户重命名

renameuser

mysql>help rename user;
Name: ‘RENAME USER‘
Description:
Syntax:
RENAME USER old_user TO new_user
   [, old_user TO new_user] ...

RENAMEUSER ‘jeffrey‘@‘localhost‘ TO ‘jeff‘@‘127.0.0.1‘;

取消授权

revokefrom

mysql>help revoke;
Name: ‘REVOKE‘
Description:
Syntax:
REVOKE
   priv_type [(column_list)]              #想收回那个权限
     [, priv_type [(column_list)]] ...    #如果是字段上的权限也可以明确指定并收回

   ON [object_type] priv_level
   FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION
   FROM user [, user] ...

REVOKE PROXY ON user
   FROM user [, user] ...

取消其用户的select权限

mysql>show grants for test1@‘%‘;
+------------------------------------------------------------------------------------------------------+
| Grants fortest1@%                                                                                     |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO ‘test1‘@‘%‘ IDENTIFIED BY PASSWORD‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘ |
| GRANT CREATE ON `wpdb`.* TO‘test1‘@‘%‘                                                              |
| GRANT CREATE ON `abc`.* TO‘test1‘@‘%‘                                                               |
| GRANT SELECT, INSERT, CREATE, ALTER ON `test1`.* TO‘test1‘@‘%‘                                      |
| GRANT UPDATE (age) ON `test1`.`testtb` TO ‘test1‘@‘%‘                                               |
| GRANT CREATE ON `mysql`.`wpdb` TO‘test1‘@‘%‘                                                        |
+------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

mysql>revoke select on test1.* from test1@‘%‘;
Query OK, 0 rows affected (0.00 sec)

#select已经没有了

mysql> showgrants for test1@‘%‘;
+------------------------------------------------------------------------------------------------------+
| Grants for test1@%                                                                                  |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO ‘test1‘@‘%‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘ |
| GRANT CREATE ON `wpdb`.* TO‘test1‘@‘%‘                                                               |
| GRANT CREATE ON `abc`.* TO ‘test1‘@‘%‘                                                               |
| GRANT INSERT, CREATE, ALTER ON `test1`.* TO ‘test1‘@‘%‘                                              |
| GRANT UPDATE (age) ON `test1`.`testtb` TO‘test1‘@‘%‘                                                 |
| GRANT CREATE ON `mysql`.`wpdb` TO‘test1‘@‘%‘                                                         |
+------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

一般来讲select都是读权限，一般可以赋予，其他权限则都为写权限需要慎重考虑授权

本文出自 “心情依旧” 博客，请务必保留此出处http://yijiu.blog.51cto.com/433846/1393230

MySQL管理之用户权限,古老的榕树,5-wow.com