測試環境為 CentOS 7 x86_64
使用者的權限可以區分為
- 登入權限,請參考 https://benjr.tw/97973
- 使用者存取 資料庫(DateBase)/資料表(Table) 權限,下面來討論.
- 使用者自訂,使用 SQL 函數的權限,請參考 https://benjr.tw/99060
權限有分為 管理權限 結構權限 與 資料權限
- 管理權限
GRANT , SUPER , PROCESS , RELOAD , SHUTDOWN , SHOW DATABASES , LOCK TABLES , REFERENCES , REPLICATION CLIENT , REPLICATION SLAVE , CREATE USER - 結構權限
CREATE , ALTER , INDEX , DROP , CREATE TEMPORARY TABLES , SHOW VIEW , CREATE ROUTINE , ALTER ROUTINE , EXECUTE , CREATE VIEW , EVENT , TIGGER - 資料權限
SELECT , INSERT , UPDATE , DELETE , FILE
這邊先來看一下資料庫 SELECT , INSERT , UPDATE , DELETE 與 FILE 的權限,先來建立要測試用的資料庫與不同的使用者.
[root@localhost ~]$ mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 67 Server version: 5.5.52-MariaDB MariaDB Server Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> CREATE DATABASE user1Data; Query OK, 1 row affected (0.00 sec)
user1 只能透過本機 localhost 登入,user2 可以從任意處登入 (% 表示任意).
MariaDB [(none)]> CREATE USER user1@localhost IDENTIFIED BY '111111'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> CREATE USER user2@'%' IDENTIFIED BY '111111'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SELECT host,user FROM mysql.user; +-----------------------+-------+ | host | user | +-----------------------+-------+ | % | user2 | | 127.0.0.1 | root | | ::1 | root | | localhost | root | | localhost | user1 | | localhost.localdomain | root | +-----------------------+-------+ 7 rows in set (0.000 sec)
GRANT
user1 給予 資料庫 (user1Date ) 的全部權限 (ALL PRIVILEGES) , user2 只有讀取 (SELECT) 資料庫 (user1Data) 與 FILE(全域) 的權限.
MariaDB [(none)]> GRANT ALL PRIVILEGES ON user1Data.* TO user1@localhost; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> SHOW GRANTS FOR user1@localhost; +--------------------------------------------------------------------------------------------------------------+ | Grants for user1@localhost | +--------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*FD571203974BA9AFE270FE62151AE967ECA5E0AA' | | GRANT ALL PRIVILEGES ON `user1Data`.* TO 'user1'@'localhost' | +--------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
MariaDB [(none)]> GRANT SELECT ON user1Data.* TO user2@'%'; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> GRANT FILE ON *.* TO user2@'%'; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> SHOW GRANTS FOR user2@'%'; +-----------------------------------------------------------------------------------------------------+ | Grants for user2@% | +-----------------------------------------------------------------------------------------------------+ | GRANT FILE ON *.* TO 'user2'@'%' IDENTIFIED BY PASSWORD '*FD571203974BA9AFE270FE62151AE967ECA5E0AA' | | GRANT SELECT ON `user1Data`.* TO 'user2'@'%' | +-----------------------------------------------------------------------------------------------------+ 2 rows in set (0.000 sec) MariaDB [(none)]> EXIT; Bye
指令說明 :
剛建立建立帳號與給予權限的指令是分開下的,其實是可以同的,如下所示:
MariaDB [(none)]> GRANT ALL PRIVILEGES ON user1Data.* TO user1@localhost IDENTIFIED BY ‘your.password’ ; MariaDB [(none)]> SHOW GRANTS FOR user1@localhost; +--------------------------------------------------------------------------------------------------------------+ | Grants for user1@localhost | +--------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*FD571203974BA9AFE270FE62151AE967ECA5E0AA' | | GRANT ALL PRIVILEGES ON `user1Data`.* TO 'user1'@'localhost' | +--------------------------------------------------------------------------------------------------------------+
- GRANT USAGE
第一行代表 Global Level 的權限,user1 的 USAGE 代表沒權限 ( no privileges ),而剛剛的 user2 則是有 FILE 權限. - GRANT ALL PRIVILEGES
包含 資料 ( SELECT , INSERT , UPDATE , DELETE ) , 結構 ( CREATE , ALTER , INDEX , DROP , CREATE TEMPORARY TABLES , SHOW VIEW , CREATE ROUTINE , ALTER ROUTINE , EXECUTE , CREATE VIEW , EVENT , TRIGGER ) ,管理 ( LOCK TABLES , REFERENCES ) 的權限.可以設定的權限包含 ALL [PRIVILEGES] , ALTER, ALTER ROUTINE , CREATE , CREATE ROUTINE , CREATE TABLESPACE , CREATE TEMPORARY TABLES , CREATE USER , CREATE VIEW , DELETE , DROP , EVENT , EXECUTE , FILE , GRANT OPTION , INDEX , INSERT , LOCK TABLES , PROCESS , PROXY , REFERENCES , RELOAD , REPLICATION CLIENT , REPLICATION SLAVE , SELECT , SHOW VIEW , SHUTDOWN , SUPER , TRIGGER, UPDATE , USAGE
下面會針對 SELECT , INSERT , UPDATE , DELETE 與 FILE 的權限來說明,其他權限請參考 https://dev.mysql.com/doc/refman/5.7/en/grant.html 說明
- user1.*
指定可以存取哪些 Db_name/Table - user1@localhost
格式 : username@localhost
username – 新增的使用者名稱
localhost 限制可從哪裡來存取, localhost 代表只能從本機登入,172.16.15.% 代表可以從 172.16.15.* 網段登入.如果要任意處登入可以使用 user@’%’ (% 表示任意). - identified by ‘your.password’
設定使用者密碼 ( password ) .
下面我們就登入個別的使用者來測試權限.
User1
測試 user1 對於 user1Data 資料庫存取權限.
[root@localhost ~]$ mysql -u user1 -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 52 Server version: 10.0.31-MariaDB-0ubuntu0.16.04.2 Ubuntu 16.04 Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | user1Data | +--------------------+ 2 rows in set (0.00 sec)
測試一下 User1 等權限.
MariaDB [(none)]> USE user1Data; Database changed MariaDB [user1Data]> CREATE TABLE employee (Name CHAR(20),Dept CHAR(20),jobTitle CHAR(20)); Query OK, 0 rows affected (0.03 sec) MariaDB [user1Data]> DESCRIBE employee; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | Name | char(20) | YES | | NULL | | | Dept | char(20) | YES | | NULL | | | jobTitle | char(20) | YES | | NULL | | +----------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
- INSERT
MariaDB [user1Data]> INSERT INTO employee VALUES ('Ben','Testing','Engineer'), ('Afa','Power','Engineer'); Query OK, 2 rows affected (0.003 sec) Records: 2 Duplicates: 0 Warnings: 0 - SELECT
MariaDB [user1Data]> SELECT * FROM employee; +------+---------+----------+ | Name | Dept | jobTitle | +------+---------+----------+ | Ben | Testing | Engineer | | Afa | Power | Engineer | +------+---------+----------+ 2 rows in set (0.00 sec)
- DELETE
MariaDB [user1Data]> DELETE FROM employee WHERE Name LIKE 'Ben'; Query OK, 1 row affected (0.003 sec) MariaDB [user1Data]> SELECT * FROM employee; +------+-------+----------+ | Name | Dept | jobTitle | +------+-------+----------+ | Afa | Power | Engineer | +------+-------+----------+ 1 row in set (0.001 sec)
- FILE
當我們使用 LOAD_FILE 函數時,需要有 FILE 的權限,透過指定載入檔案位置(只限定位於 MySQL Server Host 上的檔案)來讀檔案.檔案讀取時需要注意權限問題(該使用者需有 File 的權限,可使用 SQL 指令 SHOW PRIVILEGES; 來檢視),無法讀取時 LOAD_FILE 函數會回傳 NULL .得到 NULL 代表 mysql 使用者無法讀取該檔案,這時要注意存放檔案的目錄需要可以進入 (a+x) 的權限,檔案本身至少需要可供讀取 (a+r) 的權限.
很可惜當初設定 user1 時,沒有 FILE 權限,無法讀取檔案,顯示為 NULL.
[root@localhost ~]# cat /var/ftp/file.txt TEST
MariaDB [user1Data]> SET @txt = LOAD_FILE("/var/ftp/file.txt"); Query OK, 0 rows affected (0.000 sec) MariaDB [user1Data]> SELECT @txt; +------+ | @txt | +------+ | NULL | +------+ 1 row in set (0.000 sec)MariaDB [user1Data]> EXIT; Bye
USer2
測試一下 User2 的權限,可以發現只有 SELECT 與 FILE 權限.
[root@localhost ~]$ mysql -u user2 -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 33 Server version: 10.0.31-MariaDB-0ubuntu0.16.04.2 Ubuntu 16.04 Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> USE user1Data; Database changed
- INSERT
MariaDB [user1Data]> INSERT INTO employee VALUES ('Ben','Testing','Engineer'), ('Afa','Power','Engineer'); ERROR 1142 (42000): INSERT command denied to user 'user2'@'localhost' for table 'employee' - SELECT
MariaDB [user1Data]> SELECT * FROM employee; +------+-------+----------+ | Name | Dept | jobTitle | +------+-------+----------+ | Afa | Power | Engineer | +------+-------+----------+ 1 row in set (0.001 sec)
- DELETE
MariaDB [user1Data]> DELETE FROM employee WHERE Name LIKE 'Afa'; ERROR 1142 (42000): DELETE command denied to user 'user2'@'localhost' for table 'employee' MariaDB [user1Data]> SELECT * FROM employee; +------+-------+----------+ | Name | Dept | jobTitle | +------+-------+----------+ | Afa | Power | Engineer | +------+-------+----------+ 1 row in set (0.001 sec)
- FILE
當我們使用 LOAD_FILE 函數時,需要有 FILE 的權限,透過指定載入檔案位置(只限定位於 MySQL Server Host 上的檔案)來讀檔案.檔案讀取時需要注意權限問題(該使用者需有 File 的權限,可使用 SQL 指令 SHOW PRIVILEGES; 來檢視),無法讀取時 LOAD_FILE 函數會回傳 NULL .得到 NULL 代表 mysql 使用者無法讀取該檔案,這時要注意存放檔案的目錄需要可以進入 (a+x) 的權限,檔案本身至少需要可供讀取 (a+r) 的權限.
[root@localhost ~]# cat /var/ftp/file.txt TEST
MariaDB [user1Data]> SET @txt = LOAD_FILE("/var/ftp/file.txt"); Query OK, 0 rows affected (0.000 sec) MariaDB [user1Data]> SELECT @txt; +-------+ | @txt | +-------+ | TEST | +-------+ 1 row in set (0.000 sec)MariaDB [user1Data]> EXIT; Bye
REVOKE
如果要移除 user1 其 user1Date 資料庫的全部權限:
[root@localhost ~]$ mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 67 Server version: 5.5.52-MariaDB MariaDB Server Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> REVOKE ALL PRIVILEGES ON user1Data.* FROM user1@localhost; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> SHOW GRANTS FOR user1@localhost; +--------------------------------------------------------------------------------------------------------------+ | Grants for user1@localhost | +--------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*FD571203974BA9AFE270FE62151AE967ECA5E0AA' | +--------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
user2 的 FILE 全域權限要怎麼移除.
MariaDB [(none)]> REVOKE FILE ON *.* FROM user2@'%'; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> SHOW GRANTS FOR user2@'%'; +------------------------------------------------------------------------------------------------------+ | Grants for user2@% | +------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'user2'@'%' IDENTIFIED BY PASSWORD '*FD571203974BA9AFE270FE62151AE967ECA5E0AA' | | GRANT SELECT ON `user1Data`.* TO 'user2'@'%' | +------------------------------------------------------------------------------------------------------+ 2 rows in set (0.000 sec)
DROP USER
如果要移除 user1 使用者.
MariaDB [(none)]> DROP USER user1@'localhost'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SELECT host,user FROM mysql.user; +-----------------------+-------+ | host | user | +-----------------------+-------+ | % | user2 | | 127.0.0.1 | root | | ::1 | root | | localhost | root | | localhost.localdomain | root | +-----------------------+-------+ 6 rows in set (0.001 sec)
沒有解決問題,試試搜尋本站其他內容