Role-Based Access Control
Timeplus Enterprise supports access control management based on RBAC approach.
You can configure the access entities via SQL. This works in both single node and cluster deployments.
Roles
You can create roles and associate them with Privileges.
Create an admin role
The following SQL creates the admin
role and grant all permissions to it.
CREATE ROLE admin;
GRANT ALL ON *.* TO admin;
Create a power user role
The following SQL creates the power_user
role and grant all permissions of default
database to it, also revoke the permission for this role to manage other roles or users.
CREATE ROLE power_user;
GRANT ALL ON default.* TO power_user;
REVOKE CREATE USER, DROP USER, ALTER USER, CREATE ROLE, DROP ROLE, ALTER ROLE ON *.* FROM power_user;
Create a read only user role
The following SQL creates the read_only
role and grant read permissions of default
database to it.
CREATE ROLE read_only;
GRANT SELECT ON default.* TO read_only;
GRANT SHOW ON default.* TO read_only;
Users
Create a user
The following SQL creates a user and assign the specific role to it.
CREATE USER user_name IDENTIFIED WITH plaintext_password BY 'password';
GRANT user_name TO role_name;
Drop a user
The following SQL drop the user. This won't delete the SQL resources created by the user.
DROP USER user_name;
Privileges
ALL
A special privilege to include all permissions.
SELECT
Able to run SELECT
on the specified streams or views, e.g.
GRANT SELECT ON test1 TO user1;
INSERT
Able to run INSERT
on the specified streams, e.g.
GRANT INSERT ON test1 TO user1;
CREATE
Able to create new streams, views or materialized views, e.g.
GRANT CREATE ON *.* TO user1;
You can also specify the resource type, such as GRANT CREATE USER
.
DROP
Able to drop new streams, views or materialized views, e.g.
GRANT DROP ON *.* TO user1;
You can also specify the resource type, such as GRANT DROP USER
.
KILL QUERY
Able to kill a running query, e.g.
GRANT KILL QUERY ON *.* TO user1;
-- user1 kill other's running query
KILL QUERY WHERE query_id='xxx';
ACCESS MANAGEMENT
Able to manage users, roles and row policies, e.g.
GRANT ACCESS MANAGEMENT ON *.* TO user1;
-- user1
CREATE USER user2;
Row Policies
Currently row-based access control is available for historical query, not for streaming query yet.
For example:
-- create row policy for user1 to select rows with col1 != 'hidden' in stream 'test1'
CREATE ROW POLICY policy1 ON test1 FOR SELECT USING col1 != 'hidden' TO user1;
-- alter row policy to also attach to user2
ALTER POLICY policy1 ON test1 TO user1, user2;
-- allow other user to SELECT all rows on test1
CREATE ROW POLICY policy2 ON test1 USING 1 TO ALL EXCEPT user1;
-- view the row policy
SHOW POLICIES ON default.*;
SHOW CREATE ROW POLICIES policy1;
-- user1 could only view rows with col1 != 'hidden'
SELECT * FROM table(test1);
Quota
Quota limits resource usage. Quota contains a set of limits for some duration, as well as a list of roles and/or users which should use this quota.
Management queries:
- CREATE QUOTA
- ALTER QUOTA
- DROP QUOTA
- SHOW CREATE QUOTA
- SHOW QUOTA
- SHOW QUOTAS
For example, the following SQL set the max query numbers per hour to 1, then change it to 10, and apply this to user1.
-- create and alter quota
CREATE QUOTA quota1 FOR INTERVAL 1 hour MAX queries = 1 TO user1;
ALTER QUOTA quota1 FOR INTERVAL 1 hour MAX queries = 10;
-- user1 run multiple queries and be blocked
SELECT * FROM test1;