Home / Questions / 563343
Accepted
julio
Asked: 2022-10-21 07:25:27 +0800 CST 2022-10-21 07:25:27 +0800 CST

MySQL grant select bug?

  • 772

I am preparing a common schemao databasethat contains tables in common use across multiple projects. All MySQL users must have permission to run ONLY selecton those tables. For that I do:

drop database if exists common_schema;

CREATE DATABASE common_schema CHARACTER SET utf8 COLLATE utf8_general_ci;
grant select on common_schema.* to ""@"";
flush privileges;
use common_schema;

set foreign_key_checks = 0;

-- creación_de_tablas
-- inserts
-- foreign_keys

set foreign_key_checks = 1;

The entire operation is executed with a bash script that is as follows:

cat *.sql  > sql_completo.sql1
mv sql_completo.sql1 sql_completo.sql
mysql -u root -p < sql_completo.sql
rm sql_completo.sql

This script concatenates all the SQL files and loads it into MySQL. The problem is that any user can insert, modify or delete records from common_schema, which it shouldn't be.

I am using MySQL 5.6.51.

Thanks in advance!

mysql

2 Answers

  1. Two things:

    • The command flush privileges is unnecessary in recent versions of MySQL if you do things correctly. It is a legacy of versions 3.x when you had to refresh everything when modifying the metadata tables. Now, if you do a GRANT, the permissions take effect immediately (if you did an INSERT to the permissions tables, then you have to do the flush). So there is no bug in the command because you are not doing anything new by running it.
    • In order not to deal with the default configuration of a new database (which does not have the option to define an owner or something similar), you might as well add a REVOKE ALL ON common_schema FROM 'usuario_pepito'@'%';to restrict a user. Then you give the SELECT with GRANT. No flush.
    • 1
  2. Best Answer

    I found a way to do it.

    Although permissions must be explicitly defined, grant select on common_schema.* to ''@'%';it gave all permissions to all users.

    I remind you: the idea was a schemacommon one with permission of select(only) all users, without sacrificing root/admin.

    First you have to revoke the permissions, and then assign the necessary ones:

    revoke all on common_schema.* from ''@'%';
grant select on common_schema.* to ''@'%';

    In this way, all the permits that should not have been awarded but were, are revoked, and then grantthe necessary permits are awarded.

    And so, little friends, we can have a common schema that everyone can consult but only root can modify.

    until next time!

    • 0