GRANT to Assign Privileges

1. Introduction

When we have an object such as a table, view, or index created, the object is assigned an owner. Typically, the owner of the object will be the role that executed the CREATE statement associated with the object. For most types of objects, typically, only the owner or superuser has the ability to do anything with the object. In order to allow other roles whether it is a user or group role to use and interact with the object, privileges to the object must be granted. There are many different types of privileges that are available to grant depending on the type of object.

2. Possible Privileges

These privileges include:

• SELECT – Allows the role to select from any column or the specific columns listed within a table, view, or sequence. This privilege would also be required if there is a need to reference existing column values in an UPDATE or DELETE statement.
• INSERT – Allows the role to INSERT a new row within a table. If there are specific columns that are listed, only those columns may be inserted into the other columns automatically being set with default values.
• UPDATE – Allows the role to UPDATE a column or a list of columns of a table. If the UPDATE privilege is granted, the SELECT privilege should also be granted since it has to reference the table columns to determine which rows of data should be updated.
• DELETE – Allows the role to DELETE a row from a table. If the DELETE privilege is granted, the SELECT privilege should also be granted since it has to reference the table columns to determine which rows of data should be removed.
• ALL – This grants access to all available privileges to that object.

Note there are other privileges as well that include TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, and USAGE that allows interaction with other objects.

3. Examples

Let us say we have a role called employee_role already created as a group role to add user roles to. We may want to allow the querying of the employee table but not allow any changes to the data. As such, we would run the following:

GRANT SELECT ON employee TO employee_role;

We could also grant the employee edit access and query access on the customer table like:

GRANT SELECT, INSERT, UPDATE, DELETE ON customer TO employee_role;

For an admin role, you could grant all privileges to the customer table like:

GRANT ALL ON customer TO admin_role;

If we wanted to grant access to specific columns, we would add those columns in round brackets after the privilege type. For example, if we wanted to grant UPDATE on the track but only on the unit_price to the employee_role, we would do the following:

GRANT UPDATE(unit_price) ON track TO employee_role;

This would allow us to only grant the ability to update the price but none of the other items.

---

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.