User Management for Admins

This tutorial covers the responsibilities of different administrator roles and is especially helpful for users who have just been given access to a new DeltaStream #_organiation. A high level overview of the core concepts of Access Control can be found here.

The first task for a new Organization is to invite users to add other administrators to administrative roles – sysadmin, securityadmin, useradmin, orgadmin. An overview of the hierarchy of roles can be found in the diagram below:

Role hierarchy

Note that admin roles are very powerful in the system and should only be given to select individuals. The admin roles are built-in Roles created by DeltaStream. To briefly cover each of the admin roles:

• ORGADMIN — Root of the the role hierarchy. This role manages operations at the organization level.

• SYSADMIN — Role that has privileges to create, manage, and drop objects.

• USERADMIN — Role that has privileges to manage users and roles within the Organization.

• SECURITYADMIN — Role that manages any object grants globally. This role inherits privileges from the useradmin role.

Using OrgAdmin

The orgadmin role is the most powerful role and should only be used for administrative tasks. As a best practice, use a lower role whenever possible. For example, when creating Objects such as Databases, Schemas, or Relations, use the sysadmin or lower roles in the hierarchy. Similarly when inviting new users or creating new Roles, use the useradmin role.

After getting access to DeltaStream, the first user will be granted the orgadmin role. The sysadmin, and securityadmin roles are also inherited and sysadmin is used as the default role for the user.

1. Add other OrgAdmin users

Using the orgadmin or useradmin role (tutorial for switching roles), invite other users to be orgadmin using the INVITE USER command (also see our other tutorial on inviting users to an Organization). Set these users' default role to something that is NOT orgadmin.

INVITE USER '[e-mail]' WITH (
  'roles' = (orgadmin, sysadmin), 
  'default' = sysadmin
);

2. Switch to a different role

After inviting other orgadmins, switch to use a different role. While there are no restrictions against it, best practices are to avoid using the orgadmin for day-to-day tasks.

USE ROLE useradmin;

Using UserAdmin

The useradmin role should be the default for managing users and creating custom roles. Note that the useradmin role is very powerful as well, with the privilege to grant roles. So similarly to orgadmin and securityadmin, only very select users should be given the role of useradmin.

1. Managing Invitations

Invite User

Invite other users to the Organization. The following is an example of inviting a user with the useradmin and public roles, default being public, using the INVITE USER command:

INVITE USER '[e-mail]' WITH (
  'roles' = (useradmin, "public"), 
  'default' = "public"
);

List Invitations

List invitations using the LIST INVITATIONS command. To see invitations that the user just sent, use LIST PENDING INVITATIONS:

<no-db>/<no-store># LIST PENDING INVITATIONS;
             Invitation ID             | Org name |                            Org ID                            |     Email     | Invited by  |    User roles    | Default role
---------------------------------------+----------+--------------------------------------------------------------+---------------+-------------+------------------+---------------
  8f7a4504-ce64-4ee3-a9b5-227925e9dq44 | doc_org  | 830e26fe-de4g-4996-839f-bccb258f8f91                         | [e-mail] | useradmin   | useradmin,public | public

Revoke Invitation

Use the REVOKE INVITATION command to delete a pending invitation:

REVOKE INVITATION 8f7a4504-ce64-4ee3-a9b5-227925e9dq44;

Describe User

Use the DESCRIBE USER command to print information about an existing user:

<no-db>/<no-store># DESCRIBE USER '[e-mail]' ;
  Given name | Family name |     Email     | Locale
-------------+-------------+---------------+---------
  user       | Demo        | [e-mail] | en

  GrantedRoles | Inherited
---------------+------------
  useradmin    |
  public       | ✓

2. Managing Roles

Grant Role

Grant a specific role to a user or another role. In this example, we are granting the sysadmin role to a user using the GRANT ROLE command:

GRANT ROLE sysadmin TO USER '[e-mail]';

In this example, we are granting the custom role my_role to the sysadmin role:

GRANT ROLE my_role TO ROLE sysadmin;

Revoke a role

Similarly, we can revoke roles from users or other roles. In this example we are revoking the sysadmin role from a user using the REVOKE ROLE command:

REVOKE ROLE sysadmin FROM USER '[e-mail]';

In this example, we are revoking the custom role my_role from the sysadmin role:

REVOKE ROLE my_role FROM ROLE sysadmin;

Create a custom role

Create a new custom role. In this example we are creating the production_role role using the CREATE ROLE command:

CREATE ROLE production_role;

Drop a custom role

Drop an existing role. In this example we are dropping the production_role role using the DROP ROLE command:

DROP ROLE production_role;

Using SecurityAdmin

The securityadmin role should be the default for managing object grants. Note that the securityadmin role is very powerful as well as it inherits from the useradmin role. So similarly to orgadmin and useradmin, only very select users should be given the role of securityadmin.

1. Granting Privileges

Grant privileges

Grant privileges to roles. A list of all privileges can be found on the Access Control Core Concepts page. In this example we are granting the CREATE_DATABASE privilege to the my_role role using the GRANT <PRIVILEGE> command:

GRANT CREATE_DATABASE ON ORGANIZATION TO ROLE my_role;

Revoke privileges

Revoke privileges from existing roles. A list of all privileges can be found on the Access Control Core Concepts page. In this example we are revoking the CREATE_DATABASE privilege from the my_role role using the REVOKE <PRIVILEGE> command:

REVOKE CREATE_DATABASE ON ORGANIZATION FROM ROLE my_role;

2. Granting Ownership

Grant ownership

Grant ownership of objects to different roles. Ownership can only be transferred if the current role is the owner or the object and has been granted the destination role, or if the current role is securityadmin. Note that as a best practice, granting ownership of roles to the sysadmin role or any roles that sysadmin inherits is discouraged. Custom roles should be owned by the useradmin, and sysadmin or other custom roles can be granted those roles (but not granted ownership).

In this example we are granting ownership of the db Database to the my_role role using the GRANT OWNERSHIP command:

GRANT OWNERSHIP ON DATABASE db TO my_role;

Using SysAdmin

The sysadmin role has the privileges to create, manage, and drop objects. As a best practice, this role should be at the root of the hierarchy for all custom roles that also create, manage, or drop objects. This way, the sysadmin role is in charge of all objects in the Organization.

Most day-to-day tasks will be done in the sysadmin role or a custom role that sysadmin is granted. Some of the main actions that the sysadmin can do include:

• CREATE STORE / DROP STORE

• CREATE ENTITY / DROP ENTITY

• CREATE DATABASE / DROP DATABASE

• CREATE SCHEMA / DROP SCHEMA

• CREATE STREAM / DROP STREAM

• CREATE CHANGELOG / DROP CHANGELOG

• CREATE MATERIALIZED VIEW AS

• SELECT

• TERMINATE QUERY

See some of our other tutorials for examples:

• Creating Stores for Streaming Data

• Using Multiple Stores in Queries

• Creating Relations to Structure Raw Data

• Namespacing with Database and Schema

• Creating and Querying Materialized Views