Help Center/ GaussDB(DWS)/ Best Practices/ Database Management/ Role-based Access Control (RBAC)
Updated on 2024-10-29 GMT+08:00
View PDF
Share

Role-based Access Control (RBAC)

What is RBAC?

  • Role-based access control (RBAC) is to grant permissions to roles and let users obtain permissions by associating with roles.
  • A role is a set of permissions.
  • RBAC greatly simplifies permissions management.

What is the RBAC Model?

Assign appropriate permissions to roles.

Associate users with the roles.

Scenarios

Assume there are two schemas, s1 and s2.

There are two groups of users:

  • Users u1 and u2 can query all the tables in s1 and update all the tables in s2.
  • Users u3 and u4 can query all the tables in s2 and update all the tables in s1.

Granting Permissions

  1. Connect to the GaussDB(DWS) database as user dbadmin.
  2. Run the following statements to create schemas s1 and s2 and users u1 to u4:

    Replace {password} with the actual password.

    1
2
3
4
5
6
    		 
    CREATE SCHEMA s1;
CREATE SCHEMA s2;
CREATE USER u1 PASSWORD '{password}';
CREATE USER u2 PASSWORD '{password}';
CREATE USER u3 PASSWORD '{password}';
CREATE USER u4 PASSWORD '{password}';

  3. Copy and run the following statements to create the s1.t1 and s2.t1 tables: 

    1
2
    		 
    CREATE TABLE s1.t1 (c1 int, c2 int);
CREATE TABLE s2.t1 (c1 int, c2 int);

  4. Run the following statement to insert data to the tables: 

    1
2
    		 
    INSERT INTO s1.t1 VALUES (1,2);
INSERT INTO s2.t1 VALUES (1,2);

  5. Run the following statements to create four roles, each having the query or update permission of table s1 or s2: 

    1
2
3
4
    		 
    CREATE ROLE rs1_select PASSWORD disable;  -- Permission to query s1
CREATE ROLE rs1_update PASSWORD disable; -- Permission to update s1
CREATE ROLE rs2_select PASSWORD disable; -- Permission to query s2
CREATE ROLE rs2_update PASSWORD disable; -- Permission to update s2

  6. Run the following statements to grant the access permissions of schemas s1 and s2 to the roles: 

    1
    		 
    GRANT USAGE ON SCHEMA s1, s2 TO rs1_select, rs1_update,rs2_select, rs2_update;

  7. Run the following statements to grant specific permissions to the roles: 

    1
2
3
4
    		 
    GRANT SELECT ON ALL TABLES IN SCHEMA s1 TO rs1_select; -- Grant the query permission on all the tables in s1 to the rs1_select role.
GRANT SELECT,UPDATE ON ALL TABLES IN SCHEMA s1 TO rs1_update;  -- Grant the query and update permissions on all the tables in s1 to the rs1_update role.
GRANT SELECT ON ALL TABLES IN SCHEMA s2 TO rs2_select;  -- Grant the query permission on all the tables in s2 to the rs2_select role.
GRANT SELECT,UPDATE ON ALL TABLES IN SCHEMA s2 TO rs2_update;  -- Grant the query and update permissions on all the tables in s2 to the rs2_update role.

  8. Run the following statements to grant roles to users: 

    1
2
    		 
    GRANT rs1_select, rs2_update TO u1, u2;  -- Users u1 and u2 have the permissions to query s1 and update s2.
GRANT rs2_select, rs1_update TO u3, u4;  -- Users u3 and u4 have the permissions to query s2 and update s1.

  9. Run the following statement to view the role bound to a specific user: 

    1
    		 
    \du u1;

  10. Start another session. Connect to the database as user u1. 

    1
    		 
    gsql -d gaussdb -h GaussDB(DWS)_EIP -U u1 -p 8000 -r -W {password};

  11. Run the following statements in the new session verify that user u1 can query but cannot update s1.t1: 

    1
2
    		 
    SELECT * FROM s1.t1;
UPDATE s1.t1 SET c2 = 3 WHERE c1 = 1;

  12. Run the following statements in the new session to verify that user u1 can update s2.t1: 

    1
2
    		 
    SELECT * FROM s2.t1;
UPDATE s2.t1 SET c2 = 3 WHERE c1 = 1;

Parent topic: Database Management