Example: Setting Up Custom Roles for Production and Stage
This article takes you through the steps of setting up an organization with custom roles for production (prod) and stage. Follow along, and by the end of the article 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 newly-created custom 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 has the privilege 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 New Role
With the new roles in place, it's time to grant these roles the usage privilege each role needs to work with its 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 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 the responsibility of the useradmin, 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 to them the prod_manager and stage_manager roles. For stage_user@demo.org, you're granting only 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 (because the useradmin set it as the default role for the prod user). Use LIST ROLES to verify the 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:
Now 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 (because the useradmin set it as the default role for the prod user). Use LIST ROLES to verify the 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