Example: Setting Up Custom Roles for Production and Stage
This tutorial takes you through the steps of setting up an organization with custom roles for production (prod) and stage. By the end of the tutorial you should have:
2 databases –
prod_dbandstage_db2 custom roles –
prod_managerandstage_managerprod_managershould have have full access to theprod_dbdatabasestage_managershould have have full access to thestage_dbdatabase
1 admin user
the admin user is the first user, granted the
ORGADMINrole and inheriting all other admin roles
2 non-admin users
prod user is granted
prod_managerandstage_managerstage user is granted only
stage_manager
Logging in as Admin User
When you're the first to log into an organization, by default you log in as the sysadmin built-in role. But you have access to all of the built-in admin roles, including orgadmin.
In this example, as the admin user, you have 4 small tasks:
Create databases
Create custom roles
Grant the appropriate privileges for these new roles
Invite non-admin users and assign them these custom roles
1. Creating Databases
When you create objects in an organization, use either the sysadmin role or a role that's lower in the hierarchy. Let's switch to the sysadmin role:
Use the CREATE DATABASE to create the prod_db and stage_db databases:
Now list your databases to view them:
2. Creating Custom Roles
Switch to the useradmin role. This role is responsible for creating roles and granting roles to other members of your organization:
Use the CREATE ROLE to create the prod_manager and stage_manager roles:
When you first create these roles, they are standalone roles detached from any other roles. However, you should grant these custom roles to the sysadmin role . When you do this they become lower members of the sysadmin role's hierarchy.
Use the GRANT ROLE command to grant these custom roles to the sysadmin:
Now use LIST ROLES to view the new roles you just created:
3. Granting the Appropriate Privileges for Each Role
With the new roles in place, it's time to grant these roles the usage privilege to work with their respective databases. As the sysadmin role is the owner of these databases, you must use that role to grant the usage privilege to the custom roles.
Start by switching to the sysadmin role:
Now use the GRANT PRIVILEGES to grant privileges to your custom roles. For this example, grant the USAGE privilege for each database to the matching role:
To see details and make sure you're on the right track, useDESCRIBE ROLE to view the new granted privileges for your prod_manager role:
4. Inviting Users and Assigning them Custom Roles
Great -- you've set up your databases and the custom roles to use them. Now it's time to invite users and assign them to a set of roles.
Inviting users is one of the useradmin responsibilities, so let's switch to that role:
Use INVITE USER to invite new users:
DeltaStream uses a person's email address as their unique identifier. For example, in the above code block you're inviting prod_user@demo.org to your organization and granting the prod_manager and stage_manager roles to them. For stage_user@demo.org, you're only granting the stage_manager role.
Now gain visibility into how interacting with the organization looks from each user's perspective.
Interacting with the Organization as Prod User
As the prod user, after accepting the invitation into this organization (see ACCEPT INVITATION), you see that your current role is the stage_manager role, as the useradmin set it as the default role for the prod user. Use LIST ROLES to verify which role you're using:
If you list the databases you only see stage_db. That's because stage_db is the only database available to the stage_manager role you are assuming:
Since the prod user has access to both the stage_manager and prod_manager roles, you can switch to the prod_manager role:
Using the prod_manager role, if you list your databases again you can see that only the prod_db database displays:
Interacting with the Organization as Stage User
As the stage user, after you accept the invitation into this organization (see ACCEPT INVITATION), you see that your current role is the stage_manager role, as the useradmin set it as the default role for the prod user. Use LIST ROLES to verify which role you're using:
If you list the databases you only see stage_db. That's because stage_db is the only database available to the stage_manager role you're assuming:
Notably, the stage_manager doesn't have access to the prod_manager role. The system displays an error it you try to use the prod_manager role:
Last updated