Provide only GRANT EXECUTE 'procedure' to a user (No select or insert permissions)

Question

I was wondering if it's possible to grant EXECUTE permissions to a user without granting SELECT, INSERT etc. permissions on the table that a procedure runs on?

Using it for a Logins table for a webapp. MySQL is running in a Docker container. SQL for creating procedures is copied across as part of the docker build process (when run, the sql is used in entrypoint.sh). Login_db is created when running the container (-e flag).

I'd like to remove the GRANT SELECT line from below so, no matter what happens, the webapp server can never run a SELECT query - such as doing SELECT * FROM logins.

CREATE USER 'logins'@'172.24.0.7' IDENTIFIED BY 'some-password';
GRANT SELECT, INSERT, UPDATE on logins_db.login TO 'logins'@'172.24.0.7';
GRANT EXECUTE ON PROCEDURE logins_db.sp_login16 TO 'logins'@'172.24.0.7';
FLUSH PRIVILEGES;

This doesn't solve it - as being the table owner would expose the same privileges:

Execute stored proc fails with GRANT EXECUTE because of table permissions

This might explain why I can't, but the table names are a bit odd to me (MySQL newbie - I'm under the impression that mysql.proc is a system table, so not sure if it applies):

How to grant execute on specific stored procedure to user

Could it be that root doesn't have SELECT privileges when creating the procedure and so the the logins user cannot run it? (Because Docker MySQL runs entrypoint.sh and then the environment variable)?

The procedure code is here (I know, not the most elegant) - could I GRANT and then REVOKE privileges for the logins user within this, considering the DEFINER is root ?

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_login16`(
IN p_email VARCHAR(120),
IN p_password VARCHAR(120))
BEGIN
SELECT user_id,user_password FROM login WHERE user_email = p_email;

score 3 · Answer 1 · answered Jan 15 '17 at 20:14

3

Yes, you can do this by using sql security definer while declaring the stored procedure:

The SQL SECURITY characteristic can be DEFINER or INVOKER to specify the security context; that is, whether the routine executes using the privileges of the account named in the routine DEFINER clause or the user who invokes it. This account must have permission to access the database with which the routine is associated. The default value is DEFINER. The user who invokes the routine must have the EXECUTE privilege for it, as must the DEFINER account if the routine executes in definer security context.

The DEFINER clause specifies the MySQL account to be used when checking access privileges at routine execution time for routines that have the SQL SECURITY DEFINER characteristic.

If a user value is given for the DEFINER clause, it should be a MySQL account specified as 'user_name'@'host_name', CURRENT_USER, or CURRENT_USER(). The default DEFINER value is the user who executes the CREATE PROCEDURE or CREATE FUNCTION statement. This is the same as specifying DEFINER = CURRENT_USER explicitly.

To sum it up: the user in the definer clause has to have the select / insert privileges to the underlying table in this ase, while the user who executes the stored proc must have execute privileges to the stored proc.

answered Jan 15 '17 at 20:14

Shadow

33,525
10
51
64

Thanks for the answer. Correct me if I'm wrong, but > Thus, the procedure succeeds or fails depending on whether the invoker has the EXECUTE privilege for it and the SELECT privilege for the mysql.user table. would suggest that the logins user would need to have `SELECT` privileges on the login table? – dijksterhuis Jan 15 '17 at 20:21
I'm sorry, but I do not rwally understand your summary. One thing is sure, though, non-admin users should not have any access to any table in the mysql database (the database named mysql). Not even select. – Shadow Jan 15 '17 at 23:23
So further down in the page you linked to, there's two examples. The thing I quoted (thus ........ MySQL.user table) is from just after the second example. It seems to suggests that a user, with execute privileges, must have select privileges on the table that the stored procedure will run on? – dijksterhuis Jan 16 '17 at 00:01
No, the documentation after the sample explicitly says "The procedure succeeds or fails depending on whether invoker has the EXECUTE privilege for it and 'admin'@'localhost' has the SELECT privilege for the mysql.user table." The admin account is the definer in the example. – Shadow Jan 16 '17 at 00:07
Ah! So the first example of the two is the relevant one. Gotcha! Thanks! – dijksterhuis Jan 16 '17 at 01:43
Hang on, the first example is what I have already. Root is my definer (and as root has select privileges on the table) but the logins user cannot execute the procedure without select permissions being granted on the login table. Is using root as the definer causing the problem? Do I need to set up a non-root user (i.e. Admin@localhost) to define the procedure? – dijksterhuis Jan 16 '17 at 02:12
As long as the definer has access to the underlying table, you should be tine. If you are not, then you are not describing the issue completely. – Shadow Jan 16 '17 at 02:29
MySQL is run in a docker container, logins db and stored procedures created by root on start up. Logins db create passed as an environment variable, others are part of the build. The only thing I can think of is that the stored procedures are defined before the login_db when the container runs. So the root user doesn't have select privileges when they are defined? But that shouldn't matter as root will have select privileges once the db is created... – dijksterhuis Jan 16 '17 at 02:38
Your stored procedure in the question will fail with syntax error to begin with. So, you are definitely not describing your issue completely. – Shadow Jan 16 '17 at 02:44
I left out delimiter $$ / end $$ for ease of viewing. Doesn't fail at all. Been working with it for the past week. – dijksterhuis Jan 16 '17 at 02:46

dijksterhuis · Accepted Answer · 2021-09-19T14:35:31.207

Added a new user ADMIN@localhost with SELECT, INSERT and UPDATE privileges. ADMIN then became the DEFINER for all the procedures, with 'logins'@'172.24.0.7' only being granted EXECUTE permissions. Runs perfectly now!

Apparently you can't use root in the way I was trying to. Kudos to @Shadow for pointing me in the right direction.

Setting up the admin user:

CREATE USER 'admin'@'localhost' IDENTIFIED BY '<password>';
GRANT SELECT, INSERT, UPDATE, DELETE ON db.table_name TO 'admin'@'localhost';
GRANT ALTER ROUTINE, CREATE ROUTINE, EXECUTE ON *.* TO 'admin'@'localhost'; 
FLUSH PRIVILEGES;

Defining a stored procedure that creates a entry using the limited admin user

DELIMITER $$
CREATE DEFINER=`admin`@`localhost` PROCEDURE `sp_createTableEntry`(

    IN value_one VARCHAR(120),
    IN value_two VARCHAR(200)
)
BEGIN
    IF ( select exists (select 1 from table_name where column_one = value_one) ) THEN
     
        select 'Column One Exists !!';
     
    ELSE
     
        insert into table_name
        (  
            column_one,
            column_two
        )
        values
        (
            value_one,
            value_two
        );
    END IF ;
END $$
DELIMITER ;

@Fusion replaced link with actual sql. Apologies, set the repo to private a while back. — dijksterhuis, Sep 19 '21 at 14:36

Provide only GRANT EXECUTE 'procedure' to a user (No select or insert permissions)

2 Answers2