Mysql Question posted in
The official MySQL documentation can be found here.

I need to give users to MySQL Views and Stored Routines, but not the underlying tables

I have a database with multiple schemas. One of these schemas contains some data that needs to remain hidden from the other schemas while allowing limited access to other data. The intent is to only make the data accessible via Views and Stored Routines.
I’m using MySQL 5.7.33 on an Ubuntu server and MySQL Workbench 8.0 on Windows.
My question is, how do I set up the schema to make views, etc. accessible, but not the tables.
I’ve given users the following permissions on the restricted schema.
User Permissions
And the CREATE statement for the view begins: 

CREATE 
ALGORITHM = UNDEFINED 
DEFINER = `DBA`@`%` 
SQL SECURITY DEFINER
VIEW `CommonData`.`VW_SystemProperty` AS ...

When I run a select on the view I get the error:
Error Code: 1142. SELECT command denied to user ” for table ‘VW_SystemProperty’
What am I missing?

Tags:

2

Answers


  1. 0

    You can grant to all procedures or all procedures in a given schema this way:

    mysql> grant execute on test.* to 'root'@'localhost';
Query OK, 0 rows affected (0.01 sec)

    Strangely, the optional PROCEDURE keyword for the object type does not support wildcards in the procedure name.

    mysql> grant execute on procedure test.* to 'root'@'localhost';
ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the manual to see which privileges can be used

    But you can use this keyword if you grant to a specific procedure by name.

    mysql> grant execute on procedure test.foo to 'root'@localhost;
Query OK, 0 rows affected (0.00 sec)

    The only wildcard for table names or view names is *. You can’t use other wildcards like % or _ in table names or view names.

    In MySQL 5.x, you could use wildcards in schema names, but this is now deprecated, because it’s a potential security weakness.

    Login or Signup to reply.
  2. 0

    Although you cannot grant privileges using wildcards in the procedure or view names, you can use the following script to generate the GRANT statements.
    This is just a technique to save some effort and avoid errors.

    select
    CONCAT('GRANT SELECT ON `CommonData`.`', t.TABLE_NAME, '` to user;') as GrantStatement
from
    INFORMATION_SCHEMA.TABLES t
where
    t.TABLE_TYPE = 'VIEW'
    and t.TABLE_SCHEMA = 'CommonData'
    AND t.TABLE_NAME like 'VW[_]%'

    Once generated, the statements will need to be run manually.

    Of course, you can add/remove restrictions in the WHERE clause according to your requirements.

    A similar approach can be followed for stored procedures:

    select
    CONCAT('GRANT EXECUTE ON `CommonData`.`', r.SPECIFIC_NAME, '` to user;') as GrantStatement
from
    INFORMATION_SCHEMA.ROUTINES r
where
    r.ROUTINE_CATALOG = 'CommonData'
    and r.ROUTINE_NAME like 'usp%'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search