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

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è)
国产亚洲精品第一综合| 午夜毛片免费在线播放| 日韩欧美国产亚洲在线| 久久精品欧美日韩视频| 亚洲综合精品久久久一区| 天天操夜夜操夜夜操精品| 亚洲一区二区三区精品福利| 日韩欧美在线观看一区二区| 亚洲日本一区二区一本一道| 中文人妻熟妇乱又伦精品| 黄色录像免费看中文字幕| 日本精品a秘在线观看| 国产女主播高清在线视频| 亚洲av优选在线观看精品| 日韩无砖区2021不卡| 黄色日韩欧美在线观看| 黄色录像黄色片黄色片| 丰满人妻被猛烈进入中| 内射极品美女在线观看| 国产精品三级玖玖玖电影| 色婷婷综合激情一区二区| 国产l精品国产亚洲区久久 | 亚洲中文字幕在线乱码| 久久91亚洲精品久久91| 亚洲精品国产精品乱码| 日本一区二区免费视频| 日本加勒比系列在线视频| 美味人妻手机在线观看| 欧美黄片在线播放视频| 亚洲av一本岛在线播放| 蜜桃av在线播放视频| 国产国产成人精品久久| 浮力草草日韩欧美三级| 欧美另类不卡在线观看| 国产高清av免费在线观看| 日本欧美三级高潮受不了| 熟女自拍偷拍视频播放| 成人18禁h黄在线看免费| 蜜臀午夜精品视频在线观看| 欧美黑人少妇高潮喷水| 欧美三级亚洲三级日韩三级|