Permissions checks for MySQL stored procedures, functions, triggers, and views

When stored procedures, functions, triggers, and views are created, not only by the creator to execute, but also by other users, in other words, the performer may not be the creator itself, so how does MySQL do permissions checks when executing the stored procedure?

By default, MySQL checks the creator's permissions. Suppose user A creates a stored procedure p () to access the table T and assigns execute permission to B, even if User B does not have access to the table T, and can access the table t by executing stored procedure p ().

Let's look at an example:

First, we create a table test.t and two user a,b and give the user a permission

root@ (none) 05:39:45>create table portal.t as SELECT * from Mysql.user;  
Query OK, rows affected (0.16 sec)  
records:25  duplicates:0 warnings:0  root@  
      
(none) 05:39:55> Create user A identified by ' a ';  
Query OK, 0 rows affected (0.02 sec)  
      
root@ (none) 05:40:51>create User B identified by ' B ';  
Query OK, 0 rows Affected (0.00 sec)  
      
root@ (none) 05:40:59>grant all privileges in portal.* to A;  
Query OK, 0 rows affected (0.01 sec)

Next, create the stored procedure p () with User A:

DELIMITER $$ use  
portal$$  
CREATE PROCEDURE ' P ' () to  
BEGIN
  SELECT COUNT (*) from portal.t;  
end$$  
DELIMITER;

and assigns the permission to execute the stored procedure to User B:

root@ (None) 05:54:28>grant execute on procedure portal.p to B;  
Query OK, 0 rows Affected (0.00 sec)

At this point, the access rights of the T table can be obtained by executing stored procedures after User B is connected:

b@ (None) 05:58:20>call portal.p ();  
+----------+  
| COUNT (*) |  
+----------+  
|       |  
+----------+  
1 row in Set (0.00 sec)  
      
Query OK, 0 rows Affected (0.00 sec)

However, if direct access will appear with permission errors:

b@ (None) 05:58:40>select count (*) from portal.t;  
ERROR 1142 (42000): SELECT command denied to user ' B ' @ ' 192.168.1.15 ' for table ' t '

MySQL has a certain set of reasons, but it also poses a security risk: for example, if a user creates a stored procedure to access sensitive data, all users who can invoke the stored procedure can access sensitive data.

If you do not want to use MySQL's default settings, you can specify the definition by using the Definer = account phrase in the CREATE statement when you define the stored program and view, so that when you execute the stored program and view, you will check the permissions of the Definer, not the creator.

For example, when you create a stored procedure with root, by default, the performer obtains root permissions when you execute the stored procedure, but when you add Definer = A, the performer gains only a permission.

But Definer still failed to completely solve the security risks mentioned above, don't worry, MySQL also provides the SQL Safety option to control permissions, it has two values:

1) Definer: Execute with the permissions of the defined person (default)

2) Invoker: Execute with the authority of the caller

If you do not want to save the program or try to execute more permissions than the caller, set the SQL security invoker.

For example, the following attempts to access Mysql.user and set the SQL Security Invoker option so that if the caller does not have access to the mysql.user, it cannot pass the permission check.

Create SQL Security Invoker View v as  
  select * from Mysql.user;

NOTE: Because triggers and events are called by the system and do not have the concept of the caller, they do not have the SQL security option.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/