Re: SQL Filtering

Daniel's right, of course, but perhaps a bit more direction is in order. Look for the references to sys_context along with the phrase "fine-grained access control." Chapter 11 of the "Oracle8i Application Developer's Guide - Fundamentals", Release 2 (8.1.6), Oracle part number A76939-01 has some real interesting reading.

Tom

Daniel Morgan wrote:

> Ben Heath wrote:
>
> > I have a problem, that I'm sure someone has had to solve before. I have a
> > client table and I want to allow users access to only certain classes of
> > clients. In my example I have 'OrdinaryClients' and 'ImportantClients'. If
> > I want to filter a query so that certain users can only see
> > 'OrdinaryClients' what's the best way to do this.
> >
> > This is what I am currently doing, but the queries are very slow as the
> > table sizes increase. When an index is added to Client.clientNumber it's
> > not being used if the resultset returned from Userprofile is larger than
> > around 10 to12 clients. Any ideas?
> > Table:
> >
> > Client
> > ------
> > clientNumber varchar(6)
> > clientName varchar(20)
> > clientDesc varchar(60)
> > otherClientInfo varchar(120)
> > .
> > .
> > .
> >
> > UserProfile
> > -----------
> > profileName varchar(20)
> > clientNumber varchar(6)
> > .
> > .
> > .
> >
> > User
> > ----
> > userName varchar(20)
> > password varchar(20)
> > profileName varchar(20)
> > .
> > .
> > .
> >
> > User
> > ----
> > 'ben', 'xxxx', 'OrdinaryClients'
> >
> > UserProfile
> > -----------
> > 'ImportantClients', '111111'
> > 'ImportantClients', '222222'
> > 'ImportantClients', '333333'
> > 'ImportantClients', '444444'
> > 'OrdinaryClients', '555555'
> > 'OrdinaryClients', 666666'
> > 'OrdinaryClients', '777777'
> >
> > Client
> > ------
> > '111111', 'abc Company', ...
> > '222222', 'def Company', ...
> > '333333', 'ghi Company', ...
> > '444444', 'jkl Company', ...
> > '555555', 'mno Company', ...
> > '666666', 'pqr Company', ...
> > '777777', 'stu Company', ...
> >
> > select clientName from Client where client.clientNumber in
> > (select clientNumber from userProfile where profileName = 'OrdinaryClients')
>
> SYS_CONTEXT.
>
> You can find information at http://tahiti.oracle.com
>
> Daniel Morgan
Received on Fri Aug 02 2002 - 11:59:43 CDT