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_db
andstage_db
2 custom roles –
prod_manager
andstage_manager
prod_manager
should have have full access to theprod_db
databasestage_manager
should have have full access to thestage_db
database
1 admin user
admin user will be the first user, granted the
ORGADMIN
role and inheriting all other admin roles
2 non-admin users
prod user will be granted
prod_manager
andstage_manager
stage 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 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 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:
We can see the new roles we've just created with the LIST ROLES:
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 PRIVILEGES (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:
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:
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, or revoke roles from users with the REVOKE ROLE.
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), 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:
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), 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:
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