DB2->PostgreSQL question on user permissions and views

From: Hans Meyer <nospaming(at)gmx(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: DB2->PostgreSQL question on user permissions and views
Date: 2002-08-15 08:46:17
Message-ID: 3D5B6A59.5020702@gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

hi

I am currently trying to port one of our java applications
(which I unfotunatly didn't write myself) from
IBM DB2 to PostgreSQL. However I am having some troubles with
user accounts/permissions in relation to Views and Aliases.

The program uses views like this one:

create view
Main
as select distinct
name,birthdate,sex,id,risNo,modality,studydatetime,freigabe,station,studyinstanceuid
from PATIENT,Modal where patParent=id;

create view
Super.Main
as select Main.*,
1 as login
from Main;

create view
Normal.Main
as select Main.* ,
1 as login
from Main
where station=any(select station from RECHTE where nutzer=USER);

create view
Notfall.Main
as select Main.*,
2 as login
from Main;

GRANT SELECT ON SUPER.MAIN TO SUPER;
GRANT SELECT ON NORMAL.MAIN TO NORMAL;
GRANT SELECT ON NOTFALL.MAIN TO NOTFALL;

I don't know DB2 very well but it seems that Super, Normal and Notfall
are local db user accounts and those views make sure that each user can
only access the data he's allowed to see.
This happens on DB level and is not visible in SELECT statements.
i.e. the applications just does a SELECT on Main and is db internally
redirected to the corresponding *.Main view for the current user.
=> if i connect as user Normal and do a SELECT * FROM Main; I will see
only some of the rows, user Super and Notfall would see all rows.

Postgres accepts the first view but gives a parsing error for the '.'
in front of Main (i.e. in Normal.Main).
I wonder if something as describes above is at all possible with
postgres without having to change the SQL client software.

THX!

PS: I am using PostgreSQL 7.2.1 on Debian 3.0

mfg
h. meyer

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Richard Huxton 2002-08-15 08:48:56 Re: Migrating MySQL data to PostgreSQL
Previous Message Tom Lane 2002-08-15 05:00:05 Re: EPCOH: extract returns millisecs too?