[MySQL]CREATE FUNCTION的權限

        話說同事跟我說他無法在MySQL裡建立Function，出現如下錯誤訊息

You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable) 

        我想說已經給他該DB層級ALL的權限了，為何還要給SUPER這種伺服器層級的權限呢？

        一查13.1.15 CREATE PROCEDURE and CREATE FUNCTION Syntax一節，還真的要給SUPER權限

CREATE PROCEDURE and CREATE FUNCTION require the CREATE ROUTINE privilege. They might also require theSUPER privilege, depending on the DEFINER value, as described later in this section. If binary logging is enabled,CREATE FUNCTION might require the SUPER privilege, as described in Section 20.7, “Binary Logging of Stored Programs”.

        後來又仔細看20.7 Binary Logging of Stored Programs一節，是可以設定log_bin_trust_function_creators選項來讓CREATE ROUTINE權限可以建立Function喔，試了一下，果然OK，權限就不用給到那麼大了

root@(none) 02:52:37>create database test;

Query OK, 1 row affected (0.00 sec)

root@(none) 02:52:50>GRANT CREATE ROUTINE, ALTER ROUTINE ON test.* TO 'test'@'localhost' IDENTIFIED BY 'test';

Query OK, 0 rows affected (0.00 sec)

root@(none) 03:08:17>FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

root@(none) 02:53:28>exit

[root@Centos tmp]# mysql -utest -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 215

Server version: 5.5.37-r5436-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

test@(none) 03:10:36>use test;

Database changed

test@test 03:10:39>DELIMITER $$

test@test 03:10:50>CREATE FUNCTION hello_world()

    ->   RETURNS TEXT

    ->   LANGUAGE SQL

    -> BEGIN

    ->   RETURN 'Hello World';

    -> END;

    -> $$

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

test@test 03:11:57>exit

Bye

[root@Centos tmp]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 218

Server version: 5.5.37-r5436-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@(none) 03:13:02>SET GLOBAL log_bin_trust_function_creators = 1;

Query OK, 0 rows affected (0.00 sec)

root@(none) 03:13:13>exit

Bye

[root@Centos tmp]# mysql -utest -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 219

Server version: 5.5.37-r5436-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

test@(none) 03:15:09>use test;

Database changed

test@test 03:15:22>DELIMITER $$

test@test 03:15:24>CREATE FUNCTION hello_world()

    ->   RETURNS TEXT

    ->   LANGUAGE SQL

    -> BEGIN

    ->   RETURN 'Hello World';

    -> END;

    -> $$

Query OK, 0 rows affected (0.00 sec)

test@test 03:15:24>DELIMITER ;

test@test 03:15:25>select hello_world();

+---------------+

| hello_world() |

+---------------+

| Hello World   |

+---------------+

1 row in set (0.00 sec)

test@test 03:16:03>exit

Bye