my company is going to evaluate PVM. I have installed it on a Windows Server 2012 R2 and successfully connected it to a MySQL 5.6.34 database (the community version, not the commercial) which is located on a different host (running Oracle Linux 7.2). That's all I was able to do so far.
I would now like to create users, but I get the error message that the user which I'm using to connect to the database has insufficient privileges. Why is it trying to create a user in the "mysql.user" table and not in a table in the dedicated database for PVM? On the latter, it would have sufficient rights.
We have several databases running on the Linux host and we can't give the devolutions user more rights than it has.
The only way I see to continue is to set up a new database server where we don't have to worry about other databases, so we can give the devolutions users more rights. Or is there another option that you can tell me?
When using a MySQL data source user are created as logins in MySQL. In other words "authentication -> MySQL" & "authorization -> RDM".
You RDM administrator needs rights to create logins & query the mysql.user table to be able to manager users.
Users don't have any access to other schemas on your server we only grant accesses to the RDM schema.
We also limit the access of non-admin RDM users to certain SQL operations (SELECT, INSERT, DELETE, UPDATE).
Thanks for your answer. Even though you refer to RDM, I assume this also applies to PVM.
I have granted our devolutions mysql user more rights now. The current grants are:
GRANT CREATE USER ON *.* TO 'devolutions'@'%' IDENTIFIED BY PASSWORD 'whatever'
GRANT ALL PRIVILEGES ON `password_vault`.* TO 'devolutions'@'%'
GRANT SELECT, UPDATE, DELETE, CREATE ON `mysql`.`user` TO 'devolutions'@'%'
This seems to be enough as we can now create and delete users. Do you see anything that is too much? Or something that is still missing that could hinder us at some other point?
That looks good. A few things to consider.
You might have issues when creating new admin user in RDM, added "with grant option" should resolve that.
GRANT CREATE USER ON *.* TO 'devolutions'@'%' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON `password_vault`.* TO 'devolutions'@'%' WITH GRANT OPTION;
You could removing UPDATE, DELETE CREATE on `mysql`.`user`statement. The GRANT CRATE USER is enough since we only SELECT from `mysql`.`user` to check for the existance of a user and prior to CREATE USER. Again "WITH GRANT OPTION" is required.
GRANT SELECT ON `mysql`.`user` TO 'devolutions'@'%' WITH GRANT OPTION;
I'm looking through the code in RDM and it looks like I can tweak the grant/revoke to give less privileges to admin users.