Description:
Create database and tables.
Create user(jim@'%').
Create stored functions in database.
Assign routine level privilege to users for stored functions.
Verify the grants using show grants statement.
Drop database and users.
Recreate user(jim@'%').
Verify grants for jim@'%';
We notice that privileges to stored functions exists on user jim@'%'
This does not happen in case of stored procedures.
CREATE DATABASE db1;
CREATE TABLE db1.t1(a INT, b CHAR(20));
INSERT INTO db1.t1 VALUES
(1,'TEST1'),(2,'TEST2'),(3,'TEST3'),(4,'TEST4');
CREATE USER 'jim'@'%';
DELIMITER ||
CREATE FUNCTION db1.f1() RETURNS INT
BEGIN
RETURN(SELECT COUNT(*) FROM db1.t1);
END;||
DELIMITER ;
GRANT EXECUTE ON FUNCTION db1.f1 TO jim@'%';
SHOW GRANTS FOR jim@'%';
SELECT * FROM mysql.procs_priv;
DROP DATABASE db1;
DROP USER jim@'%';
CREATE USER 'jim'@'%';
SHOW GRANTS FOR jim@'%';
SHOW FUNCTION STATUS;
The user jim@'%' shows execute privilege on stored functions
How to repeat:
mysql> CREATE DATABASE db1;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE db1.t1(a INT, b CHAR(20));
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO db1.t1 VALUES
-> (1,'TEST1'),(2,'TEST2'),(3,'TEST3'),(4,'TEST4');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> CREATE USER 'jim'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ||
mysql> CREATE FUNCTION db1.f1() RETURNS INT
-> BEGIN
-> RETURN(SELECT COUNT(*) FROM db1.t1);
-> END;||
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
mysql> GRANT EXECUTE ON FUNCTION db1.f1 TO jim@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR jim@'%';
+---------------------------------------------------+
| Grants for jim@% |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO 'jim'@'%' |
| GRANT EXECUTE ON FUNCTION `db1`.`f1` TO 'jim'@'%' |
+---------------------------------------------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM mysql.procs_priv;
+------+-----+------+--------------+--------------+----------------+-----------+---------------------+
| Host | Db | User | Routine_name | Routine_type | Grantor | Proc_priv | Timestamp |
+------+-----+------+--------------+--------------+----------------+-----------+---------------------+
| % | db1 | jim | f1 | FUNCTION | root@localhost | Execute | 2009-01-08 20:30:21 |
+------+-----+------+--------------+--------------+----------------+-----------+---------------------+
1 row in set (0.01 sec)
mysql> DROP DATABASE db1;
Query OK, 1 row affected (0.00 sec)
mysql> DROP USER jim@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER 'jim'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR jim@'%';
+---------------------------------------------------+
| Grants for jim@% |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO 'jim'@'%' |
| GRANT EXECUTE ON FUNCTION `db1`.`f1` TO 'jim'@'%' |
+---------------------------------------------------+
2 rows in set (0.00 sec)
mysql> SHOW FUNCTION STATUS;
Empty set (0.01 sec)
Ideally, privileges to users for stored functions should not be present when database is dropped and user is recreated.
Description: Create database and tables. Create user(jim@'%'). Create stored functions in database. Assign routine level privilege to users for stored functions. Verify the grants using show grants statement. Drop database and users. Recreate user(jim@'%'). Verify grants for jim@'%'; We notice that privileges to stored functions exists on user jim@'%' This does not happen in case of stored procedures. CREATE DATABASE db1; CREATE TABLE db1.t1(a INT, b CHAR(20)); INSERT INTO db1.t1 VALUES (1,'TEST1'),(2,'TEST2'),(3,'TEST3'),(4,'TEST4'); CREATE USER 'jim'@'%'; DELIMITER || CREATE FUNCTION db1.f1() RETURNS INT BEGIN RETURN(SELECT COUNT(*) FROM db1.t1); END;|| DELIMITER ; GRANT EXECUTE ON FUNCTION db1.f1 TO jim@'%'; SHOW GRANTS FOR jim@'%'; SELECT * FROM mysql.procs_priv; DROP DATABASE db1; DROP USER jim@'%'; CREATE USER 'jim'@'%'; SHOW GRANTS FOR jim@'%'; SHOW FUNCTION STATUS; The user jim@'%' shows execute privilege on stored functions How to repeat: mysql> CREATE DATABASE db1; Query OK, 1 row affected (0.00 sec) mysql> CREATE TABLE db1.t1(a INT, b CHAR(20)); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO db1.t1 VALUES -> (1,'TEST1'),(2,'TEST2'),(3,'TEST3'),(4,'TEST4'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> CREATE USER 'jim'@'%'; Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER || mysql> CREATE FUNCTION db1.f1() RETURNS INT -> BEGIN -> RETURN(SELECT COUNT(*) FROM db1.t1); -> END;|| Query OK, 0 rows affected (0.01 sec) mysql> DELIMITER ; mysql> GRANT EXECUTE ON FUNCTION db1.f1 TO jim@'%'; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS FOR jim@'%'; +---------------------------------------------------+ | Grants for jim@% | +---------------------------------------------------+ | GRANT USAGE ON *.* TO 'jim'@'%' | | GRANT EXECUTE ON FUNCTION `db1`.`f1` TO 'jim'@'%' | +---------------------------------------------------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM mysql.procs_priv; +------+-----+------+--------------+--------------+----------------+-----------+---------------------+ | Host | Db | User | Routine_name | Routine_type | Grantor | Proc_priv | Timestamp | +------+-----+------+--------------+--------------+----------------+-----------+---------------------+ | % | db1 | jim | f1 | FUNCTION | root@localhost | Execute | 2009-01-08 20:30:21 | +------+-----+------+--------------+--------------+----------------+-----------+---------------------+ 1 row in set (0.01 sec) mysql> DROP DATABASE db1; Query OK, 1 row affected (0.00 sec) mysql> DROP USER jim@'%'; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER 'jim'@'%'; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS FOR jim@'%'; +---------------------------------------------------+ | Grants for jim@% | +---------------------------------------------------+ | GRANT USAGE ON *.* TO 'jim'@'%' | | GRANT EXECUTE ON FUNCTION `db1`.`f1` TO 'jim'@'%' | +---------------------------------------------------+ 2 rows in set (0.00 sec) mysql> SHOW FUNCTION STATUS; Empty set (0.01 sec) Ideally, privileges to users for stored functions should not be present when database is dropped and user is recreated.