When you create a database object, you become its owner. By
default, only the owner of an object can do anything with the
object. In order to allow other users to use it,
privileges must be granted. (However,
users that have the superuser attribute can always
access any object.)
There are several different privileges: SELECT,
INSERT, UPDATE, DELETE,
RULE, REFERENCES, TRIGGER,
CREATE, TEMPORARY, EXECUTE,
and USAGE. The privileges applicable to a particular
object vary depending on the object's type (table, function, etc).
For complete
information on the different types of privileges supported by
EnterpriseDB, refer to the
GRANT reference page. The following sections
and chapters will also show you how those privileges are used.
The right to modify or destroy an object is always the privilege of
the owner only.
Note: To change the owner of a table, index, sequence, or view, use the
ALTER TABLE command. There are corresponding
ALTER commands for other object types.
To assign privileges, the GRANT command is used.
You grant privileges to users so these users can accomplish tasks
required for their job. You should grant a privilege only to a user
who absolutely requires the privilege to accomplish necessary work.
Excessive granting of unnecessary privileges can compromise security.
A user can receive a privilege in two different ways:
You can grant privileges to users explicitly.
For example, you can explicitly grant the privilege to insert records
into the emp table to the user SCOTT.
(Assuming that you have a user named SCOTT in the database).
You can also grant privileges to a role (a named group of privileges),
and then grant the role to one or more users.
For example, you can grant the privileges to select, insert, update,
and delete records from the emp table to the role named CLERK,
which in turn you can grant to the users SCOTT and BRIAN.
(Assuming that you have CLERK as a role and BRIAN as a user
in the database).
To assign privileges, the GRANT command is
used. For example, if joe is an existing user, and
emp is an existing table, the privilege to
update the table can be granted with
GRANT UPDATE ON emp TO joe;
A better approach is to append the schema name with the object
on which we are granting the privileges. So the above
statement could be re-written as:
GRANT UPDATE ON public.emp TO joe;
To grant a privilege for selecting from the dept table
to an existing ROLE called
staff, we would use the following
syntax:
GRANT SELECT ON public.dept TO GROUP staff;
To revoke a privilege, use the fittingly named
REVOKE command:
REVOKE ALL ON emp FROM staff;
The special privileges of the object owner (i.e., the right to do
DROP, GRANT, REVOKE, etc.)
are always implicit in being the owner,
and cannot be granted or revoked. But the object owner can choose
to revoke his own ordinary privileges, for example to make a
table read-only for himself as well as others.
Ordinarily, only the object's owner (or a superuser) can grant or revoke
privileges on an object. However, it is possible to grant a privilege
"GRANT ANY ROLE", which gives the recipient the right to
grant it in turn to others. If the grant option is subsequently revoked
then all who received the privilege from that recipient (directly or
through a chain of grants) will lose the privilege. For details see
the GRANT and REVOKE reference
pages.