欧美一级特黄大片做受成人-亚洲成人一区二区电影-激情熟女一区二区三区-日韩专区欧美专区国产专区

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)主營(yíng)松陽網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營(yíng)網(wǎng)站建設(shè)方案,成都APP應(yīng)用開發(fā),松陽h5微信小程序定制開發(fā)搭建,松陽網(wǎng)站營(yí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)限管理
轉(zhuǎn)載來源：http://aaarwkj.com/article18/ggppdp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站導(dǎo)航、App開發(fā)、微信小程序、做網(wǎng)站、企業(yè)建站、網(wǎng)站策劃

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)

h5響應(yīng)式網(wǎng)站建設(shè)
在线国产丝袜自拍观看| 国产精品国产精品国产| 日本熟人妻中文字幕在线| 伊人亚洲一区二区三区| 91欧美精品在线视频| 国产中文精品字幕a区| 亚洲午夜一区二区三区精品| 欧美亚洲综合激情在线| 亚洲欧美日韩国产成人精品| 五月天丁香婷婷深爱| 天天日天天天干夜夜操| 亚洲欧洲国产视频一区二区| 哈昂~不要啊在线观看| 扒开女性毛茸茸的视频| 亚洲国产视频中文字幕| 国产日韩欧美一区二区三区四区| 国产精品综合av一区二区国产馆| 欧美欧美一区二区三区| 日本亚洲一区二区在线观看| 综合久久久精品国产亚洲av | 国产91在线视频播放| 一区二区三区精品人妻| 粉嫩极品美女国产精品| 日本av成人激情视频| 国产成人公开免费视频| 国产欧美日韩国产精品| 中文字幕人妻在线播放| 国产精品亚洲伦理在线| 日本高清精品视频在线| 亚洲精品久久麻豆蜜桃| 日本一区二区久久人妻高清| 亚洲视频一区二区精品| 在线视频天堂亚洲天堂| 亚洲国产欧美日韩久久| 国产毛片一区二区在线 | 国产大神91一区二区三区| 国产丝袜美腿一二三区| 精品人妻av区久久久| 国产精品亚洲精品日韩在线| 国产精品久久av高潮呻吟| 国产美女作爱视频网站|