一，用戶的創(chuàng)建

1 use mysql;

image

2 mysql的用戶全部存放于user表中撩鹿；

查看這張表目前只有2個(gè)用戶谦炬，其中一個(gè)就是root

[圖片上傳失敗...(image-f60cc1-1611546628609)]

image

3 新建一個(gè)用戶

方法1：

CREATE USER 'a'@'%' IDENTIFIED BY '1';

新建一個(gè)用戶名為a的用戶，密碼為1三痰，%表示可以遠(yuǎn)程連接數(shù)據(jù)庫

具體執(zhí)行如下

image

如果出現(xiàn)下面的ok吧寺，就說明新家用戶成功了窜管。

方法2：

GRANT USAGE ON . TO 'b'@'%' IDENTIFIED BY '2';

新建一個(gè)b用戶散劫，密碼為2

最后查看用戶

image

二 給用戶分配權(quán)限

分配之前，先看看用戶的權(quán)限

SHOW GRANTS FOR 'a'@'%';

是沒有任何權(quán)限的幕帆。

1 GRANT SELECT ON test.tdb_goods TO 'a'@'%';

給用戶a分配一個(gè)操作書庫局test中表tdb_goods的select權(quán)限

2 GRANT ALL ON test.tdb_goods TO 'a'@'%';

給用戶a分配一個(gè)操作書庫局test中表tdb_goods的所有權(quán)限

或者

GRANT ALL PRIVILEGES ON test.tdb_goods TO 'a'@'%' IDENTIFIED BY '1';

總結(jié)获搏，分配權(quán)限具體的格式為： grant 權(quán)限 on 數(shù)據(jù)庫.* to 用戶名@登錄主機(jī) identified by "密碼";

三 撤銷權(quán)限

REVOKE ALL ON test.tdb_goods FROM 'a'@'%';

四 刪除用戶

DROP USER 'a'@'%';

五 修改用戶的登錄密碼

UPDATE user SET authentication_string=password('3') WHERE user='b' AND Host='%';

FLUSH PRIVILEGES;

六 其他一些常用命令

1 列出所有的數(shù)據(jù)庫

show databases;

2 切換數(shù)據(jù)庫

ues 數(shù)據(jù)庫的名字;

3 列出所有的表

show tables;

4 顯示數(shù)據(jù)表結(jié)構(gòu)

desc 表名;

5 刪除數(shù)據(jù)庫

drop databases 數(shù)據(jù)庫名

6 刪除數(shù)據(jù)表

drop table 數(shù)據(jù)表

7 查看mysql版本

select version();

8 查看當(dāng)前的時(shí)間

select now();

9 查看當(dāng)前登錄的用戶名

select user();

10 查看數(shù)據(jù)庫的編碼方式

show create database 數(shù)據(jù)庫名;

11 查看錯(cuò)誤信息

show warnings;

12 創(chuàng)建一個(gè)gbk格式的數(shù)據(jù)庫

create database if not exists 數(shù)據(jù)庫名 character set gbk;

13 修改數(shù)據(jù)庫的編碼方式

alter database 數(shù)據(jù)庫名 character set=utf8;

14 直接查看某個(gè)庫有多少張表

show tables from 數(shù)據(jù)庫名；

15 直接查看當(dāng)前處于哪個(gè)數(shù)據(jù)庫

select database();

16 查看表的結(jié)構(gòu)

show columns from 表名；

17 添加一列到某個(gè)字段的后面

alter table 表名 add 將要添加的字段 varchar(10) after 某個(gè)字段

18 添加一列到第一列

alter table 表名 add 添加的字段名 varchar(10) first

19 刪除多列

alter table 表名 drop xxx,drop xxx;

20 增加一個(gè)主鍵列

alter table表名 add constraint 列名 primary key (id)

21 增加唯一約束列

alter table 表名 add unique (列名)

22 設(shè)置客戶端的編碼方式

set names gbk;

23 刪除主鍵約束

alter table 表名 drop primary key;

24 刪除唯一約束常熙，不刪除字段

alter table 表名 drop index 列名

25 查看外鍵約束索引

show indexes from 表名\G

七 用戶權(quán)限說明

image.png

一．權(quán)限表

mysql數(shù)據(jù)庫中的3個(gè)權(quán)限表：user 纬乍、db、 host

權(quán)限表的存取過程是：

1)先從user表中的host裸卫、 user仿贬、 password這3個(gè)字段中判斷連接的IP、用戶名墓贿、密碼是否存在表中茧泪，存在則通過身份驗(yàn)證；

2)通過權(quán)限驗(yàn)證聋袋，進(jìn)行權(quán)限分配時(shí)队伟，按照useràdbàtables_privàcolumns_priv的順序進(jìn)行分配。即先檢查全局權(quán)限表user幽勒，如果user中對(duì)應(yīng)的權(quán)限為Y嗜侮，則此用戶對(duì)所有數(shù)據(jù)庫的權(quán)限都為Y，將不再檢查db, tables_priv,columns_priv啥容；如果為N锈颗，則到db表中檢查此用戶對(duì)應(yīng)的具體數(shù)據(jù)庫，并得到db中為Y的權(quán)限咪惠；如果db中為N宜猜，則檢查tables_priv中此數(shù)據(jù)庫對(duì)應(yīng)的具體表，取得表中的權(quán)限Y硝逢，以此類推姨拥。

