真实的国产乱ⅩXXX66竹夫人,五月香六月婷婷激情综合,亚洲日本VA一区二区三区,亚洲精品一区二区三区麻豆

成都創(chuàng)新互聯(lián)網(wǎng)站制作重慶分公司

mysql權(quán)限管理

2019/5/20 星期一 //這是MySQL的權(quán)限問題 具體的詳情見 第三章 《mysql用戶及賦予用戶權(quán)限grant》
現(xiàn)在有一個(gè)需求，就是指定一個(gè)用戶對(duì)一個(gè)數(shù)據(jù)庫中的所有表只有只讀權(quán)限
mysql用戶及賦予用戶權(quán)限grant
我們?cè)谖覀兊膁ev環(huán)境的 192.168.0.36 上進(jìn)行試驗(yàn)

創(chuàng)新互聯(lián)主營松陽網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營網(wǎng)站建設(shè)方案,成都APP應(yīng)用開發(fā),松陽h5微信小程序定制開發(fā)搭建,松陽網(wǎng)站營銷推廣歡迎松陽等地區(qū)企業(yè)咨詢

@(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)
首先創(chuàng)建一個(gè) 試驗(yàn)的表
create database university; //create database university character set utf8mb4;
use university;
創(chuàng)建一個(gè)學(xué)生表
create table student(
Sno char(9) PRIMARY KEY,
Sname char(20) unique,
Ssex char(2),
Sage int,
Sdept char(20)
);

插入試驗(yàn)數(shù)據(jù)
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)

接下來，我們要?jiǎng)?chuàng)建一個(gè)用戶majihui 只對(duì)university 庫下的所有表有權(quán)限。
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 指定庫的指點(diǎn)表
    | tbl_name 指定一個(gè)表
    | db_name.routine_name指定一個(gè)庫的存儲(chǔ)過程

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 每小時(shí)查詢幾次
| MAX_UPDATES_PER_HOUR count 每小時(shí)更新幾次
| MAX_CONNECTIONS_PER_HOUR count 每小時(shí)鏈接幾次
| MAX_USER_CONNECTIONS count 每小時(shí)使用幾次

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';  創(chuàng)建一個(gè)用戶名為oldboy密碼為oldboy123的用戶
Query OK, 0 rows affected (0.00 sec)
//此部分在dba第三 章有詳細(xì)解釋，請(qǐng)轉(zhuǎn)至第三章 創(chuàng)建mysql用戶及賦予用戶權(quán)限 *****

我們具體的操作如下：
首先，我們查看用戶

@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 這臺(tái)服務(wù)器上用root用戶去連0.36上的mysql 看能否鏈接創(chuàng)建數(shù)據(jù)
[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)
我們可以查看
我們接下來試著插入一個(gè)數(shù)據(jù)

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)

接下來，我們的實(shí)際操作
接下來，我們要?jiǎng)?chuàng)建一個(gè)用戶majihui 只對(duì)university 庫下的所有表有權(quán)限。
方法1：
create user majihui@'localhost' identified by 'majihui123'; 創(chuàng)建一個(gè)用戶名為majihui密碼為majihui123的用戶
grant all on university.* to majihui@'localhost'; （把majihui庫中的所有表的權(quán)限 給majihui用戶）

方法2：一條命令搞定
mysql> grant all on oldboy. to oldboy@'localhost' identified by 'oldboy123';
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
提示：相當(dāng)于
useradd oldboy && echo "oldboy123"|password -stdin oldboy
列表說明如下
grant all on oldboy. to oldboy@'localhost' identified by 'oldboy123';
授權(quán)命令 對(duì)于權(quán)限 目標(biāo)：庫和表 用戶名和主機(jī) 用戶密碼

//我們的實(shí)際操作如下： //我們之前用的是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@'%';

再執(zhí)行這個(gè)就可以鏈接了
[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這個(gè)用戶對(duì)university.*下的所有庫有增刪改查的權(quán)限

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

刪除majihui這個(gè)用戶
@(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)

創(chuàng)建vargo這個(gè)用戶，單獨(dú)的對(duì)university.下所有表 只有可讀的權(quán)限。也就是只有select的權(quán)限
具體操作如下
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'

成功了//


網(wǎng)站名稱：mysql權(quán)限管理
當(dāng)前網(wǎng)址：http://weahome.cn/article/ggppdp.html

其他資訊

在線咨詢

微信咨詢

電話咨詢

028-86922220（工作日）

18980820575（7×24）

提交需求

返回頂部