[SQL] Label Security
I'm migrating an Oracle 9 database over to Postgres 7.3.4, and just ran into something I've never seen before (honestly, due to my lack of experience in Oracle) and was curious if Postgres supported anything similar. The DBA that set up Oracle appears to have enabled Oracle Label Security, which looks as though it offers per-row security levels. So, say we have the table 'test', user 'Nancy' does a "select * from test" and only will be shown rows she has permission to. Joe will get the same, and the superuser can see everything. Does Postgres offer anything like this, maybe even through third party software ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Label Security
On Mon, Jan 26, 2004 at 12:45:40 -0800, James Taylor <[EMAIL PROTECTED]> wrote: > I'm migrating an Oracle 9 database over to Postgres 7.3.4, and just ran > into something I've never seen before (honestly, due to my lack of > experience in Oracle) and was curious if > Postgres supported anything similar. The DBA that set up Oracle > appears to have enabled Oracle Label Security, which looks as though it > offers per-row security levels. So, say we have the table > 'test', user 'Nancy' does a "select * from test" and only will be > shown rows she has permission to. Joe will get the same, and the > superuser can see everything. Does Postgres offer anything like this, > maybe even through third party software You can do this with views, but there isn't a turn key set up to do this. You can give someone access to a view without giving them direct access to underlying tables. A view can check the current username versus some data in the table being displayed (perhaps joined with some other tables that keep track of group membership). ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] how to "enumerate" rows ?
I sent this to pgsql-general first but eventually figured this list is the more appropriate venue for asking for help. If this happens to be a FAQ item please briefly point me where to go or what search terms to use in the archive. > First of all, yes I know that result rows don't have any > intrinsic ordering that I can expect to not change. > > I have a table recording vaccinations for patients roughly > like this: > > table vaccinations > pk, > patient, > date_given, > disease > > Data in that table would look like this: > > 1, > 1742, > 2003-11-27 > tetanus > > 3, > 1742, > 2000-10-24 > flu > > 12, > 1742, > 2003-1-17 > tetanus > > Now, I need to enumerate the vaccinations per patient per > disease. They are intrinsically ordered by their date of > vaccination but I need to attach a number to them such that I > have: > > #1 > tetanus > 1, > 1742, > 2003-11-27 > > #2 > tetanus > 12, > 1742, > 2003-1-17 > > #1 > flu > 3, > 1742, > 2000-10-24 > > My plan was to select sub-sets by > > select > from vaccination > where patient=a_patient_id and disease=a_disease > order by date_given > > and then somehow cross (?) join them to a sub-set of the > integer table according to Celko's auxiliary integer table > technique (where I create the integer sub-set table by > > select val > from integers > where val <= > select count(*) > from vaccination > where > disease=a_disease and > patient=a_patient > > ) > > But I just can't figure out how to correctly do this... > > Note that I try to construct a view and thus don't have > constant values for a_disease and a_patient. > > Can someone please point me in the right direction ? > > Thanks, > Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Label Security
Ok, so for example, say I add another column to the tables I want to have the row-level security on called 'security'. I would go ahead and designate different security levels for each user, (ex. Jane security 1000, Bill 2000, Joe 3000). Then, if I only want X user with security 1000 to view Y row, I set Y.security to 1000. Then, I give these users no access to the table, and create views for EACH user saying something to the effect of "select * from z where security=securitylevel", and grant access to the views only to the user itself. Couldn't a user then go into the console themselves and create a view giving them full access to the table? Or, maybe I'm way off on this whole thing On Jan 26, 2004, at 1:06 PM, Bruno Wolff III wrote: On Mon, Jan 26, 2004 at 12:45:40 -0800, James Taylor <[EMAIL PROTECTED]> wrote: I'm migrating an Oracle 9 database over to Postgres 7.3.4, and just ran into something I've never seen before (honestly, due to my lack of experience in Oracle) and was curious if Postgres supported anything similar. The DBA that set up Oracle appears to have enabled Oracle Label Security, which looks as though it offers per-row security levels. So, say we have the table 'test', user 'Nancy' does a "select * from test" and only will be shown rows she has permission to. Joe will get the same, and the superuser can see everything. Does Postgres offer anything like this, maybe even through third party software You can do this with views, but there isn't a turn key set up to do this. You can give someone access to a view without giving them direct access to underlying tables. A view can check the current username versus some data in the table being displayed (perhaps joined with some other tables that keep track of group membership). ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] how to "enumerate" rows ?
Karsten Hilbert <[EMAIL PROTECTED]> writes:
>> Now, I need to enumerate the vaccinations per patient per
>> disease. They are intrinsically ordered by their date of
>> vaccination but I need to attach a number to them ...
The best, recommended way to do this is to plaster on the row numbers
in your client-side code. AFAIK there just isn't any way to do it in
standard SQL.
If you feel you really gotta have a server-side solution, the easiest
way is
CREATE TEMP SEQUENCE myseq;
SELECT nextval('myseq'), *
FROM (SELECT ... ORDER BY date_given) ss;
DROP SEQUENCE myseq;
(Alternatively, you can create a temp sequence once per session and just
reset it with setval() for each query.)
Note that you can *not* simplify this to
SELECT nextval('myseq'), ... ORDER BY date_given;
because if you do, the nextval() values may get computed before the
ORDER BY sorting occurs. Putting the ORDER BY into a sub-select makes
it work reliably.
This is not real portable since neither sequences nor ORDER BY in a
subselect are SQL-standard. It's also probably a good deal slower than
a simple client-side counter, because nextval() does a lot more than
just add one to a variable. But if you have to have it, there it is.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Label Security
On Mon, Jan 26, 2004 at 16:50:06 -0800, James Taylor <[EMAIL PROTECTED]> wrote: > Ok, so for example, say I add another column to the tables I want to > have the row-level security on called 'security'. I would go ahead and > designate > different security levels for each user, (ex. Jane security 1000, Bill > 2000, Joe 3000). Then, if I only want X user with security 1000 to > view Y row, I set Y.security to 1000. > Then, I give these users no access to the table, and create views for > EACH user saying something to the effect of "select * from z where > security=securitylevel", and grant access to the views only to the user > itself. You can refer to current_user in the view. This allows you to use one view for all users. Though you still have to have some information mapping users to rows they can access in the database. > Couldn't a user then go into the console themselves and create a view > giving them full access to the table? You don't have to give them access to the table directly in order to have the view work. When accessing a table through a view you have the rights of the person who created the view. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
