MySQL – Stored Procedure Requiring SUPER Permission

MySQLpermissionsstored-procedures

I'm trying to figure out a "safe" way of allowing a non-SUPER user to execute a specific command requiring SUPER privileges, i.e. SET SESSION binlog_format = 'MIXED'. I don't want to grant the user SUPER privileges for security reasons. Can this be accomplished using a stored procedure or any other method?

Best Answer

You can have stored procedures execute as the definer instead of the invoker:

CREATE DEFINER = 'admin'@'localhost' PROCEDURE p1()
SQL SECURITY DEFINER
BEGIN
  UPDATE t1 SET counter = counter + 1;
END;

See: Access Control for Stored Programs and Views

Related Solutions

Grant permission to execute stored procedure without granting access to underlying table – Oracle

As long as you have your database organized in such a way that there is an account that is the owner of the tables and other objects, and your application connects using a separate account you can do what you want.

If you have tables and procedures organized in separate owners, you fist have to grant the required tables to the procedure owner (directly granted). Next you can grant the execute to a role or a user.

As soon as you allow connections to an object owner your security is broken since in that case you can always use all the objects of the owner (you are owner so you can use it).

Db2 – Adding group rights in db2 for a stored procedure

I ran into a similar problem and while this wasn't what happened here, this answer may help others running into this.

Make sure the OS group mygroup is all lowercase. DB2 on AIX does not recognize uppercase letters in the group name and will fail to validate a user belongs to a group if that group name has uppercase letters. eg:

# id useraccounthere
uid=xx(useraccounthere) gid=yyy(mygroup) <- this works

# id useraccounthere
uid=xx(useraccounthere) gid=yyy(MyGroup) <- this does not work

This is documented here: DB2 UDB Security

Best Answer

Related Solutions

Grant permission to execute stored procedure without granting access to underlying table – Oracle

Db2 – Adding group rights in db2 for a stored procedure

Related Question