Skip to main content

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

Any user can reset their own password without needing any privileges.

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';

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;