[MDEV-9767] CREATE USER/ROLE and REVOKE disagree Created: 2016-03-20  Updated: 2016-03-20  Resolved: 2016-03-20

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System
Affects Version/s: 10.1.11
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Igor Pashev Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

It might be odd to edit mysql tables directly, but I believe it is common to delete rows from mysql.user and flush privileges to make sure that a user or role does not exist. It seems that any rows in tables_priv or columns_priv remain and it makes impossible to recreate the user or role or revoke all privileges:

MariaDB [mysql]> create role foo;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [mysql]> grant select on `bob_live_sg`.`catalog_config_shoes` to foo;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [mysql]> show grants for foo;
 
+---------------------------------------------------------------+
 
| Grants for foo                                                |
 
+---------------------------------------------------------------+
 
| GRANT USAGE ON *.* TO 'foo'                                   |
 
| GRANT SELECT ON `bob_live_sg`.`catalog_config_shoes` TO 'foo' |
 
+---------------------------------------------------------------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [mysql]> delete from user where user='foo' and is_role='Y';
 
Query OK, 1 row affected (0.00 sec)
 
 
 
MariaDB [mysql]> flush privileges;
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
MariaDB [mysql]> show grants for foo;
 
ERROR 1141 (42000): There is no such grant defined for user 'foo' on host '%'
 
MariaDB [mysql]> delete from roles_mapping where role='foo';
 
Query OK, 1 row affected (0.00 sec)
 
 
 
MariaDB [mysql]> create role if not exists foo;
 
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
 
 
Note (Code 1975): Can't create role 'foo'; it already exists
 
MariaDB [mysql]> revoke all,grant option from foo;
 
ERROR 1269 (HY000): Can't revoke all privileges for one or more of the requested users
 
MariaDB [mysql]> 



MariaDB [mysql]> create user if not exists bar;  
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [mysql]> grant select on `bob_live_sg`.`catalog_config_shoes` to bar;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [mysql]> delete from user where user='bar' and is_role='N';
 
Query OK, 1 row affected (0.00 sec)
 
 
 
MariaDB [mysql]> flush privileges;
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
MariaDB [mysql]> select * from tables_priv where user='bar';
 
+------+-------------+------+----------------------+-------------------+---------------------+------------+-------------+
 
| Host | Db          | User | Table_name           | Grantor           | Timestamp           | Table_priv | Column_priv |
 
+------+-------------+------+----------------------+-------------------+---------------------+------------+-------------+
 
| %    | bob_live_sg | bar  | catalog_config_shoes | mariadb@localhost | 0000-00-00 00:00:00 | Select     |             |
 
+------+-------------+------+----------------------+-------------------+---------------------+------------+-------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [mysql]> create user if not exists bar;
 
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
 
 
Note (Code 1973): Can't create user 'bar'@'%'; it already exists
 
MariaDB [mysql]> revoke all,grant option from bar;
 
ERROR 1269 (HY000): Can't revoke all privileges for one or more of the requested users
 
MariaDB [mysql]> grant usage on *.* to bar;
 
ERROR 1133 (28000): Can't find any matching row in the user table



 Comments   
Comment by Sergei Golubchik [ 2016-03-20 ]

This is not a bug. We do not support manual editing of privilege tables. You do it on your own risk and it's your responsibility to leave privilege tables in the consistent state. Alternatively, you can use DROP USER and DROP ROLE that will do all the job for you.

Comment by Igor Pashev [ 2016-03-20 ]

That's fair. I'm just trying ways around MDEV-9691.

Comment by Sergei Golubchik [ 2016-03-20 ]

are you trying to delete and then create that user again? then try CREATE OR REPLACE USER

Comment by Igor Pashev [ 2016-03-20 ]

CREATE OR REPLACE has side effects - deleting mappings (for a role) or password (for a user).
I'd like to keep all mappings and passwords, but make sure users and roles have only explicitly listed privileges:

MariaDB [mysql]> select * from roles_mapping;
 
+-----------+-------------+-------------+--------------+
 
| Host      | User        | Role        | Admin_option |
 
+-----------+-------------+-------------+--------------+
 
| localhost | mariadb     | catalog_hk  | Y            |
 
| localhost | mariadb     | role1       | Y            |
 
| localhost | mariadb     | catalog_all | Y            |
 
| localhost | mariadb     | catalog_sg  | Y            |
 
|           | catalog_all | catalog_hk  | N            |
 
| localhost | mariadb     | role2       | Y            |
 
|           | role2       | role1       | N            |
 
+-----------+-------------+-------------+--------------+
 
7 rows in set (0.01 sec)
 
 
 
MariaDB [mysql]> create or replace role role1;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [mysql]> select * from roles_mapping;
 
+-----------+-------------+-------------+--------------+
 
| Host      | User        | Role        | Admin_option |
 
+-----------+-------------+-------------+--------------+
 
| localhost | mariadb     | catalog_hk  | Y            |
 
| localhost | mariadb     | catalog_all | Y            |
 
| localhost | mariadb     | catalog_sg  | Y            |
 
|           | catalog_all | catalog_hk  | N            |
 
| localhost | mariadb     | role2       | Y            |
 
| localhost | mariadb     | role1       | Y            |
 
+-----------+-------------+-------------+--------------+
 
MariaDB [mysql]> create user user1 identified by password '*AC5C3BDA823EECFF90B8381D554232C762A345B4';                                                                                    
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [mysql]> select user,password from user where user='user1';
 
+-------+-------------------------------------------+
 
| user  | password                                  |
 
+-------+-------------------------------------------+
 
| user1 | *AC5C3BDA823EECFF90B8381D554232C762A345B4 |
 
+-------+-------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [mysql]> create or replace user user1 ;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [mysql]> select user,password from user where user='user1';
 
+-------+----------+
 
| user  | password |
 
+-------+----------+
 
| user1 |          |
 
+-------+----------+
 
1 row in set (0.00 sec)

Comment by Sergei Golubchik [ 2016-03-20 ]

Of course CREATE OR REPLACE USER will delete the password — I was suggesting a safe alternative to your delete from mysql.user (and delete does not preserve the password either).

If you want to "revoke all grants" you can try to delete from mysql.roles_mapping where=.... This is not supported either, but it seems not to leave any artifacts, at least now.

Generated at Thu Feb 08 07:37:11 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.