mysql用戶的建表權限,mysql表權限分配表權限

本文目錄一覽：

1、mysql數據庫全局權限什麼意思
2、MySQL 數據庫，如何分角色權限建表？
3、如何用phpmyadmin設置mysql數據庫用戶的權限
4、MySQL的權限有哪些
5、mysql給用戶創建數據庫的權限嗎

mysql數據庫全局權限什麼意思

一．權限表

mysql數據庫中的3個權限表：user 、db、 host

權限表的存取過程是：

1)先從user表中的host、 user、 password這3個字段中判斷連接的IP、用戶名、密碼是否存在表中，存在則通過身份驗證；

2)通過權限驗證，進行權限分配時，按照useràdbàtables_privàcolumns_priv的順序進行分配。即先檢查全局權限表user，如果user中對應的權限為Y，則此用戶對所有數據庫的權限都為Y，將不再檢查db, tables_priv,columns_priv；如果為N，則到db表中檢查此用戶對應的具體數據庫，並得到db中為Y的權限；如果db中為N，則檢查tables_priv中此數據庫對應的具體表，取得表中的權限Y，以此類推。

二．MySQL各種權限（共27個）

（以下操作都是以root身份登陸進行grant授權，以p1@localhost身份登陸執行各種命令。）

1. usage

連接（登陸）權限，建立一個用戶，就會自動授予其usage權限（默認授予）。

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

該權限只能用於數據庫登陸，不能執行任何操作；且usage權限不能被回收，也即REVOKE用戶並不能刪除用戶。

MySQL 數據庫，如何分角色權限建表？

角色一直存在各個數據庫中，比如 SQL Server、Oracle 等，MySQL 自從版本 8.0 release，引入了角色這個概念。

角色的概念

角色就是一組針對各種數據庫權限的集合。比如，把一個角色分配給一個用戶，那這個用戶就擁有了這個角色包含的所有權限。一個角色可以分配給多個用戶，另外一個用戶也可以擁有多個角色，兩者是多對多的關係。不過 MySQL 角色目前還沒有提供類似於其他數據庫的系統預分配的角色。比如某些數據庫的 db_owner、 db_datareader 、 db_datawriter 等等。那接下來我分幾個方面，來示例說明角色的使用以及相關注意事項。

示例 1：一個完整角色的授予步驟

用管理員創建三個角色：db_owner, db_datareader, db_datawriter

mysql create role db_owner,db_datareader,db_datawriter;

Query OK, 0 rows affected (0.02 sec)

mysql grant all on ytt_new.* to db_owner;

Query OK, 0 rows affected (0.01 sec)

mysql grant select on ytt_new.* to db_datareader;

Query OK, 0 rows affected (0.01 sec)

mysql grant insert,delete,update on ytt_new.* to db_datawriter;

Query OK, 0 rows affected (0.01 sec)

創建三個普通用戶，分別為 ytt1、ytt2、ytt3。mysql create user ytt1 identified by ‘ytt’,ytt2 identified by ‘ytt’,ytt3 identified by ‘ytt’;Query OK, 0 rows affected (0.01 sec)

分別授予這三個用戶對應的角色。

— 授權角色

mysql grant db_owner to ytt1;

Query OK, 0 rows affected (0.02 sec)

— 激活角色

mysql set default role db_owner to ytt1;

Query OK, 0 rows affected (0.00 sec)

mysql grant db_datareader to ytt2;

Query OK, 0 rows affected (0.01 sec)

mysql set default role db_datareader to ytt2;

Query OK, 0 rows affected (0.01 sec)

mysql grant db_datawriter to ytt3;

Query OK, 0 rows affected (0.01 sec)

mysql set default role db_datawriter to ytt3;

Query OK, 0 rows affected (0.01 sec)

以上是角色授予的一套完整步驟。那上面有點非常規的地方是激活角色這個步驟。MySQL 角色在創建之初默認是沒有激活的，也就是說創建角色，並且給一個用戶特定的角色，這個用戶其實並不能直接使用這個角色，除非激活了才可以。

示例 2：一個用戶可以擁有多個角色

— 用管理員登錄並且創建用戶

mysql create user ytt4 identified by ‘ytt’;

Query OK, 0 rows affected (0.00 sec)

— 把之前的三個角色都分配給用戶ytt4.

mysql grant db_owner,db_datareader,db_datawriter to ytt4;

Query OK, 0 rows affected (0.01 sec)

— 激活用戶ytt4的所有角色.

