2,283 瀏覽數

MariaDB 使用者存取 DateBase/Table/Column 權限

測試環境為 CentOS 7 x86_64

使用者的權限可以區分為

  1. 登入權限,請參考 http://benjr.tw/97973
  2. 使用者存取 資料庫(DateBase)/資料表(Table)/資料欄(column)權限,下面來討論.

使用者存取 資料庫(DateBase)/資料表(Table)/資料欄(column)權限
資料庫 (DateBase) 的權限
ALL PRIVILEGES , ALTER , CREATE , DELETE , DROP , FILE , INDEX , INSERT , PROCESS , REFERENCES , RELOAD , SELECT , SHUTDOWN , UPDATE , USAGE
資料表(Table) 的權限
SELECT , INSERT , UPDATE , DELETE , CREATE , DROP , INDEX , ALTER
資料欄(column) 的權限
SELECT , INSERT , UPDATE

user1 給予 資料庫 (user1Date ) 的全部權限 (ALL PRIVILEGES) , user2 只有 操作 (select) 資料庫 (user1Data) 的權限.

[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)
MariaDB [(none)]> CREATE USER user1@localhost IDENTIFIED BY '111111';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> CREATE USER user2@'172.16.15.%' IDENTIFIED BY '111111';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select host,user from mysql.user;
+-------------+-------+
| host        | user  |
+-------------+-------+
| localhost   | root  |
| localhost   | user1 |
| 172.16.15.% | user2 |
+-------------+-------+
4 rows in set (0.00 sec)
MariaDB [(none)]> grant all privileges on user1Data.* to user1@localhost;
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@'172.16.15.%';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show grants for user2@'172.16.15.%';
+----------------------------------------------------------------------------------------------------------------+
| Grants for user2@172.16.15.%                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'172.16.15.%' IDENTIFIED BY PASSWORD '*FD571203974BA9AFE270FE62151AE967ECA5E0AA' |
| GRANT SELECT ON `user1Data`.* TO 'user2'@'172.16.15.%'                                                         |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> exit;
Bye

新增加的使用者都有 GRANT USAGE ON *.* 權限, USAGE 代表了登入權限而已,並沒有操作任何資料庫的權限.

建立帳號與給予權限的指令可以同時下的,如下所示:

> grant all privileges on user1Data.* to user1@localhost identified by ‘your.password’ ;

grant 參數
all privileges
授權的權限 (ALL PRIVILEGES, SELECT, INSERT, DELETE …. etc,請參考前面的權限說明)
user1.*
指定可以存取哪些 Db_name/Table
user1@localhost
username@localhost , username – 新增的使用者, localhost – 限制可從哪裡來存取, 可用 % (代表全部)
identified by ‘your.password’
password: 使用者密碼

測試 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 use , create table , Insert 等權限.

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)

MariaDB [user1Data]> INSERT INTO employee VALUES ('Ben','Testing','Engineer');
Query OK, 1 row affected (0.00 sec)

MariaDB [user1Data]> INSERT INTO employee VALUES ('Afa','Power','Engineer');
Query OK, 1 row affected (0.01 sec)

MariaDB [user1Data]> SELECT * from employee;
+------+---------+----------+
| Name | Dept    | jobTitle |
+------+---------+----------+
| Ben  | Testing | Engineer |
| Afa  | Power   | Engineer |
+------+---------+----------+
2 rows in set (0.00 sec)
MariaDB [user1Data]> exit;
Bye

測試一下 User2 的權限.

[root@localhost ~]$ mysql -u user2 -p -h 172.16.15.129
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)]> SELECT * from user1Data.employee;
+------+---------+----------+
| Name | Dept    | jobTitle |
+------+---------+----------+
| Ben  | Testing | Engineer |
| Afa  | Power   | Engineer |
+------+---------+----------+
2 rows in set (0.00 sec)

MariaDB [(none)]> INSERT INTO user1Data.employee VALUES ('Cars','Testing','Senior Engineer');
ERROR 1142 (42000): INSERT command denied to user 'user2'@'172.16.15.129' for table 'employee'
MariaDB [(none)]> exit;
Bye

user2 只有 select 的權限.

如果要移除 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, grant option from user1@localhost;
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' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

如果要移除 user1 使用者.

MariaDB [(none)]> drop user user1@'localhost';
Query OK, 0 rows affected (0.00 sec)

發表迴響