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

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

Using Views for Access Control

While you cannot control read-access to specified columns or rows of a table, you can achieve this indirectly through the careful use of views. By creating a view on a table, and forcing users to access the table through that view, you can allow only desired columns or rows to be selected.

You limit columns by specifying a column list in the view's SELECT statement when you create the view. The view will then return only the columns you specify. You limit rows by writing a WHERE clause in the view's SELECT statement. The view will then return only those rows that match the WHERE clause (see Chapter 4, for more about creating views).

As ACL privileges may be applied to views as well as tables, you may then grant SELECT rights to the limited view, but not the table itself. Users will then be able to select from the view even though they don't have access to the underlying table.

For instance, the Book Town store has a stock table correlating a book's ISBN number to its purchase cost, retail price, and the current available stock. The table structure is shown in Table 10-3.

Table 10-3. The stock table

Column

Type

Modifier

isbn

text

NOT NULL

cost

numeric(5,2)

retail

numeric(5,2)

stock

integer

Suppose that the manager of Book Town doesn't want the salespeople to have access to the purchase cost of each book. This information can be restricted by generating a view which retrieves data from only the isbn, retail and stock columns. Example 10-20 creates such a view, grants rights to the sales group, and verifies the rights with the \z psql slash command.

Example 10-20. Controlling SELECT privileges with a view

booktown=# 
CREATE VIEW stock_view

booktown-# 
            AS SELECT isbn, retail, stock

booktown-# 
               FROM stock;

CREATE
booktown=# 
GRANT SELECT ON stock_view TO GROUP sales;

CHANGE
booktown=# 
\z stock

      Access permissions for database "booktown"
   Relation   |          Access permissions
--------------+---------------------------------------
 stock        |
 stock_backup |
 stock_view   | {"=","manager=arwR","group sales=r"}
(3 rows)

Example 10-21 demonstrates the addition of a new user, barbara. It grants SELECT rights on the stock_view. Since the barbara user does not have any implicit rights on the stock table, it is inaccessible; this is the case, even though the view on that table is accessible as a result of the GRANT statement.

Example 10-21. Controlling SELECT

booktown=# 
CREATE USER barbara;

CREATE USER
booktown=# 
GRANT USER barbara SELECT ON stock_view;

booktown=# 
\c - barbara

You are now connected as new user barbara.
booktown=> 
SELECT * FROM stock;

ERROR:  stock: Permission denied.
booktown=> 
SELECT * FROM stock_view;

    isbn    | retail | stock
------------+--------+-------
 0385121679 |  36.95 |    65
 039480001X |  32.95 |    31
 0394900014 |  23.95 |     0
 044100590X |  45.95 |    89
 0441172717 |  21.95 |    77
 0451160916 |  28.95 |    22
 0451198492 |  46.95 |     0
 0451457994 |  22.95 |     0
 0590445065 |  23.95 |    10
 0679803335 |  24.95 |    18
 0694003611 |  28.95 |    50
 0760720002 |  23.95 |    28
 0823015505 |  28.95 |    16
 0929605942 |  21.95 |    25
 1885418035 |  24.95 |    77
 0394800753 |  16.95 |     4
(16 rows)

Notice that when connected as the barbara user, the SELECT statement from the stock_view is successful, while the stock table presents a Permission denied error.

Databases - Practical PostgreSQL
Previous Page Home Next Page

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