mysql给用户授权最大_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 (idintunsigned 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都是读权限，一般可以赋予，其他权限则都为写权限需要慎重考虑授权