Problem creating new users with MariaDB

Hello,

I will have a look at this, but if you create users through our management screens they should have only whats necessary.

The only place where we usually get hung up is that we do not create instance level admins, we grant rights only to the database.

Thanks.

Just to make it clear, I am using RDM to create the users, but trying to create Data Source for each new user fails, unless I'm connecting to the DB and manually granting privs:

MariaDB [(none)]> grant all on rdm.* to 'newuser'@'%';
MariaDB [(none)]> FLUSH PRIVILEGES;

that definitely too many permissions, I am looking at the code and will let you know.

Hello,

Here's what I found

When you check the "administrator" checkbox for a user, we execute

GRANT ALL ON *.* TO '{username}'@'%' WITH GRANT OPTION;

This is what is called a Global Privilege because of the *.*

When a user is NOT an administrator, we call


REVOKE ALL ON *.* FROM '{userName}'@'%';
GRANT SELECT, INSERT, DELETE, UPDATE ON `{database}`.* TO '{userName}'@'%';
GRANT EXECUTE ON `{database}`.* TO '{userName}'@'%';


The account that you used to perform the initial setup, did it have the 'with grant option'? I have battled to set my environment up because of all the subtleties of @'%' versus @'localhost' which is really important for working on the Mysql server itself.

Thanks!

The "GRANT" priv was missing, now the "admin" user can create new users successfully from within RDM.

I seem to be experiencing this issue with 10.5.1.0.
I create a new user with the Administration tab, with or without Administrator privileges gives the same result.
Next I create a new datasource and enter the username/password and the Schema name.
Test Host indicates a successful connection, Test Schema results in:



using SHOW GRANTS FOR 'kishp' I see that he has GRANT USAGE but no other grants.
SELECT * FROM MySQL.db; is an empty set also. Not sure whats going on, if I grant SELECT and other permissions manually to the DB it works, but this is obviously not an optimal solution.

This is odd. The MariaDB/MySQL data source performs the following grant/revoke statements when you create/edit a user.

AdminGRANT ALL ON *.* TO '{0}'@'%' WITH GRANT OPTION;
Non Admin: REVOKE ALL ON *.* FROM '{0}'@'%';

GRANT SELECT, INSERT, DELETE, UPDATE ON '{0}'.* TO '{1}'@'%';

GRANT EXECUTE ON '{0}'.* TO '{1}'@'%';
Could it be that the user that created the new user does not have sufficient rights to be able to execute the above statements?

That's exactly what it was. The original DB user was missing the WITH GRANT OPTION, thanks!

Hate to necropost, but this is the exact issue I just had.
RDM 13.6.7.0.
Fedora Server 28
# mysql --version
mysql Ver 15.1 Distrib 10.2.17-MariaDB, for Linux (x86_64) using readline 5.1

I created a new MariaDB datasource. Connected with the MariaDB root account and created an initial administrator account. The new account did not get created with the "WITH GRANT OPTION" privilege. This caused the "Test Schema" option to fail.

MariaDB [(none)]> SHOW GRANTS FOR 'rdmAdmin'@'%';
+---------------------------------------------------------------------------------------------------------+
| Grants for rdmAdmin@% |
+---------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'rdmAdmin'@'%' IDENTIFIED BY PASSWORD '<snip>' |
+---------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

After manually updating the rdmAdmin account privileges to include GRANT OPTIONS, the account started working.
The root account does have the correct permissions:

MariaDB [(none)]> SHOW GRANTS FOR 'root'@'%';
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@% |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*478DA7BE4EA792031DF24E0467EEEF1D1488B72D' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

@mrjester - thank you for the information. I've reworked the grant/deny/revoke code in the v14 beta (v13.9.x). I will double check to make sure all is good in the next beta release.

I've tested with RDM (v13.7.6 & v13.9.x(beta)) on a "MySQL Community Server - GPL" v8.0.11. Here are my results

User 'stef' created using 'root':> SHOW GRANTS FOR 'stef'@'%'

GRANT USAGE ON *.* TO `stef`@`%`
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `testing`.* TO `stef`@`%`
2 rows in setUser 'stef-admin' created using 'root' RDM v13.9x:> SHOW GRANTS FOR 'stef-admin'@'%'

GRANT CREATE USER ON *.* TO `stef-admin`@`%` WITH GRANT OPTION
GRANT ALL PRIVILEGES ON `testing`.* TO `stef-admin`@`%` WITH GRANT OPTION
GRANT SELECT ON `mysql`.`user` TO `stef-admin`@`%` WITH GRANT OPTION
3 rows in setUser 'stef2' created using 'stef-admin':> SHOW GRANTS FOR 'stef2'@'%'

GRANT USAGE ON *.* TO `stef2`@`%`
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `testing`.* TO `stef2`@`%`
2 rows in set
As you can see all looks normal on my side. I don't have an explanation as to why it failed on your end.

Perhaps something different between Maria and MySQL. My experience was with MariaDB.