mysql set default role all to ytt4;

Query OK, 0 rows affected (0.02 sec)

— ytt4 用戶登錄

root@ytt-pc:/var/lib/mysql# mysql -uytt4 -pytt -P3304 -hytt-pc

…

— 查看當前角色列表

mysql select current_role();

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

| current_role() |

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

| `db_datareader`@`%`,`db_datawriter`@`%`,`db_owner`@`%` |

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

1 row in set (0.00 sec)

— 簡單創建一張表並且插入記錄，檢索記錄，完了刪掉這張表

mysql use ytt_new

Database changed

mysql create table t11(id int);

Query OK, 0 rows affected (0.05 sec)

mysql insert into t11 values (1);

Query OK, 1 row affected (0.02 sec)

mysql select * from t11;

+——+

| id |

+——+

| 1 |

+——+

1 row in set (0.00 sec)

mysql drop table t11;

Query OK, 0 rows affected (0.04 sec)

示例 3：用戶在當前 session 里角色互換

其實意思是說，用戶連接到 MySQL 服務器後，可以切換當前的角色列表，比如由 db_owner 切換到 db_datareader。

— 還是之前的用戶ytt4, 切換到db_datareader

mysql set role db_datareader;

Query OK, 0 rows affected (0.00 sec)

mysql select current_role();

+———————+

| current_role() |

+———————+

| `db_datareader`@`%` |

+———————+

1 row in set (0.00 sec)

— 切換後，沒有權限創建表

mysql create table t11(id int);

ERROR 1142 (42000): CREATE command denied to user ‘ytt4’@’ytt-pc’ for table ‘t11’

— 切換到 db_owner，恢復所有權限。

mysql set role db_owner;

Query OK, 0 rows affected (0.00 sec)

mysql create table t11(id int);

Query OK, 0 rows affected (0.04 sec)

示例 4：關於角色的兩個參數

activate_all_roles_on_login：是否在連接 MySQL 服務時自動激活角色mandatory_roles：強制所有用戶默認角色

— 用管理員連接MySQL,

— 設置默認激活角色

mysql set global activate_all_roles_on_login=on;

Query OK, 0 rows affected (0.00 sec)

— 設置強制給所有用戶賦予角色db_datareader

mysql set global mandatory_roles=’db_datareader’;

Query OK, 0 rows affected (0.00 sec)

— 創建用戶ytt7.

mysql create user ytt7;

Query OK, 0 rows affected (0.01 sec)

— 用 ytt7登錄數據庫

root@ytt-pc:/var/lib/mysql# mysql -uytt7 -P3304 -hytt-pc

…

mysql show grants;

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

| Grants for ytt7@% |

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

| GRANT USAGE ON *.* TO `ytt7`@`%` |

| GRANT SELECT ON `ytt_new`.* TO `ytt7`@`%` |

| GRANT `db_datareader`@`%` TO `ytt7`@`%` |

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

3 rows in set (0.00 sec)

示例 5 ：create role 和 create user 都有創建角色權限，兩者有啥區別？

以下分別創建兩個用戶 ytt8、ytt9，一個給 create role，一個給 create user 權限。

— 管理員登錄，創建用戶ytt8,ytt9.

mysql create user ytt8,ytt9;

Query OK, 0 rows affected (0.01 sec)

mysql grant create role on *.* to ytt8;

Query OK, 0 rows affected (0.02 sec)

mysql grant create user on *.* to ytt9;

Query OK, 0 rows affected (0.01 sec)

— 用ytt8 登錄，

root@ytt-pc:/var/lib/mysql# mysql -uytt8 -P3304 -hytt-pc

…

mysql create role db_test;

Query OK, 0 rows affected (0.02 sec)

— 可以創建角色，但是不能創建用戶

mysql create user ytt10;

ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation

mysql \q

Bye

— 用ytt9 登錄

root@ytt-pc:/var/lib/mysql# mysql -uytt9 -P3304 -hytt-pc

…

— 角色和用戶都能創建

mysql create role db_test2;

Query OK, 0 rows affected (0.02 sec)

mysql create user ytt10;

Query OK, 0 rows affected (0.01 sec)

mysql \q

Bye

那這裡其實看到 create user 包含了 create role，create user 即可以創建用戶，也可以創建角色。

示例 6：MySQL 用戶也可以當角色來用

— 用管理員登錄，創建用戶ytt11,ytt12.

mysql create user ytt11,ytt12;

