Re: Is this Roles?

Craig Morea wrote:

> Hi,
>
> I am a non-technical manager who needs to understand technical issues
> concerning database management (probably mostly Oracle) well enough to
> know what the tech-guys are talking about. If I can understand how it
> all works at the flowchart model level, it is not necessary that I
> understand how to code it. I apologize if this question is in the
> wrong place and would accept redirection if that is appropriate.
>
> The main issue I need to understand is a variation on roles-based
> access. There is quite a bit of information available on how systems
> use roles to grant or limit permissions, but I have not found what I
> am looking for. Since many examples focus on hospitals, I will make
> my example along the same lines:
>
> The general assumption seems to be that Doctors have more permissions
> than Nurses. This is fine. But both Doctors and Nurses always seem
> to have access to all the records in the hospital. I want to be able
> to restrict their access to the records of patients specifically
> assigned to them.
>
> Also, I'd like to be able to grant access to personnel data on
> employees, to the employee's supervisor, and also to his supervisor's
> supervisor, all the way up the chain, but not to anyone outside the
> chain. This appears to be partly a role issue, since supervisors can
> only see certain data, but it is also beyond roles, because the
> question is "who is supervisor of who?," and it gets worse when you
> want to add supervisor's supervisor, etc.
>
> So...I'm not looking for solutions (unless you happen to have one
> handy). But an assessment of whether these things are even possible
> and an explanation of where to start looking to tackle this kind of
> thing would be appreciated.
>
> Thanks,
>
> Craig

Roles are irrelevant to your question. What you are asking about is implemented using Row Level Security (using the DBMS_RLS built-in package) or Label Security which is a 'separate' install. I would expect based on my work with Hospital/Medical enterprises that row level security would be sufficient.

For a coded demo of this (as of Oracle 9i) you can go to: http://www.psoug.org
click on Morgan's Library
click on Row-Level Security

You will also find documentation on this at http://tahiti.oracle.com.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)