I am tormented by the problem of obtaining information about whether the user has rights to certain commands to certain tables. For example, there is a user user and he has SELECT rights in table1, as well as SELECT, DELETE rights in table2. How to determine if the user has rights to delete specifically for the table table2?

If you use the command:

SHOW GRANTS FOR user;

then all user privileges are displayed, which in turn is difficult to automatically process (more precisely, I cannot imagine how to choose DELETE from a changing output that will apply to table2 and not to be confused with other tables.)

If you use the command:

 SELECT * FROM information_schema.user_privileges;

then not all privileges are displayed (in my case, only one USAGE privilege is displayed),

If use command

 USE information_schema; SELECT TABLE_NAME, PRIVILEGES FROM COLUMNS;

then the privileges are displayed only those that match the list "select, insert, update, references" i.e. in my case DELETE is not here.

Is it really impossible to find out whether the user has certain rights?

    1 answer 1

    First, you need to understand that in MySQL, privileges can be granted to different objects

    1. On the entire server
    2. On some bases
    3. On some tables
    4. On some columns

    Request

     SHOW GRANTS FOR user;

    It will show you all granted privileges for this user.

     SELECT * FROM information_schema.user_privileges;

    show privileges granted to the whole server

    What gives the request

     SELECT TABLE_NAME, PRIVILEGES FROM information_schema.COLUMNS;

    I do not know. But user privileges by columns are listed in the column_privileges table (by the way, DELETE for a column cannot be determined - the column data cannot be deleted)

    And then there are table_privileges - privileges to the table and schema_privileges - privileges to the base.

    I think your privilege should be searched in table_privileges .

    In general, I would advise not to bother with this issue. And just try to do a certain action and check the error code.

    It is possible to check the DELETE privilege specifically with such a request.

     SELECT 1 FROM user_privileges usr WHERE usr.GRANTEE = :user AND usr.PRIVILEGE_TYPE = 'DELETE' UNION SELECT 1 FROM schema_privileges sc WHERE sc.GRANTEE = :user AND sc.PRIVILEGE_TYPE = 'DELETE' sc.TABLE_SCHEMA = 'my_db' UNION SELECT 1 FROM table_privileges tb WHERE tb.GRANTEE = :user AND tb.PRIVILEGE_TYPE = 'DELETE' AND tb.TABLE_SCHEMA = 'my_db' AND tb.TABLE_NAME = 'my_table'
    • I understand perfectly that privileges can be granted to different objects, hence the question of how to find out that a specific table has DELETE rights for a specific user. On account of your example - CHIM
    • @ CHIM I quoted a request - Anton Shchyrov
    • Thanks for the tip on "table_privileges" apparently there are exactly those values ​​that I need, but I was embarrassed that there were no root rights and another user with admin rights, but apparently they do not fit into this table. On account of your example, gives an error at the moment "usr.GRANTEE =: user AND" - CHIM
    • @ CHIM so instead :user need to substitute the name of your user - Anton Shchyrov
    • @ CHIM Root privileges will extend to the entire server, they will not be in user_privileges - Anton Shchyrov