Hi,
I'm setting up a system to allow certain users to see only certain
records via a views. Thus, I revoked all permissions on the original
tables, I set up some views that handle the filtering on the records
using an account key, and these views I used to produce user views on
which permissions are granted to select, update , ...
In short, there are three layers of relations:
1. fysical tables: only access by sysadmin
ARE USED BY
2. administrative views: only access by sysadmin
ARE USED BY
3. user views: access to users depending on function
The problem is, that e.g. when I try to do a select on a user view, on
which a certain user has the permissions to select, I get an exception
that tells me the user has no rights to access to underlying
administrative views!
So from this behaviour, either I must have completely misunderstood the
authorization system, or their must be a bug in the system?
The following link drove me into the direction of seting the system up
like this.
http://www.archonet.com/pgdocs/chap-access.html#RESTRICT-USERS
Any comments will be greatly appreciated,
Lieven
A short example:
/* table to link uid to administrative accounts */
CREATE TABLE adm_user (login char(20), admin char(20));
/* sample base table */
CREATE TABLE base (admin char(20), data text);
/* sample administrative view */
CREATE VIEW adm_base AS
SELECT b.data
FROM base b, adm_user u
WHERE
(b.admin = u.admin) AND (u.login = bpchar(current_user))
;
/* rules to manipulate adm_base - omitted */
/* sample user view */
CREATE VIEW usr_base AS
SELECT * FROM adm_base;
After setting up the adm_user table, granting permissions on usr_base
to a user, and connecting to the DB as that user, I get
SELECT * FROM usr_base;
Error: adm_base: permission denied.
Of course, loosing the permissions on the adm_base view or base table
could solve this issue, but the point was security in implementing this
system!