Обсуждение: DB2->PostgreSQL question on user permissions and views

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

Hans Meyer <nospaming@gmx.net> writes:
> Postgres accepts the first view but gives a parsing error for the '.'
> in front of Main (i.e. in Normal.Main).

That's because we don't have schema support yet.  You should be able
to convert this code directly to PG when 7.3 comes out, but if you
want to run on 7.2 you will have to do a lot of work :-(

            regards, tom lane