Query OK, 0 rows affected (0.01 sec)

mysql grant select on ytt_new.* to ytt11;

Query OK, 0 rows affected (0.01 sec)

— 把ytt11普通用戶的權限授予給ytt12

mysql grant ytt11 to ytt12;

Query OK, 0 rows affected (0.01 sec)

— 來查看 ytt12的權限，可以看到擁有了ytt11的權限

mysql show grants for ytt12;

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

| Grants for ytt12@% |

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

| GRANT USAGE ON *.* TO `ytt12`@`%` |

| GRANT `ytt11`@`%` TO `ytt12`@`%` |

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

2 rows in set (0.00 sec)

— 在細化點，看看ytt12擁有哪些具體的權限

mysql show grants for ytt12 using ytt11;

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

| Grants for ytt12@% |

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

| GRANT USAGE ON *.* TO `ytt12`@`%` |

| GRANT SELECT ON `ytt_new`.* TO `ytt12`@`%` |

| GRANT `ytt11`@`%` TO `ytt12`@`%` |

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

3 rows in set (0.00 sec)

示例 7：角色的撤銷

角色撤銷和之前權限撤銷類似。要麼 revoke，要麼刪除角色，那這個角色會從所有擁有它的用戶上移除。

— 用管理員登錄，移除ytt2的角色

mysql revoke db_datareader from ytt2;

Query OK, 0 rows affected (0.01 sec)

— 刪除所有角色

mysql drop role db_owner,db_datareader,db_datawriter;

Query OK, 0 rows affected (0.01 sec)

— 對應的角色也從ytt1上移除掉了

mysql show grants for ytt1;

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

| Grants for ytt1@% |

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

| GRANT USAGE ON *.* TO `ytt1`@`%` |

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

1 row in set (0.00 sec)

至此，我分了 7 個目錄說明了角色在各個方面的使用以及注意事項，希望對大家有幫助。

如何用phpmyadmin設置mysql數據庫用戶的權限

首先打開phpMyadmin;

點擊用戶菜單；

在任意用戶菜單上點擊“編輯權限”；

修改密碼點擊執行就OK了

權限意思可以對照下面翻譯：

數據：

SELECT：允許讀取數據。

INSERT：允許插入和替換數據。

UPDATA：允許更改數據。

DELETE：允許刪除數據。

FILE：允許從文件中導入數據以及將數據導出至文件。

結構：

CREATE：允許創建新數據庫和表。

ALTER：允許修改現有表的結構。

INDEX：允許創建和刪除索引。

DROP：允許刪除數據庫和表。

CREATE TEMPORARY TABLES：允許創建暫時表。

CREATE VIEW：允許創建新的意見。

SHOW VIEW：顯示創建的看法。

CREATE ROUTINE：允許創建存儲過程。

ALTER ROUTINE：允許改變和下降存儲過程。

EXECUTE：允許許執行存儲過程。

管理：

GRANT：允許添加用戶和權限，而不允許重新載入權限表。

SUPER：允許在達到最大允許數目時仍進行連接。

PROCESS：允許查看進程列表中的完整查詢。

RELOAD：允許重新載入服務器設置並刷新服務器的緩存。

SHUTDOWN：允許關閉服務器。

SHOW DATABASES：允許訪問完整的數據庫列表。

LOCK TABLES：允許鎖住當前線索的表。

REFERENCES：在此版本的 MySQL 中無效。

REPLICATION CLIENT：用戶有權詢問附屬者/控制者在哪裡。

REPLICATION SLAVE：回復附屬者所需。

CREATE USER：允許創建，下降和重新命名的用戶帳戶。

MySQL的權限有哪些

MySQL各種權限（共27個）

（以下操作都是以root身份登陸進行grant授權，以p1@localhost身份登陸執行各種命令。）

1. usage

連接（登陸）權限，建立一個用戶，就會自動授予其usage權限（默認授予）。

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

該權限只能用於數據庫登陸，不能執行任何操作；且usage權限不能被回收，也即REVOKE用戶並不能刪除用戶。

2. select

必須有select的權限，才可以使用select table

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

mysql select * from shop;

3. create

必須有create的權限，才可以使用create table

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

4. create routine

必須具有create routine的權限，才可以使用{create |alter|drop} {procedure|function}

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

當授予create routine時，自動授予EXECUTE, ALTER ROUTINE權限給它的創建者：

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的權限，才可以使用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的權限，才可以使用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數據庫的全局CREATE USER權限，或擁有INSERT權限。

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

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

8. insert

必須有insert的權限，才可以使用insert into ….. values….

9. alter

必須有alter的權限，才可以使用alter table

alter table shop modify dealer char(15);

10. alter routine

必須具有alter routine的權限，才可以使用{alter |drop} {procedure|function}

mysqlgrant 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的權限，才可以使用update table

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

12. delete

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

13. drop

必須有drop的權限，才可以使用drop database db_name; drop table tab_name;

drop view vi_name; drop index in_name;

14. show database

通過show database只能看到你擁有的某些權限的數據庫，除非你擁有全局SHOW DATABASES權限。

對於p1@localhost用戶來說，沒有對mysql數據庫的權限，所以以此身份登陸查詢時，無法看到mysql數據庫：

mysql show databases;

+——————–+

| Database |

+——————–+

| information_schema|

| pyt |

| test |

+——————–+

15. show view

必須擁有show view權限，才能執行show create view。

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

mysql show create view v_shop;

16. index

必須擁有index權限，才能執行[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

執行存在的Functions,Procedures

mysql call pro_shop1(0001,@a)；

+———+

| article |

+———+

| 0001 |

+———+

mysql select @a;

+——+

| @a |

+——+

| 2 |

+——+

18. lock tables

必須擁有lock tables權限，才可以使用lock tables

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

mysql lock tables a1 read;

mysql unlock tables;

19. references

有了REFERENCES權限，用戶就可以將其它表的一個字段作為某一個表的外鍵約束。

20. reload

必須擁有reload權限，才可以執行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

擁有此權限可以查詢master server、slave server狀態。

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;

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

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

| mysql-bin.000006 | 2111 | | |

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

mysql show slave status;

22. replication slave

擁有此權限可以查看從服務器，從主服務器讀取二進制日誌。

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)

mysqlshow binlog events;

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

| 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

關閉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

擁有grant option，就可以將自己擁有的權限授予其他用戶（僅限於自己已經擁有的權限）

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

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

25. file

擁有file權限才可以執行 select ..into outfile和load data infile…操作，但是不要把file, process, super權限授予管理員以外的賬號，這樣存在嚴重的安全隱患。

mysql grant file on *.* to p1@localhost;

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

26. super

這個權限允許用戶終止任何查詢；修改全局變量的SET語句；使用CHANGE MASTER，PURGE MASTER LOGS。

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

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

27. process

通過這個權限，用戶可以執行SHOW PROCESSLIST和KILL命令。默認情況下，每個用戶都可以執行SHOW PROCESSLIST命令，但是只能查詢本用戶的進程。

mysql show processlist;

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

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

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

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

另外，

管理權限（如 super， process， file等）不能夠指定某個數據庫，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)

mysql給用戶創建數據庫的權限嗎

登錄MySQL

[plain] view plain copy print?

mysql -u root -p

添加新用戶

允許本地 IP 訪問 localhost, 127.0.0.1

[plain] view plain copy print?

create user ‘test’@’localhost’ identified by ‘123456’;

允許外網 IP 訪問

[plain] view plain copy print?

create user ‘test’@’%’ identified by ‘123456’;

刷新授權

[sql] view plain copy print?

flush privileges;

為用戶創建數據庫

[sql] view plain copy print?

create database test DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

為新用戶分配權限

授予用戶通過外網IP對於該數據庫的全部權限

[sql] view plain copy print?

grant all privileges on `testdb`.* to ‘test’@’%’ identified by ‘123456’;

授予用戶在本地服務器對該數據庫的全部權限

[sql] view plain copy print?

grant all privileges on `testdb`.* to ‘test’@’localhost’ identified by ‘123456’;

刷新權限

[sql] view plain copy print?

flush privileges;

退出 root 重新登錄

[sql] view plain copy print?

exit

用新帳號 test 重新登錄，由於使用的是 % 任意IP連接，所以需要指定外部訪問IP

原創文章，作者：小藍，如若轉載，請註明出處：https://www.506064.com/zh-hant/n/194730.html

mysql用戶的建表權限,mysql表權限分配表權限

本文目錄一覽：

mysql數據庫全局權限什麼意思

MySQL 數據庫，如何分角色權限建表？

如何用phpmyadmin設置mysql數據庫用戶的權限

MySQL的權限有哪些

mysql給用戶 創建數據庫的權限嗎

相關推薦

發表回復

mysql給用戶創建數據庫的權限嗎