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.
These privileges include:
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.