mysql权限管理

2019/5/20 星期一 //这是mysql的权限问题 具体的详情见 第三章 《mysql用户及赋予用户权限grant》
现在有一个需求，就是指定一个用户对一个数据库中的所有表只有只读权限
mysql用户及赋予用户权限grant
我们在我们的dev环境的 192.168.0.36 上进行试验

@(none)> select user,host from mysql.user; 
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| root          | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)
首先创建一个 试验的表
create database university; //create database university character set utf8mb4;
use university;
创建一个学生表
create table student(
Sno char(9) PRIMARY KEY,
Sname char(20) unique,
Ssex char(2),
Sage int,
Sdept char(20)
);

插入试验数据
insert into student values(200215121,'李勇','男',20,'CS');
insert into student values(100215122,'刘晨','女',19,'CS');
insert into student values(100215123,'王敏','女',18,'MA');
insert into student values(100215124,'张立','男',19,'IS');

@university> use university;
Database changed
@university> show tables;
+----------------------+
| Tables_in_university |
+----------------------+
| student              |
+----------------------+
1 row in set (0.00 sec)

@university> select * from student;
+-----------+--------+------+------+-------+
| Sno       | Sname  | Ssex | Sage | Sdept |
+-----------+--------+------+------+-------+
| 100215122 | 刘晨   | 女   |   19 | CS    |
| 100215123 | 王敏   | 女   |   18 | MA    |
| 100215124 | 张立   | 男   |   19 | IS    |
| 200215121 | 李勇   | 男   |   20 | CS    |
+-----------+--------+------+------+-------+
4 rows in set (0.00 sec)

接下来，我们要创建一个用户majihui 只对university 库下的所有表有权限。
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 ...]

object_type:
TABLE
| FUNCTION
| PROCEDURE

priv_level:

• 所有
  | . 所有库所有表
  | db_name.* 指定库的所有表
  | db_name.tbl_name 指定库的指点表
  | tbl_name 指定一个表
  | db_name.routine_name指定一个库的存储过程

user_specification:
user [IDENTIFIED BY [PASSWORD] 'password']

ssl_option:
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'

with_option:
GRANT OPTION
| MAX_QUERIES_PER_HOUR count 每小时查询几次
| MAX_UPDATES_PER_HOUR count 每小时更新几次
| MAX_CONNECTIONS_PER_HOUR count 每小时链接几次
| MAX_USER_CONNECTIONS count 每小时使用几次

The GRANT statement grants privileges to MySQL user accounts. GRANT
also serves to specify other account characteristics such as use of
secure connections and limits on access to server resources. To use
GRANT, you must have the GRANT OPTION privilege, and you must have the
privileges that you are granting.

Normally, a database administrator first uses CREATE USER to create an
account, then GRANT to define its privileges and characteristics. For
example:

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1. TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
GRANT USAGE ON .* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

However, if an account named in a GRANT statement does not already
exist, GRANT may create it under the conditions described later in the
discussion of the NO_AUTO_CREATE_USER SQL mode.

The REVOKE statement is related to GRANT and enables administrators to
remove account privileges. See [HELP REVOKE].

When successfully executed from the mysql program, GRANT responds with
Query OK, 0 rows affected. To determine what privileges result from the
operation, use SHOW GRANTS. See [HELP SHOW GRANTS].

URL: http://dev.mysql.com/doc/refman/5.1/en/grant.html

首先方法1 
方法1：
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
mysql> create user oldboy@'localhost' identified by 'oldboy123';  创建一个用户名为oldboy密码为oldboy123的用户
Query OK, 0 rows affected (0.00 sec)
//此部分在dba第三 章有详细解释，请转至第三章 创建mysql用户及赋予用户权限 *****

我们具体的操作如下：
首先，我们查看用户

@university> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| root | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

没有我们想要的majihui用户

我们接下来在192.168.83.181 这台服务器上用root用户去连0.36上的mysql 看能否链接创建数据
[root@hadoop01-181 ~]# mysql -uroot -pxxxxx -h292.168.0.36 -P3306
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| freeswitch |
| gpush |
| ivargo_pay |
| mysql |
| openfire |
| performance_schema |
| sys |

23 rows in set (0.01 sec)
我们可以查看
我们接下来试着插入一个数据

insert into student values(20090001,'马吉辉','男',26,'CS');
mysql> insert into student values(20090001,'马吉辉','男',26,'CS');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+-----------+-----------+------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept |
+-----------+-----------+------+------+-------+
| 100215122 | 刘晨      | 女   |   19 | CS    |
| 100215123 | 王敏      | 女   |   18 | MA    |
| 100215124 | 张立      | 男   |   19 | IS    |
| 200215121 | 李勇      | 男   |   20 | CS    |
| 20090001  | 马吉辉    | 男   |   26 | CS    |
+-----------+-----------+------+------+-------+
5 rows in set (0.00 sec)

