Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com
Answertopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Restricting Rights with REVOKE

By default, a normal user has no all privileges on any database object that they do not own. To explicitly revoke a right after it has been granted, the object's owner (or a superuser) can issue the REVOKE command. This command is very similar in form to the GRANT command.

Here is the syntax for REVOKE:

  REVOKE 
privilege
 [, ...] ON 
object
 [, ...]
         FROM { PUBLIC | 
username
 | GROUP 
groupname
 }

The structure of the REVOKE command syntax is identical to that of the GRANT command, with the exception that the SQL command itself is REVOKE rather than GRANT, and the keyword FROM is used, rather than the TO keyword.

Note: Revoking privileges from PUBLIC only affects the special "public" group, which includes all users. Revoking rights from PUBLIC will not affect any users who have been explicitly granted those privileges.

Suppose the UPDATE rights on the books table have been granted to the user david. When David is transferred to another department, and no longer needs the ability to modify book information, you should revoke David's UPDATE privilege on the books table.

Example 10-19 uses the \z slash command in psql to check the permissions on the books table, revealing that david has write-access privileges to that table. A REVOKE statement then explicitly revokes the UPDATE and DELETE privileges on the books table from the user david. Finally, another \z slash command is executed to verify the removal of the privilege.

Example 10-19. Revoking rights

booktown=# 
\z books

Access permissions for database "booktown"
 Relation |       Access permissions
----------+--------------------------------
 books    | {"=","manager=arwR","david=w"}
(1 row)

booktown=# 
REVOKE UPDATE, DELETE ON books

booktown-# 
       FROM david;

CHANGE
booktown=# 
\z books

Access permissions for database "booktown"
 Relation |  Access permissions
----------+----------------------
 books    | {"=","manager=arwR"}
(1 row)
Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire