0

I would like to audit my database to check which users have been granted individual EXECUTE permission to which procedure.

I would like to use this information to be able to save GRANTs when changing (DROP+CREATE) the definition of some procedure in order to restore them after.

How can I query the INFORMATION_SCHEMA or MYSQL schemas to retrieve that information?

Update I'm interested in a solution working on MySQL 5.6+.

For granular security, users of our datase are not granted an EXECUTE permission on the whole schema but on specific stored procedures. Answers on schema-level permissions are irrelevant.

0

This script executes and shows all privileges associated for account.

SHOW GRANTS FOR 'antonio'@'antonio@gmail.com';

  • 2
    This is not a good answer to the question. Assume there are 2,000 users are you going to do this 2,000 times? – James Jenkins Aug 1 at 18:08
0

Digging in the mysql schema (USE mysql; SHOW TABLES;) I found the procs_priv table that contains the information I need:

SELECT Routine_type, Routine_name, User, Host
FROM mysql.procs_priv
WHERE Db = DATABASE()
AND Proc_priv = 'Execute';
  • I believe that stores the results of GRANT EXECUTE, ... ON FUNCTION (or proc) dbname.functionname TO user@host; -- Note that that is one user at a time. – Rick James Aug 6 at 17:10
  • @RickJames Yes, that's the point of my question. – dolmen Aug 7 at 16:35
0

What version of MySQL?

(At least) before 8.0, this will list users that have "Execute" privilege in all databases:

SELECT user, host FROM mysql.user WHERE Execute_priv = 'Y';

This will say which ones have permission in a particular my_db:

SELECT user, host FROM mysql.user WHERE Execute_priv = 'Y' AND db = "my_db";

(The following probably works in all recent versions:)

But also keep in mind the distinction between these:

SQL SECURITY INVOKER
SQL SECURITY DEFINER

See also:

SELECT ROUTINE_SCHEMA,  -- (database)
       ROUTINE_NAME,
       ROUTINE_TYPE,    -- PROCEDURE or FUNCTION
       DEFINER,         -- eg, "root@localhost"
       SECURITY_TYPE    -- INVOKER or DEFINER
    FROM information_schema.ROUTINES;
  • This doesn't answer my question. My question is about specific right granted on specific stored procedures, not about general permission on a whole schema. – dolmen Aug 6 at 10:52
  • @dolmen - There seem to be two ways to GRANT execute permission to a user -- My Answer covers the GRANT EXECUTE ON db.* TO user@host -- without specifying specific functions/procs. – Rick James Aug 6 at 17:12
  • Exactly. That's why it doesn't answer my question. – dolmen Aug 7 at 16:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.