測試環境為 CentOS 7 x86_64
使用者的權限可以區分為
- 登入權限,請參考 https://benjr.tw/97973
- 使用者存取 資料庫(DateBase)/資料表(Table)/資料欄(column)權限,請參考 https://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)
回傳值, CONTACT 函數可以將兩個或更多個字串連接在一起.
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)
備份與還原
備份 Procedures 與 Function 權限需透過下面的方式來完成.
[root@localhost ~]# mysqldump -u root -p mysql proc > mysql_proc.sql
還原 Procedures 與 Function 權限.
[root@localhost ~]# mysql -u root -p mysql < mysql_proc.sql Enter password:
沒有解決問題,試試搜尋本站其他內容