Example: Setting Up Custom Roles for Prod and Stage
In this tutorial, we will go through an example of how to set up an Organization with custom roles for prod and stage. Starting from a fresh Organization, by the end of the tutorial we aim to have the following:
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
admin user will be the first user, granted the
ORGADMINrole and inheriting all other admin roles
2 non-admin users
prod user will be granted
prod_managerandstage_managerstage user will only be granted
stage_manager
As Admin User
The first user in an Organization will log in and by default be using the sysadmin built-in role. However, this user has access to all of the built-in admin roles, including orgadmin. As the admin user for this example, there are 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. Create Databases
When creating objects in an Organization, users should use the sysadmin role or a lower role in the hierarchy. Let's switch to the sysadmin role:
Use the CREATE DATABASE command to create the prod_db and stage_db Databases:
That's it! Now if we list our Databases, we should be able to see both:
2. Create Custom Roles
Switch to the useradmin role, which is responsible for creating roles and granting roles to users:
Use the CREATE ROLE command to create the prod_manager and stage_manager roles:
By just creating these roles, they are standalone roles that are detached from any other roles. However, these custom roles should be granted to the sysadmin role and become lower members of the sysadmin role's hierarchy. We can grant these custom roles to the sysadmin using the GRANT ROLE command:
We can see the new roles we've just created with the LIST ROLES command:
3. Grant the Appropriate Privileges for each Role
Now that our new roles are created, we need to grant these roles the usage privilege to work with their respective Databases. As the owner of these Databases, the sysadmin role should be used to grant this privilege to the custom roles. So, let's switch to the sysadmin role:
We can grant privileges to our custom roles by using the GRANT <PRIVILEGE> command (see the full list of privileges). For this example, we want to grant the USAGE privilege of a each Database to the matching role:
We can see the new granted privileges for our prod_manager role by describing the role using the DESCRIBE ROLE command:
4. Invite Users and Assign them these Custom Roles
Now that we have our Databases and custom roles set up, we can 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:
We can invite our new users using the INVITE USER command:
The email is used as the identifier for a particular user. In the above code block, we are inviting 'prod_user@demo.org' to our Organization and granting the prod_manager and stage_manager roles to them. For 'stage_user@demo.org', we are only granting the stage_manager role. Note that the useradmin can grant additional roles to users with the GRANT ROLE command, or revoke roles from users with the REVOKE ROLE command.
In the next sections, let's see how interacting with the Organization looks as each user.
As Prod User
As the prod user, after accepting the invitation into this Organization (see ACCEPT INVITATION command), we should see that our current role is the stage_manager role since the useradmin set it as the default role for the prod user. We can see which role we are using by using the LIST ROLES command:
If we list the Databases, we'll only see stage_db because that is the only Database that is available to the stage_manager role that we are assuming:
Since the prod user has access to both stage_manager and prod_manager roles, we can switch to the prod_manager role:
Using the prod_manager role, if we list our Databases again we can see that only the prod_db Database is shown:
As Stage User
As the stage user, after accepting the invitation into this Organization (see ACCEPT INVITATION command), we should see that our current role is the stage_manager role since the useradmin set it as the default role for the prod user. We can see which role we are using by using the LIST ROLES command:
If we list the Databases, we'll only see stage_db because that is the only Database that is available to the stage_manager role that we are assuming:
Notably, the stage user doesn't have access to the prod_manager role. If we try to use the prod_manager role we will get an error message:
Last updated