Assigning a Role to a User

{question}

How to assign a Role to a user?

{question}

{answer}

Role-Based Access Control (RBAC) provides a methodology for regulating an individual user’s access to information systems or network resources based on their role in an organization. RBAC enables security teams to efficiently create, change, or discontinue roles as the unique needs of an organization evolve without having to endure the hardship of updating the privileges of individual users. Click here to learn about reasons why role-based access control is essential for database security.

In SingleStore, roles cannot be directly assigned to a user; instead, users needed to be added to a group, and the role needed to be assigned to that group. In this way, it easier to remove or add users to a role by easily removing or adding a user to the group.

The following steps present an example for assigning a role to a user. Click here to learn more about RBAC (Role-Based Access Control).

Step 1: Create Role as below:

mysql> CREATE ROLE 'APP_CHECKER_ROLE';
Query OK, 0 rows affected (0.00 sec)

Step 2: Granting the Role with desired privilege on required the DB's and tables:

mysql> GRANT SELECT on *.* to ROLE 'APP_CHECKER_ROLE';
Query OK, 0 rows affected (0.00 sec)

Step 3: Now, we need to Create Group:

mysql> CREATE GROUP 'APP_CHECKERS';
Query OK, 0 rows affected (0.00 sec)

Step 4: Assigning the role to the group using Grant Role:

mysql> GRANT ROLE 'APP_CHECKER_ROLE' to 'APP_CHECKERS';
Query OK, 0 rows affected (0.00 sec)

Step 5: Granting the group to the specific user:

If you haven't created the user yet, Create User as below,

mysql> CREATE USER 'user_checker'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.01 sec)

Below command is used to Grant Group to a user,

mysql> GRANT GROUP 'APP_CHECKERS' TO 'user_checker';
Query OK, 0 rows affected (0.00 sec)

Validate Privileges using Show Grants mentioning the particular user as below,

mysql> SHOW GRANTS FOR 'user_checker'@'%';
+-------------------------------------------------------------------------------------------------------------+
| Grants for user_checker@% |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user_checker'@'%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT SELECT ON *.* TO 'user_checker'@'%' /* via roles and groups */ |
+-------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Related Topics

Role-Based Operations

Role Creation

Account Creation

To learn more about SingleStore DB User Management, click here.

{answer}