二．MySQL各種權(quán)限（共27個(gè)）

（以下操作都是以root身份登陸進(jìn)行g(shù)rant授權(quán)，以p1@localhost身份登陸執(zhí)行各種命令渠鸽。）

1. usage

連接（登陸）權(quán)限叫乌，建立一個(gè)用戶，就會(huì)自動(dòng)授予其usage權(quán)限（默認(rèn)授予）徽缚。

mysql> grant usage on . to ‘p1′@’localhost’ identified by ‘123′;

該權(quán)限只能用于數(shù)據(jù)庫登陸憨奸，不能執(zhí)行任何操作；且usage權(quán)限不能被回收凿试，也即REVOKE用戶并不能刪除用戶排宰。

2. select

必須有select的權(quán)限，才可以使用select table

mysql> grant select on pyt.* to ‘p1′@’localhost’;

mysql> select * from shop;

3. create

必須有create的權(quán)限那婉，才可以使用create table

mysql> grant create on pyt.* to ‘p1′@’localhost’;

4. create routine

必須具有create routine的權(quán)限板甘，才可以使用{create |alter|drop} {procedure|function}

mysql> grant create routine on pyt.* to ‘p1′@’localhost’;

當(dāng)授予create routine時(shí)，自動(dòng)授予EXECUTE, ALTER ROUTINE權(quán)限給它的創(chuàng)建者：

mysql> show grants for ‘p1′@’localhost’;

+—————————————————————————+

Grants for p1@localhost

+————————————————————————–+

| GRANT USAGE ON . TO ‘p1′@’localhost’ IDENTIFIED BY PASSWORD ‘*23AE809DDACAF96AF0FD78ED04B6A265E05AA257′ |

| GRANT SELECT, CREATE, CREATE ROUTINE ON pyt.* TO ‘p1′@’localhost’|

| GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE pyt.pro_shop1 TO ‘p1′@’localhost’ |

+————————————————————————————-+

5. create temporary tables(注意這里是tables详炬，不是table)

必須有create temporary tables的權(quán)限盐类，才可以使用create temporary tables.

mysql> grant create temporary tables on pyt.* to ‘p1′@’localhost’;

[mysql@mydev ~]$ mysql -h localhost -u p1 -p pyt

mysql> create temporary table tt1(id int);

6. create view

必須有create view的權(quán)限，才可以使用create view

mysql> grant create view on pyt.* to ‘p1′@’localhost’;

mysql> create view v_shop as select price from shop;

7. create user

要使用CREATE USER，必須擁有mysql數(shù)據(jù)庫的全局CREATE USER權(quán)限在跳，或擁有INSERT權(quán)限枪萄。

mysql> grant create user on . to ‘p1′@’localhost’;

或：mysql> grant insert on . to p1@localhost;

8. insert

必須有insert的權(quán)限，才可以使用insert into ….. values….

9. alter

必須有alter的權(quán)限猫妙，才可以使用alter table

alter table shop modify dealer char(15);

10. alter routine

必須具有alter routine的權(quán)限瓷翻，才可以使用{alter |drop} {procedure|function}

mysql>grant alter routine on pyt.* to ‘p1′@’ localhost ‘;

mysql> drop procedure pro_shop;

Query OK, 0 rows affected (0.00 sec)

mysql> revoke alter routine on pyt.* from ‘p1′@’localhost’;

[mysql@mydev ~]$ mysql -h localhost -u p1 -p pyt

mysql> drop procedure pro_shop;

ERROR 1370 (42000): alter routine command denied to user ‘p1′@’localhost’ for routine ‘pyt.pro_shop’

11. update

必須有update的權(quán)限，才可以使用update table

mysql> update shop set price=3.5 where article=0001 and dealer=’A';

12. delete

必須有delete的權(quán)限割坠，才可以使用delete from ….where….(刪除表中的記錄)

13. drop

必須有drop的權(quán)限逻悠，才可以使用drop database db_name; drop table tab_name;

drop view vi_name; drop index in_name;

14. show database

通過show database只能看到你擁有的某些權(quán)限的數(shù)據(jù)庫，除非你擁有全局SHOW DATABASES權(quán)限韭脊。

對(duì)于p1@localhost用戶來說童谒，沒有對(duì)mysql數(shù)據(jù)庫的權(quán)限，所以以此身份登陸查詢時(shí)沪羔，無法看到mysql數(shù)據(jù)庫：

mysql> show databases;

+——————–+

| Database |

+——————–+

| information_schema|

| pyt |

| test |

+——————–+

15. show view

必須擁有show view權(quán)限饥伊，才能執(zhí)行show create view。

mysql> grant show view on pyt.* to p1@localhost;

mysql> show create view v_shop;

16. index

