Online College Courses for Credit

+
1 Tutorials that teach GRANT to Assign Users
Take your pick:
GRANT to Assign Users

GRANT to Assign Users

Rating:
(0)
Author: Sophia Tutorial
Description:

Identify a correctly composed GRANT statement to assign users to a role/group.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

what's covered
This tutorial explores the GRANT and REVOKE command to assign user roles to a group role.

It can be quite useful to have group roles created as we did in the prior tutorial so that it is easier to manage privileges and permissions. This is especially important as an organization has more users. This way, those privileges and permissions can be granted to or revoked from a group as a whole rather than from the individual users.

As we explored in the past two tutorials, typically a role that is used as a group would not have the LOGIN attribute but it can logically. However, it does not make sense to have it defined as such. As a reminder as well, in PostgreSQL, there is not a distinction between group roles or non-group roles. As such, you can grant membership to other group roles rather than just to user roles.

Let us first create a user role for an admin account:

CREATE ROLE myadmin
LOGIN
PASSWORD 'mypassword';

We can then create a group role called adminrole that has the various admin privileges:

CREATE ROLE adminrole
CREATEDB
CREATEROLE;

If we wanted to grant the adminrole role to the myadmin user role, we would do so with the GRANT command like:

GRANT adminrole TO myadmin;

If we wanted to grant to more than one user at a time, we could include the list of users separated by commas like:

GRANT adminrole TO myadmin1, myadmin2, myadmin3;

Say if we wanted to separate out the admin role to have a separate role that could create roles and separately one that could create databases:

CREATE ROLE adminrole_cr
CREATEROLE;

CREATE ROLE adminrole_db
CREATEDB;

We can grant permissions separately to myadmin by doing:

GRANT adminrole_cr TO myadmin;
GRANT adminrole_db TO myadmin;

If we wanted to take away the permission to create databases, we can use the REVOKE command like:

REVOKE adminrole_db FROM myadmin;

Since both of those roles are group roles, you could grant them to each other but only in one direction as the database will not allow you to set up circular membership loops:

GRANT adminrole_cr TO adminrole_db;
GRANT adminrole_db TO adminrole_cr;


File:11495-3220-1.png


try it
Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.

summary
We can GRANT and REVOKE group roles to and from user role and other group roles.