Mysql – In what order MySQL permissions are applied on database

MySQLpermissions

I want to understand the priority of executing MySQL grants privileges.

For e.g.:

I want to grant two different types of privileges on a single db Demo.

1.GRANT SELECT ON Demo.table1 TO abc@123;

2.GRANT ALL PRIVILEGES ON Demo.* TO abc@123;

As per my assumption, no 2 grant will overwrite the limitations of no 1 grant, therefore, ends up giving access to all tables including table1 to abc user.

And if I want to give SELECT privilege for table1 then I should change the order of executing permissions so that they don't get overwritten by *.

I'm not sure if this is how MySQL manages such permissions.
Anyone with a different opinion?

Thanks in advance.

Best Answer

According to MySQL 5.7 Documentation under Privileges Provided by MySQL

The privileges granted to a MySQL account determine which operations the account can perform. MySQL privileges differ in the contexts in which they apply and at different levels of operation:

Administrative privileges enable users to manage operation of the MySQL server. These privileges are global because they are not specific to a particular database.

Database privileges apply to a database and to all objects within it. These privileges can be granted for specific databases, or globally so that they apply to all databases.

Privileges for database objects such as tables, indexes, views, and stored routines can be granted for specific objects within a database, for all objects of a given type within a database (for example, all tables in a database), or globally for all objects of a given type in all databases.

From this, the order is:

Global Privileges
Database Privileges
Table Privileges

The tables that drive the order are:

mysql.user
mysql.db
mysql.tables_priv
mysql.columns_priv

With regard to an identical username @ host, please note what pages 486,487 state about mysql's authentication algorithm from MySQL 5.0 Certification Study Guide

enter image description here

There are two stages of client access control:

In the first stage, a client attempts to connect and the server either accepts or rejects the connection. For the attempt to succeed, some entry in the user table must match the host from which the client connects, the username, and the password.

In the second stage (which occurs only if a client has already connected sucessfully), the server checks every query it receives from the client to see whether the client has sufficient privileges to execute it.

The server matches a client against entries in the grant tables based on the host from which the client connects and the user the client provides. However, it's possible for more than one record to match:

Host values in grant tables may be specified as patterns contains wildcard values. If a grant table contains entries from myhost.example.com, %.example.com, %.com, and %, all of them match a client who connects from myhost.example.com.

Patterns are not allowed for the User values in grant table entries, but a username may be given as an empty string to specify an anonymous user. The empty string matches any username and thus effectively acts as a wildcard.

When the Host and the User values in more than one user table record match a client, the server must decide which one to use. It does this by sorting records with the most specific Host and User column values first, and choosing the matching record that occurs first in the sorted list, Sorting take place as follows:

In the Host Column, literal values such as localhost, 127.0.0.1, and myhost.example.com sort ahead of values such as %.example.com that have pattern characters in them. Pattern values are sorted according to how specific they are. For example, %.example.com is more specific than %.com, which is more specific than %.

In the User column, non-blank usernames sort ahead of blank usernames. That is, non-anonymous users sort ahead of anonymous users.

The server performs this sorting when it starts. It reads the grant tables into memory, sorts them, and uses the in-memory copies for access control.

When you look at these two perspectives, mysqld should always go top down when evaluating grants. Keep in mind that

GRANT SELECT ON Demo.table1 TO abc@123; is stored in mysql.tables_priv
GRANT ALL PRIVILEGES ON Demo.* TO abc@123; is stored in mysql.db
For more information, please see my older post Unable to remove permission for mysql.user

AND THE OSCAR GOES TO ...

GRANT ALL PRIVILEGES ON Demo.* TO abc@123;

Related Solutions

Mysql – Grant table privileges to a user connecting from any host

When you execute GRANT SELECT ON store.catalog TO 'wordpress'@'%';, mysqld wants to insert a row into the grant table mysql.tables_priv. Here is mysql.tables_priv:

mysql> show create table mysql.tables_priv\G
*************************** 1. row ***************************
       Table: tables_priv
Create Table: CREATE TABLE `tables_priv` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Grantor` char(77) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Table_priv` set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
  PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`),
  KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Table privileges'
1 row in set (0.00 sec)

mysql>

Since you want to insert a row into mysql.table_priv where user='wordpress' and host='%', there has to exist a row in mysql.user where user='wordpress' and host='%'.

You also mentioned that you are using MySQL Workbench. You must be using 'root'@'localhost'. That would usually have all rights and a password.

If you want to just allow anonymous SELECT against that table, first run this:

GRANT USAGE ON *.* TO 'wordpress'@'%';

This will place wordpress@'%' into mysql.user. Afterwards, GRANT SELECT ON store.catalog TO 'wordpress'@'%' should run just fine.

You will have to see what other wordpress entries are in mysql.user. This should show what SQL GRANT commands you need:

SELECT CONCAT('GRANT SELECT ON store.catalog TO ',userhost,';') GrantCommand
FROM
(
    SELECT CONCAT('''',user,'''@''',host,'''') userhost
    FROM mysql.user WHERE user='wordpress'
) A;

Mysql – How to forcibly create/modify MySQL user grants, without restarting the database service

You could copy just the the "mysql" database away to another location and start another daemon on it. Get the SHA1 or DES hash stored in the user table for a user with SUPER privs (usually root, but sometimes renamed for security through obscurity).

Then connect to the mysql using a modified version of the client library that makes mysql_real_connect() support using a pre-hashed password instead of having it take the password plaintext. This should be trivial.

You won't ever know the actual password, but with the hash and a modified client you'll be able to log in anyway.

You can then make any modifications to permissions, create necessary schema and tables and flush privileges.

I'll leave the security implications of such practices up to you.

Best Answer

AND THE OSCAR GOES TO ...

Related Solutions

Mysql – Grant table privileges to a user connecting from any host

Mysql – How to forcibly create/modify MySQL user grants, without restarting the database service

Related Question