When a user creates a database, they automatically become the owner of that database. The owner and the database superuser are the only users allowed to modify, or remove the database object. If other users need access to this database, they can have certain privileges granted to them.

The four possible privileges are:

SELECT

Allows the user to read/retrieve data from a table or view.

INSERT

Allows the user to append new rows into a table or view.

UPDATE/DELETE

Allows the user to update or delete rows of data from a table or view. UPDATE can also be restricted to columns or views of the table.

RULE

Allows the user to create a rewrite rule on a table.

Note Note
 

The owner has the privilege to modify or destroy an object.

To assign any of these privileges ( UPDATE , INSERT , SELECT , DELETE , or RULE ), use the GRANT command:

    GRANT privilege ON table TO username/GROUP groupname
   

The sales department in the Book Town store should be given permission to view the customer table, but not modify it. Use the following to grant them this permission:

Example 8-9. Granting Group Privileges

    GRANT SELECT ON customer TO GROUP sales;

   CHANGE
   

A PUBLIC user exists that contains every user on the system. If you specify PUBLIC in the place of username, it grants those permissions to every user on the system. If you want to grant every privilege to a user, you can use ALL in place of the privilege name.

This grants select privileges on the customer table to every user that can access the database:

   GRANT SELECT ON customer TO public;
   

If you don't want a user or group to view all of the columns in a table, then you can limit their access to the table by creating a view and then giving them permission to query the view. This limits those users to only seeing certain columns or rows. To create a view, use the following syntax:

 CREATE VIEW viewname AS SELECT query_to_view;
   

For instance, our booktown store has a manager named Mark who maintains all of the employee information. The accounting department should only be granted permission to see employee names and identification numbers. To do this, use the following command:

Example 8-10. Creating a View

  CREATE VIEW emp_view 
    AS SELECT emp_id, name
         FROM employees; 
   

This command grants SELECT privileges to the accounting department:

Example 8-11. Granting View Privileges to a Group

  GRANT SELECT ON emp_view TO GROUP accounting;
   

To refuse a privilege to a user, use REVOKE :

    REVOKE ALL ON table/view FROM username/GROUP groupname;
   

For instance, to revoke temp's insert privileges on the customer table, use:

Example 8-12. Removing Privileges

   REVOKE INSERT ON customer FROM temp;
   

Note Table Owner
 

A user cannot revoke a privilege held by the table owner.

Help us make a better book, leave feedback. (http://www.opendocspublishing.com/entry.lxp?lxpe=92)