Administering Users in your Organization

This tutorial is helpful if you've just been given access to a new DeltaStream organization. It discusses the responsibilities of different administrator roles.

Note For additional background, see this high-level overview of access control concepts in DeltaStream.

One of the first things to do when you begin to assemble a new organization in DeltaStream is to invite users to add other administrators to administrative roles, including:

• sysadmin

• securityadmin

• useradmin

• orgadmin

Note the role hierarchy illustrated in the diagram below:

DeltaStream Role Hierarchy

Admin roles are built into DeltaStream. There are 4 types of admin roles:

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

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

3. USERADMIN — Role that has privileges to manage users and roles within the organization.

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

Important Admin roles are the most powerful roles in DeltaStream. Only select individuals should have them.

Using the OrgAdmin Role

The orgadmin role is the single most powerful role in DeltaStream. Use it only only for routine administrative tasks, such as inviting and removing users. Do not use it to grant access to resources for activities such creating and querying streams. For example, when you create objects such as databases, schemas, or relations, use the sysadmin or lower roles in the hierarchy. Similarly, when you invite new users or create new roles, use the useradmin role.

Tip As a best practice, use a lower role whenever possible.

When someone first gets access to DeltaStream, they're granted the orgadmin role. In turn they inherit the sysadmin and securityadmin roles, and their default role sysadmin.

1. Adding OrgAdmin Users

To add people to your organization, use the orgadmin or useradmin role. To invite others to be an orgadmin, use the INVITE USER command.

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

Important Do not set OrgAdmin as the default role for people you invite into your organization.

For more details, see Switching Roles and Inviting Users to an Organization.

2. Switching to a different role

After you invite others to be orgadmin, switch to use a different role.

USE ROLE useradmin;

Using the UserAdmin Role

The useradmin role should be the default for managing users and creating custom roles. The useradmin role is also powerful, as it includes the privilege to grant roles. As with orgadmin and securityadmin, be judicious when assigning the role of useradmin.

1. Managing Invitations

Inviting other people to the organization

The below example shows how to use the INVITE USER command to invite someone who has the useradmin and public roles. The default role is public:

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

Listing Invitations

The below example shows how to use the LIST INVITATIONS command; to see invitations you've only 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

Revoking Invitations

To delete a pending invitation, use the REVOKE INVITATION command:

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

Describing a User

To print (view) information about an existing user, use the DESCRIBE USER command:

<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

Granting a specific role to an individual or to another role

The below example shows how to use the GRANT ROLE command to grant the sysadmin role to a user:

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

The below example shows how to grant the custom role my_role to the sysadmin role:

GRANT ROLE my_role TO ROLE sysadmin;

Revoking a role from either a user or another role

The below example shows how to use the REVOKE ROLE command to revoke the sysadmin role from a user:

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

The below example shows how to revoke the custom role my_role from the sysadmin role:

REVOKE ROLE my_role FROM ROLE sysadmin;

Creating a custom role

The below example shows how to use the CREATE ROLE command to create the production_role role:

CREATE ROLE production_role;

Dropping a custom role

The below example shows how to using the DROP ROLE command to drop the production_role role:

DROP ROLE production_role;

Using the SecurityAdmin Role

The securityadmin role should be the default role for managing object grants. As the securityadmin role inherits privileges from the useradmin role, it's also a powerful role. And as with the orgadmin and useradmin roles, take care to give the role of securityadmin only to people who will need it.

1. Granting Privileges to Roles

DeltaStream allows a finite number of privileges. To view them, see Access Control Core Concepts.

The below example shows how to use the GRANT <PRIVILEGE> command to grant the CREATE_DATABASE privilege to the my_role role:

GRANT CREATE_DATABASE ON ORGANIZATION TO ROLE my_role;

Revoking privileges from existing roles

The below example shows how to use the REVOKE <PRIVILEGE> command to revoke the CREATE_DATABASE privilege from the my_role role:

REVOKE CREATE_DATABASE ON ORGANIZATION FROM ROLE my_role;

2. Granting Ownership of Objects to Different Roles

You can only transfer ownership of an object when:

• the current role is the owner of the object and has been granted the destination role

• the current role is securityadmin

Custom roles should be owned by the useradmin. You can grant the Sysadmin or other custom roles but not grant ownership.

Tip Avoid granting ownership of roles to the sysadmin role or to any roles that sysadmin inherits.

The below example shows how to use the GRANT OWNERSHIP command to grant ownership of the db Database to the my_role role:

GRANT OWNERSHIP ON DATABASE db TO my_role;

Using the SysAdmin Role

The sysadmin role has the privileges to create, manage, and drop objects. Most day-to-day tasks are done in the sysadmin role or in a custom role thesysadmin is granted.

Some of the main actions that sysadmin can perform 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

Tip This role typically is at the root of the hierarchy for all custom roles that also create, manage, or drop objects. In this way the sysadmin role is in charge of all objects in the organization.

See also:

• 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