必須擁有index權(quán)限蔫饰，才能執(zhí)行[create |drop] index

mysql> grant index on pyt.* to p1@localhost;

mysql> create index ix_shop on shop(article);

mysql> drop index ix_shop on shop;

17. excute

執(zhí)行存在的Functions,Procedures

mysql> call pro_shop1(0001,@a)琅豆；

+———+

| article |

+———+

| 0001 |

| 0001 |

+———+

mysql> select @a;

+——+

| @a |

+——+

| 2 |

+——+

18. lock tables

必須擁有l(wèi)ock tables權(quán)限，才可以使用lock tables

mysql> grant lock tables on pyt.* to p1@localhost;

mysql> lock tables a1 read;

mysql> unlock tables;

19. references

有了REFERENCES權(quán)限篓吁，用戶就可以將其它表的一個(gè)字段作為某一個(gè)表的外鍵約束茫因。

20. reload

必須擁有reload權(quán)限，才可以執(zhí)行flush [tables | logs | privileges]

mysql> grant reload on pyt.* to p1@localhost;

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

mysql> grant reload on . to ‘p1′@’localhost’;

Query OK, 0 rows affected (0.00 sec)

mysql> flush tables;

21. replication client

擁有此權(quán)限可以查詢master server杖剪、slave server狀態(tài)冻押。

mysql> show master status;

ERROR 1227 (42000): Access denied; you need the SUPER,REPLICATION CLIENT privilege for this operation

mysql> grant Replication client on . to p1@localhost;

或：mysql> grant super on . to p1@localhost;

mysql> show master status;

+——————+———-+————–+——————+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+——————+———-+————–+——————+

| mysql-bin.000006 | 2111 | | |

+——————+———-+————–+——————+

mysql> show slave status;

22. replication slave

擁有此權(quán)限可以查看從服務(wù)器，從主服務(wù)器讀取二進(jìn)制日志盛嘿。

mysql> show slave hosts;

ERROR 1227 (42000): Access denied; you need the REPLICATION SLAVE privilege for this operation

mysql> show binlog events;

ERROR 1227 (42000): Access denied; you need the REPLICATION SLAVE privilege for this operation

mysql> grant replication slave on . to p1@localhost;

mysql> show slave hosts;

Empty set (0.00 sec)

mysql>show binlog events;

+—————+——-+—————-+———–+————-+————–+

| Log_name | Pos | Event_type | Server_id| End_log_pos|Info |

+—————+——-+————–+———–+————-+—————+

| mysql-bin.000005 | 4 | Format_desc | 1 | 98 | Server ver: 5.0.77-log, Binlog ver: 4 | |mysql-bin.000005|98|Query|1|197|use mysql; create table a1(i int)engine=myisam|

……………………………………

23. Shutdown

關(guān)閉MySQL：

[mysql@mydev ~]$ mysqladmin shutdown

重新連接：

[mysql@mydev ~]$ mysql

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)

[mysql@mydev ~]$ cd /u01/mysql/bin

[mysql@mydev bin]$ ./mysqld_safe &

[mysql@mydev bin]$ mysql

24. grant option

擁有g(shù)rant option洛巢，就可以將自己擁有的權(quán)限授予其他用戶（僅限于自己已經(jīng)擁有的權(quán)限）

mysql> grant Grant option on pyt.* to p1@localhost;

mysql> grant select on pyt.* to p2@localhost;

25. file

擁有file權(quán)限才可以執(zhí)行 select ..into outfile和load data infile…操作，但是不要把file, process, super權(quán)限授予管理員以外的賬號(hào)次兆，這樣存在嚴(yán)重的安全隱患稿茉。

mysql> grant file on . to p1@localhost;

mysql> load data infile ‘/home/mysql/pet.txt’ into table pet;

26. super

這個(gè)權(quán)限允許用戶終止任何查詢；修改全局變量的SET語句芥炭；使用CHANGE MASTER漓库，PURGE MASTER LOGS。

mysql> grant super on . to p1@localhost;

mysql> purge master logs before ‘mysql-bin.000006′;

27. process

通過這個(gè)權(quán)限园蝠，用戶可以執(zhí)行SHOW PROCESSLIST和KILL命令渺蒿。默認(rèn)情況下，每個(gè)用戶都可以執(zhí)行SHOW PROCESSLIST命令砰琢，但是只能查詢本用戶的進(jìn)程蘸嘶。

mysql> show processlist;

+—-+——+———–+——+———+——+——-+——————+

| Id | User | Host | db | Command | Time | State | Info |

+—-+——+———–+——+———+——+——-+——————+

| 12 | p1 | localhost | pyt | Query | 0 | NULL | show processlist |

+—-+——+———–+——+———+——+——-+——————+

另外良瞧，

管理權(quán)限（如 super陪汽， process训唱， file等）不能夠指定某個(gè)數(shù)據(jù)庫，on后面必須跟.

mysql> grant super on pyt.* to p1@localhost;

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

mysql> grant super on . to p1@localhost;

Query OK, 0 rows affected (0.01 sec)