-1

I have a set of databases under a unique db user which can do select statement over every table of all databases. But now I can't define a store procedure that can do a select statement over other database table. For example I could have a 'joe' user and I have four database 'joe_database', 'mirko_database', 'carlo_database' and 'william_database'. joe owns only joe_database but he can select over the others.

For instance joe can:

SELECT * FROM mirko_database;

But if joe defines:

CREATE PROCEDURE call_select(IN db_name VARCHAR(50))
BEGIN
    SET @query_base = 'SELECT * FROM $_database_$.any_table;'
    SET @query_base = REPLACE(@query_base, '$_database_$', db_name);

    PREPARE _lquery FROM @query_base;
    EXECUTE _lquery;
    DEALLOCATE PREPARE _lquery;
END;

And joe does:

CALL call_select('mirko_database');

He gets this error:

SELECT command denied to user 'joe'@'localhost' for table 'any_table'

How can I grant privileges to joe in order to enable it to call store procedures over all databases?

Thanks

willypuzzle
  • 321
  • 1
  • 3
  • 10

1 Answers1

0

Joe needs the EXECUTE privilege in order to execute stored routines.

GRANT EXECUTE ON PROCEDURE *.call_select TO 'joe'@'localhost';
Lars Beck
  • 3,446
  • 2
  • 22
  • 23