Granting REQUIRE SSL privilege to set of users

{question}

How to grant REQUIRE SSL privilege to a set of users?

{question}

{answer}

We can assign the "REQUIRE SSL" privilege to multiple users using RBAC - Role Based Access Control. 

Example:

Step: 1

Create multiple users 'user1','user2','user3' (or) consider the set of existing users you want to enable SSL.

singlestore> show users;
+--------------------+--------+-------------+------------+-----------------------+----------+
| User | Type | Connections | Is deleted | Default resource pool | Is local |
+--------------------+--------+-------------+------------+-----------------------+----------+
| 'user1'@'%' | Native | 1 | | | LOCAL |
| 'root'@'%' | Native | 0 | | | LOCAL |
| 'root'@'localhost' | Native | 4 | | | LOCAL |
| 'ssl_user'@'%' | Native | 2 | | | LOCAL |
| 'user2'@'%' | Native | 0 | | | LOCAL |
| 'user3'@'%' | Native | 0 | | | LOCAL |
+--------------------+--------+-------------+------------+-----------------------+----------+

Step: 2

Create a role and grant all the necessary privileges to the role using the `GRANT ... TO ROLE ...` syntax:

singlestore> create role 'ssl_role';
Query OK, 0 rows affected (0.01 sec)

singlestore> grant all on *.* to role 'ssl_role' require ssl;
Query OK, 0 rows affected (0.01 sec)

Step: 3

Create a group for these users as below.

singlestore> create group 'ssl_group';
Query OK, 0 rows affected (0.01 sec)

Step: 4

Assign the role to the above created group. 

singlestore> grant role 'ssl_role' to 'ssl_group';
Query OK, 0 rows affected (0.01 sec)

Step: 5

Assign the users created in step 1 or the existing users to the created group. 

singlestore> grant group 'ssl_group' to user1;
Query OK, 0 rows affected (0.01 sec)

singlestore> grant group 'ssl_group' to user2;
Query OK, 0 rows affected (0.00 sec)

singlestore> grant group 'ssl_group' to user3;
Query OK, 0 rows affected (0.00 sec)

Validate the changes made using "show grants".

singlestore> show grants for 'user1'@'%';
+---------------------------------------------------------------------------------+
| Grants for user1@% |
+---------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'%' |
| GRANT ALL PRIVILEGES ON *.* TO 'user1'@'%' REQUIRE SSL /* via roles and groups */ |
+---------------------------------------------------------------------------------+

For more information on RBAC (Role Based Access Control), click here.

{answer}