MariaDB 使用者自訂,使用 SQL 函數權限

測試環境為 CentOS 7 x86_64

使用者的權限可以區分為

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

權限有分為 管理權限 結構權限 與 資料權限

  • 管理權限
    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

如果是要讓使用者自訂,使用 SQL 函數的權限是要怎麼設定??

  • 自訂,管理儲存的 SQL 函數
    自訂 SQL 函數需要有 CREATE ROUTINE 權限,修改已儲存的 SQL 自訂函數需要有 ALTER ROUTINE 權限.
  • 使用 SQL 函數
    使用 SQL 自訂函數需要有 EXECUTE 權限.

先自定一個資料庫 Fun ,與 2 個使用者 user1 與 user2 .

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 5.5.56-MariaDB MariaDB Server

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)]> create database Fun;
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> CREATE USER user1@localhost IDENTIFIED BY '111111';
Query OK, 0 rows affected (0.03 sec)

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

GRANT

user1 給予 自訂 SQL 函數權限,user2 給予使用 SQL 函數權限.

MariaDB [(none)]> GRANT CREATE ROUTINE on Fun.* to user1@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT EXECUTE on Fun.* to user2@localhost;
Query OK, 0 rows affected (0.00 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 CREATE ROUTINE ON `Fun`.* TO 'user1'@'localhost'                                                       |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

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

MariaDB [(none)]> exit;
Bye

User 1 有自訂與使用 SQL 函數權限.

[root@localhost ~]# mysql -u user1 -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 5.5.56-MariaDB MariaDB Server

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 Fun;
Database changed

MariaDB [Fun]> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(20) DETERMINISTIC RETURN CONCAT(s,'!');
Query OK, 0 rows affected (0.01 sec)

MariaDB [Fun]> SHOW FUNCTION STATUS;
+-----+--------------+----------+-----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db  | Name         | Type     | Definer         | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+-----+--------------+----------+-----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Fun | hello        | FUNCTION | user1@localhost | 2018-05-03 05:49:39 | 2018-05-03 05:49:39 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+-----+--------------+----------+-----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 rows in set (0.00 sec)

MariaDB [Fun]> select hello('Testing');
+------------------+
| hello('Testing') |
+------------------+
| Testing!         |
+------------------+
1 row in set (0.00 sec)

MariaDB [Fun]> exit;
Bye
  • CREATE FUNCTION hello (s CHAR(20))
    建立一個函數,須包含函數名稱與傳入值.
  • RETURNS CHAR(20)
    回傳值.

User 2 有使用 SQL 函數權限,但無 自訂SQL 函數權限..

[root@localhost ~]# mysql -u user2 -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 5.5.56-MariaDB MariaDB Server

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 Fun;
Database changed
MariaDB [Fun]> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(20) DETERMINISTIC RETURN CONCAT(s,'!');
ERROR 1044 (42000): Access denied for user 'user2'@'localhost' to database 'Fun'
MariaDB [Fun]> select hello('Testing');
+------------------+
| hello('Testing') |
+------------------+
| Testing!         |
+------------------+
1 row in set (0.00 sec)

MariaDB [Fun]> exit;
Bye

自訂,管理儲存及使用 SQL 函數除了針對單一資料庫 mysql.db table ,也可以 mysql.user table , mysql.procs_priv table (specific routine).

REVOKE

如果要移除 user1 其自訂,管理儲存的 SQL 函數權限:

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 5.5.56-MariaDB MariaDB Server

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 grants for user1@localhost;
+--------------------------------------------------------------------------------------------------------------+
| Grants for user1@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*FD571203974BA9AFE270FE62151AE967ECA5E0AA' |
| GRANT CREATE ROUTINE ON `Fun`.* TO 'user1'@'localhost'                                                       |
| GRANT EXECUTE, ALTER ROUTINE ON FUNCTION `Fun`.`hello` TO 'user1'@'localhost'                                |
+--------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> revoke create routine on Fun.* 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' |
| GRANT EXECUTE, ALTER ROUTINE ON FUNCTION `Fun`.`hello` TO 'user1'@'localhost'                                |
| GRANT EXECUTE, ALTER ROUTINE ON FUNCTION `Fun`.`format_bytes` TO 'user1'@'localhost'                         |
+--------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

Drop

如果要移除 user1 自訂的 SQL 函數,需使用 drop 來移除.

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 5.5.56-MariaDB MariaDB Server

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 Fun;
Database changed

MariaDB [Fun]> DROP FUNCTION hello;
Query OK, 0 rows affected (0.00 sec)

MariaDB [Fun]> SHOW FUNCTION STATUS;
Empty set (0.00 sec)
沒有解決問題,試試搜尋本站其他內容

發表迴響

你的電子郵件位址並不會被公開。 必要欄位標記為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料