接下来，我们的实际操作
接下来，我们要创建一个用户majihui 只对university 库下的所有表有权限。
方法1：
create user majihui@'localhost' identified by 'majihui123'; 创建一个用户名为majihui密码为majihui123的用户
grant all on university.* to majihui@'localhost'; （把majihui库中的所有表的权限 给majihui用户）

方法2：一条命令搞定
mysql> grant all on oldboy. to oldboy@'localhost' identified by 'oldboy123';
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
提示：相当于
useradd oldboy && echo "oldboy123"|password -stdin oldboy
列表说明如下
grant all on oldboy. to oldboy@'localhost' identified by 'oldboy123';
授权命令 对于权限 目标：库和表 用户名和主机 用户密码

//我们的实际操作如下： //我们之前用的是majihui@'localhost' 要改成 majihui@'%'
@(none)> create user majihui@'%' identified by 'majihui123';
Query OK, 0 rows affected (0.01 sec)
@(none)> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| root | % |
| majihui | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)
@(none)> grant all on university. to majihui@'%';
Query OK, 0 rows affected, 1 warning (0.01 sec)
@(none)> show grants for majihui@'%';
+---------------------------------------------------------+
| Grants for majihui@% |
+---------------------------------------------------------+
| GRANT USAGE ON . TO 'majihui'@'%' |
| GRANT ALL PRIVILEGES ON university. TO 'majihui'@'%' |
+---------------------------------------------------------+
2 rows in set (0.00 sec)

[root@hadoop01-181 ~]# mysql -umajihui -pmajihui123 -h292.168.0.36 -P3306
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'majihui'@'192.168.83.181' (using password: YES)
[root@hadoop01-181 ~]# mysql -umajihui -pmajihui123 -hlocalhost -P3306
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'majihui'@'localhost' (using password: YES)

我们要把localhost改成 %
UPDATE mysql.user SET host='%' WHERE user='majihui';
@(none)> UPDATE mysql.user SET host='%' WHERE user='majihui';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

@(none)> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| majihui | % |
| root | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)

@(none)> flush privileges;
Query OK, 0 rows affected (0.00 sec)

grant all on university.* to majihui@'%';

再执行这个就可以链接了
[root@hadoop01-181 ~]# mysql -umajihui -pmajihui123 -h292.168.0.36 -P3306
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| university |
+--------------------+
2 rows in set (0.00 sec)

mysql> delete from student where Sname='马吉辉';
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+-----------+--------+------+------+-------+
| Sno | Sname | Ssex | Sage | Sdept |
+-----------+--------+------+------+-------+
| 100215122 | 刘晨 | 女 | 19 | CS |
| 100215123 | 王敏 | 女 | 18 | MA |
| 100215124 | 张立 | 男 | 19 | IS |
| 200215121 | 李勇 | 男 | 20 | CS |
+-----------+--------+------+------+-------+
4 rows in set (0.00 sec)

//成功了，majihui这个用户对university.*下的所有库有增删改查的权限

接下来收回权限
REVOKE INSERT ON university. FROM 'majihui'@'%'; 回收权限
@(none)> REVOKE INSERT ON university. FROM 'majihui'@'%';
Query OK, 0 rows affected (0.00 sec)

删除majihui这个用户
@(none)> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| majihui | % |
| root | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)

@(none)> delete from mysql.user where user='majihui';
Query OK, 1 row affected (0.01 sec)

@(none)> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| root | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

创建vargo这个用户，单独的对university.下所有表 只有可读的权限。也就是只有select的权限
具体操作如下
create user vargo@'%' identified by 'vargo123';
grant select on university. to vargo@'%';
@(none)> grant select on university.* to vargo@'%';
Query OK, 0 rows affected (0.01 sec)

@(none)> show grants for vargo@'%'
-> ;
+-----------------------------------------------+
| Grants for vargo@% |
+-----------------------------------------------+
| GRANT USAGE ON . TO 'vargo'@'%' |
| GRANT SELECT ON university.* TO 'vargo'@'%' |
+-----------------------------------------------+
2 rows in set (0.00 sec)

然后我们登录
[root@hadoop01-181 ~]# mysql -uvargo -pvargo123 -h292.168.0.36 -P3306
mysql> select * from student;
+-----------+--------+------+------+-------+
| Sno | Sname | Ssex | Sage | Sdept |
+-----------+--------+------+------+-------+
| 100215122 | 刘晨 | 女 | 19 | CS |
| 100215123 | 王敏 | 女 | 18 | MA |
| 100215124 | 张立 | 男 | 19 | IS |
+-----------+--------+------+------+-------+
3 rows in set (0.00 sec)

mysql> delete from student where Sname='张立';
ERROR 1142 (42000): DELETE command denied to user 'vargo'@'192.168.83.181' for table 'student'
mysql> insert into student values(200215121,'李勇','男',20,'CS');
ERROR 1142 (42000): INSERT command denied to user 'vargo'@'192.168.83.181' for table 'student'

成功了//