Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5210

Default role

Details

    • Task
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.1.1
    • None
    • None

    Description

      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).

      The default role is stored in a new column in the mysql.user table.

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-4397 Roles

          • Major - Major loss of function.
          • Closed
          links to

          Web Link pull request

          Activity

            Ascending order - Click to sort in descending order
            serg Sergei Golubchik created issue -
            serg Sergei Golubchik made changes -
            Field Original Value New Value
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            Description Syntax variants (which ones we'll do?):
            {code:sql}
            CREATE USER xxx DEFAULT ROLE yyy;
            ALTER USER xxx DEFAULT ROLE yyy;
            SET DEFAULT ROLE yyy;
            SET DEFAULT ROLE yyy [ FOR xxx ];
            {code}
            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).             		Syntax variants (which ones we'll do?):
            {code:sql}
            CREATE USER xxx DEFAULT ROLE yyy;
            ALTER USER xxx DEFAULT ROLE yyy;
            SET DEFAULT ROLE yyy;
            SET DEFAULT ROLE yyy [ FOR xxx ];
            {code}
            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).

            The default role is stored in a new column in the mysql.user table.
            joffrey Joffrey MICHAIE (Inactive) added a comment -

            Hi,

            Without Roles, when a normal user logs-in, all his rights are loaded, and visible in the SHOW GRANTS FOR CURRENT_USER();

            With roles, a user may want to get ALL privileges from all his ROLES granted at connect time.
            This removes the need for default ROLE, and the need to run SET ROLE by applications/users to switch between roles.

            What are your thoughts on this ?

            Regards,
            Joffrey

            joffrey Joffrey MICHAIE (Inactive) added a comment - Hi, Without Roles, when a normal user logs-in, all his rights are loaded, and visible in the SHOW GRANTS FOR CURRENT_USER(); With roles, a user may want to get ALL privileges from all his ROLES granted at connect time. This removes the need for default ROLE, and the need to run SET ROLE by applications/users to switch between roles. What are your thoughts on this ? Regards, Joffrey
            serg Sergei Golubchik added a comment -

            It would be very non-standard. According to the SQL standard one needs SET ROLE to be able to use role's privileges, and one can have at most one role set at any given point in time.

            To have all privileges from all roles one should — to stay within the SQL standard — create a new role, grant all other roles to it, and then SET this new role.

            serg Sergei Golubchik added a comment - It would be very non-standard. According to the SQL standard one needs SET ROLE to be able to use role's privileges, and one can have at most one role set at any given point in time. To have all privileges from all roles one should — to stay within the SQL standard — create a new role, grant all other roles to it, and then SET this new role.
            serg Sergei Golubchik made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            serg Sergei Golubchik made changes -
            Assignee Vicentiu Ciorbaru [ cvicentiu ] Sergei Golubchik [ serg ]
            dbart Daniel Bartholomew made changes -
            Priority Critical [ 2 ] Major [ 3 ]
            serg Sergei Golubchik made changes -
            Workflow defaullt [ 29513 ] MariaDB v2 [ 44480 ]
            serg Sergei Golubchik made changes -
            Status Open [ 1 ] In Review [ 10002 ]
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            Fix Version/s 10.1 [ 16100 ]
            Fix Version/s 10.1.0 [ 12200 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.1.1 [ 16801 ]
            Fix Version/s 10.1 [ 16100 ]
            Resolution Fixed [ 1 ]
            Status In Review [ 10002 ] Closed [ 6 ]
            elenst Elena Stepanova added a comment - - edited

            For a note, we ended up with the second variant of syntax:

            SET DEFAULT ROLE yyy;
            		 
            SET DEFAULT ROLE yyy [ FOR xxx ];

            elenst Elena Stepanova added a comment - - edited For a note, we ended up with the second variant of syntax: SET DEFAULT ROLE yyy; SET DEFAULT ROLE yyy [ FOR xxx ];
            ratzpo Rasmus Johansson (Inactive) made changes -
            Workflow MariaDB v2 [ 44480 ] MariaDB v3 [ 67142 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 67142 ] MariaDB v4 [ 132210 ]

            People

              serg Sergei Golubchik
              serg Sergei Golubchik
              Votes:
              3 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.