Manage database users in a cluster

This topic describes how to manage users, roles, and privileges in CelerData.

CelerData employs both role-based access control (RBAC) and identity-based access control (IBAC) to manage privileges within a CelerData cluster, allowing cluster administrators to easily restrict privileges within the cluster on different granular levels.

Within a CelerData cluster, privileges can be granted to users or roles. A role is a collection of privileges that can be assigned to users or other roles in the cluster as needed. A user can be granted one or more roles, which determine their permissions on different objects.

Manage users

Users with the system-defined role user_admin can create users, alter users, and drop users within the CelerData cluster.

Create a user

You can create a user by specifying the username, authentication method, and default role. For more information and advanced instructions on creating a user, see CREATE USER.

The following example creates the user jack, sets the password to 12345 for it, and assigns the role example_role to it as its default role:

CREATE USER jack IDENTIFIED BY '12345' DEFAULT ROLE 'example_role';

Alter a user

You can alter the password or property for a user.

The default role of a user is automatically activated when the user connects to CelerData.

Alter the property of a user

You can set the property of a user using ALTER USER.

The following example sets the maximum number of connections for the user jack to 1000.

SET PROPERTY FOR jack 'max_user_connections' = '1000';

Reset password for a user

You can reset the password for a user using SET PASSWORD or ALTER USER.

NOTE

Only the cluster admin user can reset password for regular users in the cluster.

To reset password for the admin user, see Reset admin user password.

Both the following examples reset the password of jack to 54321:

Reset the password using SET PASSWORD:

SET PASSWORD FOR jack = PASSWORD('54321');

Reset the password using ALTER USER:
```
ALTER USER jack IDENTIFIED BY '54321';
```

Reset password for `admin` user

If you have lost the password of the admin user and cannot connect to the cluster, you can reset it only on the CelerData Cloud BYOC console.

NOTE

Only CelerData members with Edit cluster privilege on the cluster can reset the password for the cluster admin cluster.

Follow these steps:

Sign in to the CelerData Cloud BYOC console.
On the Clusters page, click the cluster for which you want to reset the admin user password.
On the cluster details page, click Manage and choose Reset password.
On the dialog box that appears, enter a new password for the admin user, or generate a random password by clicking Generate password.
Click Reset & Save to save the new password.

Drop a user

You can drop a user using DROP USER.

The following example drops the user jack:

DROP USER jack;

Manage roles

Users with the system-defined role user_admin can create, grant, revoke, or drop roles in the CelerData cluster.

Create a role

You can create a role using CREATE ROLE.

The following example creates the role example_role:

CREATE ROLE example_role;

Grant a role

You can grant roles to a user or another role using GRANT.

Grant a role to a user.

The following example grants the role example_role to the user jack:
```
GRANT example_role TO USER jack;
```
Grant a role to another role.

The following example grants the role example_role to the role test_role:
```
GRANT example_role TO ROLE test_role;
```

Revoke a role

You can revoke roles from a user or another role using REVOKE.

NOTE

You cannot revoke the system-defined default role PUBLIC from a user.

Revoke a role from a user.

The following example revokes the role example_role from the user jack:
```
REVOKE example_role FROM USER jack;
```
Revoke a role from another role.

The following example revokes the role example_role from the role test_role:
```
REVOKE example_role FROM ROLE test_role;
```

Drop a role

You can drop a role using DROP ROLE.

The following example drops the role example_role:

DROP ROLE example_role;

CAUTION

System-defined roles cannot be dropped.

Manage privileges

Users with the system-defined role user_admin can grant or revoke privileges in the CelerData cluster.

Grant privileges

You can grant privileges to a user or a role using GRANT.

Grant a privilege to a user.

The following example grants the SELECT privilege on the table sr_member to the user jack, and allows jack to grant this privilege to other users or roles (by specifying WITH GRANT OPTION in the SQL statement):
```
GRANT SELECT ON TABLE sr_member TO USER jack WITH GRANT OPTION;
```
Grant a privilege to a role.

The following example grants the SELECT privilege on the table sr_member to the role example_role:
```
GRANT SELECT ON TABLE sr_member TO ROLE example_role;
```

Revoke privileges

You can revoke privileges from a user or a role using REVOKE.

Revoke a privilege from a user.

The following example revokes the SELECT privilege on the table sr_member from the user jack, and disallows jack to grant this privilege to other users or roles):
```
REVOKE SELECT ON TABLE sr_member FROM USER jack;
```
Revoke a privilege from a role.

The following example revokes the SELECT privilege on the table sr_member from the role example_role:
```
REVOKE SELECT ON TABLE sr_member FROM ROLE example_role;
```

View user and role information

Users with the system-defined role user_admin can view all the user and role information within the CelerData cluster.

View privilege information

You can view the privileges granted to a user or a role using SHOW GRANTS.

View the privileges of the current user.
```
SHOW GRANTS;
```
NOTE

Any user can view their own privileges without needing any privileges.
View the privileges of a specific user.

The following example shows the privileges of the user jack:
```
SHOW GRANTS FOR jack;
```
View the privileges of a specific role.

The following example shows the privileges of the role example_role:
```
SHOW GRANTS FOR ROLE example_role;
```

View user property

You can view the property of a user using SHOW PROPERTY.

The following example shows the property of the user jack:

SHOW PROPERTY FOR jack;

View roles

You can view all the roles within the CelerData cloud account using SHOW ROLES.

SHOW ROLES;

View users

You can view all the users within the CelerData cluster using SHOW USERS.

SHOW USERS;

Manage users​

Create a user​

Alter a user​

Alter the property of a user​

Reset password for a user​

Reset password for admin user​

Drop a user​

Manage roles​

Create a role​

Grant a role​

Revoke a role​

Drop a role​

Manage privileges​

Grant privileges​

Revoke privileges​

View user and role information​

View privilege information​

View user property​

View roles​

View users​