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:
Allows the user to read/retrieve data from a table or view.
Allows the user to append new rows into a table or view.
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.
Allows the user to create a rewrite rule on a table.
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
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;
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;
Example 8-12. Removing Privileges
REVOKE INSERT ON customer FROM temp;
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)