Thread: Restricted query
Hello list, This is an inventory system where some users are restricted to view only certain suppliers, those restrictions are in a table called pedusers (username,supplierallowed), there are other kind of "superuser" who are not restricted, actually I created another table for them (userexcept). To select restricted rows I use this code: select * from prvdor where prvtiprov = 2 and prvpk in (select usr_prvdor from peduser where usr_login = 'default') I think I would write this in a return set function instead but I would like to know others ways to write a SQL sentence to return the rows according users permission/restrictions. Thanks in advance for your comments -- Sinceramente, Josué Maldonado. "Vivir sin filosofar es, propiamente, tener los ojos cerrados, sin tratar de abrirlos jamás." -- Descartes
You could use a view and give group rights to a particular view. Josué Maldonado wrote: > Hello list, > > This is an inventory system where some users are restricted to view only > certain suppliers, those restrictions are in a table called pedusers > (username,supplierallowed), there are other kind of "superuser" who are > not restricted, actually I created another table for them (userexcept). > To select restricted rows I use this code: > > select * from prvdor > where prvtiprov = 2 and > prvpk in > (select usr_prvdor from peduser where > usr_login = 'default') > > > I think I would write this in a return set function instead but I would > like to know others ways to write a SQL sentence to return the rows > according users permission/restrictions. > > > Thanks in advance for your comments > > >
Marco Lazzeri wrote: > And what about restricting UPDATE/DELETE queries too. Can I still use > VIEWs? No but that you could use a function for. Sincerely, Joshua D. Drake > > Bye > > Il mer, 2004-05-19 alle 05:06, Joshua D. Drake ha scritto: > >>You could use a view and give group rights to a particular view. >> >>Josué Maldonado wrote: >> >> >>>Hello list, >>> >>>This is an inventory system where some users are restricted to view only >>>certain suppliers, those restrictions are in a table called pedusers >>>(username,supplierallowed), there are other kind of "superuser" who are >>>not restricted, actually I created another table for them (userexcept). >>>To select restricted rows I use this code: >>> >>>select * from prvdor >>>where prvtiprov = 2 and >>>prvpk in >>>(select usr_prvdor from peduser where >>>usr_login = 'default') >>> >>> >>>I think I would write this in a return set function instead but I would >>>like to know others ways to write a SQL sentence to return the rows >>>according users permission/restrictions. >>> >>> >>>Thanks in advance for your comments >>> >>> >>> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 2: you can get off all lists at once with the unregister command >> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >>
And what about restricting UPDATE/DELETE queries too. Can I still use VIEWs? Bye Il mer, 2004-05-19 alle 05:06, Joshua D. Drake ha scritto: > You could use a view and give group rights to a particular view. > > Josué Maldonado wrote: > > > Hello list, > > > > This is an inventory system where some users are restricted to view only > > certain suppliers, those restrictions are in a table called pedusers > > (username,supplierallowed), there are other kind of "superuser" who are > > not restricted, actually I created another table for them (userexcept). > > To select restricted rows I use this code: > > > > select * from prvdor > > where prvtiprov = 2 and > > prvpk in > > (select usr_prvdor from peduser where > > usr_login = 'default') > > > > > > I think I would write this in a return set function instead but I would > > like to know others ways to write a SQL sentence to return the rows > > according users permission/restrictions. > > > > > > Thanks in advance for your comments > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >