DB2->PostgreSQL question on user permissions and views

Поиск
Список
Период
Сортировка
От Hans Meyer
Тема DB2->PostgreSQL question on user permissions and views
Дата
Msg-id 3D5B6A59.5020702@gmx.net
обсуждение исходный текст
Ответы Re: DB2->PostgreSQL question on user permissions and views  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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



В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: EPCOH: extract returns millisecs too?
Следующее
От: Richard Huxton
Дата:
Сообщение: Re: Migrating MySQL data to PostgreSQL