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
|
Список | 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 по дате отправления: