{"id":9146,"date":"2019-09-29T18:09:00","date_gmt":"2019-09-30T01:09:00","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=9146"},"modified":"2023-12-05T20:29:25","modified_gmt":"2023-12-06T03:29:25","slug":"mysql-stored-object-access-control","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/mysql-stored-object-access-control\/","title":{"rendered":"MySQL Stored Object Access Control"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about the stored object access control in MySQL.<\/p>\n\n\n\n

In MySQL, stored programs including stored procedures stored functions<\/a>, triggers<\/a>, and events<\/a> execute within a security context which determines their privileges.<\/p>\n\n\n\n

MySQL uses DEFINER<\/code> and SQL SECURITY<\/code> characteristics to control these privileges.<\/p>\n\n\n\n

The DEFINER attribute<\/h2>\n\n\n\n

When you define a stored routine such as a stored procedure or function, you can optionally specify the DEFINER<\/code> attribute, which is the name of a MySQL account:<\/p>\n\n\n

CREATE<\/span> [DEFINER=user<\/span>] PROCEDURE<\/span> spName(parameter_list)\n...\n\nCREATE<\/span> [DEFINER=user<\/span>] FUNCTION<\/span> sfName()\n...\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you skip the DEFINER<\/code> attribute, the default is the current user account.<\/p>\n\n\n\n
You can specify any account in the DEFINER<\/code> attribute if you have the SUPER<\/code> or SET_USER_ID<\/code> privilege. If you specify a user account that does not exist, MySQL will issue a warning.<\/p>\n\n\n\n
As of MySQL 8.0.16, you must have the SYSTEM_USER<\/code> privilege to set the DEFINER<\/code> attribute for a stored object to a user account that has the SYSTEM_USER<\/code> privilege.<\/p>\n\n\n\n
The SQL SECURITY characteristic<\/h2>\n\n\n\n
Stored routines (stored procedures and functions) and views can include an SQL SECURITY<\/code> clause with a value of DEFINER<\/code> or INVOKER<\/code>:<\/p>\n\n\n
CREATE<\/span> [DEFINER=user<\/span>] PROCEDURE<\/span> spName(parameter_list)\nSQL<\/span> SECURITY<\/span> [DEFINER | INVOKER]\n...\n\nCREATE<\/span> [DEFINER=user<\/span>] FUNCTION<\/span> sfName(parameter_list)\nSQL<\/span> SECURITY<\/span> [DEFINER | INVOKER]\n...\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL SECURITY DEFINER<\/h3>\n\n\n\n
When you use the SQL SECURITY DEFINER<\/code> for a stored object, it will execute in a definer security context with the privilege of the user specified by the DEFINER<\/code> attribute.<\/p>\n\n\n\n
Note that the user that invokes the stored object (or invoker) may not have the same privilege as the definer.<\/p>\n\n\n\n
In case the invoker has the least privilege and the definer has the most privileges, the invoker can perform operations above its privilege within the stored object.<\/p>\n\n\n\n
SQL SECURITY INVOKER<\/h3>\n\n\n\n
If you use the SQL SECURITY INVOKER<\/code> for a stored routine or view, it will operate within the privileges of the invoker.<\/p>\n\n\n\n
The DEFINER<\/code> attribute has no effect during object execution.<\/p>\n\n\n\n
Stored object access control example<\/h2>\n\n\n\n
First, create a new database<\/a> called testdb<\/code>:<\/p>\n\n\n
CREATE<\/span> DATABASE<\/span> testdb;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
USE<\/span> testdb;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
CREATE<\/span> TABLE<\/span> messages (\n    id<\/span> INT<\/span> AUTO_INCREMENT,\n    message VARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>,\n    PRIMARY KEY<\/span> (id<\/span>)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
DELIMITER $$\n\nCREATE<\/span> DEFINER = root@localhost PROCEDURE<\/span> InsertMessage( \n    msg VARCHAR<\/span>(100<\/span>)\n)\nSQL<\/span> SECURITY<\/span> DEFINER\nBEGIN<\/span>\n    INSERT<\/span> INTO<\/span> messages(message)\n    VALUES<\/span>(msg);\nEND<\/span>$$\n\nDELIMITER ;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this stored procedure, the definer is root@localhost<\/code> that is the superuser which has all privileges.<\/p>\n\n\n\n
The SQL Security is set to the definer. It means that any user account that calls this stored procedure will execute with all privileges of the definer i.e., root@localhost<\/code> user account.<\/p>\n\n\n\n
Fifth, create a new user named dev@localhost<\/code>:<\/p>\n\n\n
CREATE<\/span> USER<\/span> dev@localhost \nIDENTIFIED<\/span> BY<\/span> 'Abcd1234'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Sixth, grant the EXECUTE<\/code> privilege to dev@localhost<\/code> so that it can execute any stored procedure in the testdb<\/code> database:<\/p>\n\n\n
GRANT<\/span> EXECUTE<\/span> ON<\/span> testdb.* \nTO<\/span> dev@localhost;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Seventh, use the dev@localhost<\/code> to log in to the MySQL Server:<\/p>\n\n\n
mysql -u dev@localhost -p<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Eight, use the SHOW DATABASES<\/code> to display the database that dev@localhost<\/code> can access:<\/p>\n\n\n
mysql> show<\/span> databases<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the list:<\/p>\n\n\n
+--------------------+<\/span>\n| Database           |\n+--------------------+<\/span>\n| information_schema |\n| testdb             |\n+--------------------+<\/span>\n2 rows in set<\/span> (0.00<\/span> sec)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Ninth, select the testdb<\/code> database:<\/p>\n\n\n
mysql> use<\/span> testdb;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Tenth, call the InsertMessage<\/code> procedure to insert a row into the messages<\/code> table:<\/p>\n\n\n
mysql> call<\/span> InsertMessage('Hello World'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This is the output:<\/p>\n\n\n
Query OK, 1 row affected (0.01 sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Even though dev@localhost<\/code> does not have any privilege on the messages<\/code> table, it can insert a new row into that table successfully via the stored procedure because the stored procedure executes in the security context of the root@localhost<\/code> user account.<\/p>\n\n\n\n
Eleventh, go to the root’s session and create a stored procedure that updates the messages<\/code> table:<\/p>\n\n\n
DELIMITER $$\n\nCREATE<\/span> DEFINER=root@localhost \nPROCEDURE<\/span> UpdateMessage( \n    msgId INT<\/span>,\n    msg VARCHAR<\/span>(100<\/span>)\n)\nSQL<\/span> SECURITY<\/span> INVOKER\nBEGIN<\/span>\n    UPDATE<\/span> messages\n    SET<\/span> message = msg\n    WHERE<\/span> id<\/span> = msgId;\nEND<\/span>$$\n\nDELIMITER ;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The UpdateMessage<\/code> has the security context of INVOKER<\/code> who calls this stored procedure.<\/p>\n\n\n\n
Twelfth, go to the dev@localhost<\/code>‘s session and call the UpdateMessage()<\/code> stored procedure:<\/p>\n\n\n
mysql> call<\/span> UpdateMessage(1<\/span>,'Good Bye'<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This time the UpdateMessage()<\/code> stored procedure executes with the privileges of the caller which is dev@localhost<\/code>.<\/p>\n\n\n\n
Because dev@localhost<\/code> does not have any privileges on the messages<\/code> table, MySQL issues an error and rejects the update:<\/p>\n\n\n
ERROR 1142 (42000): UPDATE<\/span> command denied to<\/span> user<\/span> 'dev'<\/span>@'localhost'<\/span> for<\/span> table<\/span> 'messages'<\/span>\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this tutorial, you have learned about the MySQL stored object access control.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful? <\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
Fourth, create a stored procedure<\/a> that inserts a new row<\/a> into the messages<\/code> table:<\/p>\n\n\n
Third, create a new table<\/a> called messages<\/code>:<\/p>\n\n\n
Second, select the database<\/a> testdb<\/code> to work with:<\/p>\n\n\n