Details

    • Type: Task Task
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Fix Version/s: 10.0.5
    • Labels:
    • Global Rank:
      2854

      Description

      Roles

      As (or close to) defined in the SQL:2003 standard.

      New statements

      CREATE ROLE role
      DROP ROLE role
      GRANT role TO { user | user }
      REVOKE role FROM {user | role }
      SET ROLE { role_name | NONE }
      

      According to the standard, role and user names live in the same namespace. But in MariaDB a user name has a mandatory @host part (even if it's @%). And we probably wouldn't want it for roles. So, practically, we will have users and roles in different name spaces, with a little ambiguity, when a user name is specified without a @host part, where allowed. Alternatively, we can specify that a role name cannot match the first part of any user name. May be it'll be less confusing this way.

      Only one role can be set to a user at any specific point in any given session. In other words, CURRENT_ROLE (see below) can never return a list.

      Privileges

      One needs CREATE USER privilege to create or drop a role. Or an appropriate (insert or delete) privilege on the mysql database.

      One needs a role to be granted to himself WITH ADMIN OPTION to be able to grant or revoke it further. A creator of a role gets it automatically granted to himself WITH ADMIN CURRENT_USER. Alternatively, one can use WITH ADMIN CURRENT_ROLE or (with SUPER privilege) WITH ADMIN arbitrary_definer.

      Existing statements need to work with roles

      REVOKE privilege FROM { user | role }
      GRANT privilege TO { user | role }
      SHOW GRANTS FOR { user | role }
      

      New functions

      CURRENT_ROLE
      CURRENT_ROLE()
      

      If there is no current role, the function returns NULL, not "NONE".

      Informational tables

      INFORMATION_SCHEMA.APPLICABLE_ROLES
      INFORMATION_SCHEMA.ENABLED_ROLES
      

      there are more tables in the INFORMATION_SCHEMA that are relevant for roles. They are not part of this task.

      DEFINER=CURRENT_ROLE

      Everywhere where one can write DEFINER=xxx, we should allow xxx to be a role name. And additionally we'll support DEFINER=CURRENT_ROLE.

      Reserved role names

      The role name of PUBLIC is reserved. There can be no role with this name. But this name can be used in GRANT and REVOKE statements as a grantee.Privileges granted to PUBLIC are always available to everyone. Implementing it is not part of this task.

      The role name of NONE is reserved too.

      Default role (not part of this task)

      Syntax variants (which ones we'll do?):

      CREATE USER xxx DEFAULT ROLE yyy;
      ALTER USER xxx DEFAULT ROLE yyy;
      SET DEFAULT ROLE yyy;
      SET DEFAULT ROLE yyy [ FOR xxx ];
      

      When a default role is set, the server implicitly runs SET ROLE yyy for every new connection (or after a COM_CHANGE_USER for a user xxx.

        Issue Links

          Activity

          Hide
          Mark Callaghan added a comment -

          Implemented something like this long ago in the Google patch. Will be great to see an officially supported version of it – https://code.google.com/p/google-mysql-tools/wiki/MysqlRoles

          Show
          Mark Callaghan added a comment - Implemented something like this long ago in the Google patch. Will be great to see an officially supported version of it – https://code.google.com/p/google-mysql-tools/wiki/MysqlRoles
          Hide
          Sergei Golubchik added a comment - - edited

          lp:~maria-captains/maria/10.0-serg

          Show
          Sergei Golubchik added a comment - - edited lp:~maria-captains/maria/10.0-serg

            People

            • Assignee:
              Sergei Golubchik
              Reporter:
              Sergei Golubchik
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: