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'

成功了//