Search code examples
mariadb

Can't grant roles with GRANT OPTION privilege

I have a MariaDB database with a few roles. For simplicity let's assume I have a admins role. It was created by the root user with the commands below:

CREATE ROLE admins;
GRANT ALL PRIVILEGES ON *.* TO admins WITH GRANT OPTION;

I also have another role default which does not have any privileges. I granted the admins role to a user myuser (again with root):

CREATE USER myuser IDENTIFIED BY 'mysuperstrongpassword';
GRANT admins TO myuser;

With that in mind I logged into a session using the myuser account, activated the admins role and created a new user newuser. However when I try to grant the default role to newuser with

GRANT default TO newuser;

I get hit with a Access denied to user 'myuser'@'%'. As of my understanding of the GRANT OPTION privilege, the grantee should have the permission to grant any privileges. Is there a different privilege required to allow setting roles?

I have found some information regarding the ADMIN OPTION privilege, however have not been able to grant it to a user or role with

GRANT ALL PRIVILEGES ON *.* TO [role|user] WITH ADMIN OPTION;

I haven't been able to verify if the ADMIN OPTION even exists for my version.

Current MariaDB version is 10.11.7

Solution

  • You've got a few problems listed, so do comment if I missed any.

    A role granted to a user isn't default. It can be set with SET DEFAULT ROLE like:

    SET DEFAULT ROLE admin FOR myuser

    Note a user can only have one default role. Use SET ROLE to switch to an explicit role if you have it:

    SET ROLE admin

    On your default role, if you want all users to have a base set of privileges, use the TO PUBLIC syntax (ref):

    GRANT SELECT ON common_db.* TO PUBLIC

    Granting ADMIN OPTION isn't a privilege, so it has explicit syntax (that doesn't use PRIVILEGE):

    GRANT admin ON [role|user] WITH ADMIN OPTION

GRANT default ON myuser WITH ADMIN OPTION