Re: Can I replicate SELECT column permission without using multple views

From: RSH <RSH_Oracle_at_worldnet.att.net>
Date: Wed, 06 Mar 2002 17:46:57 GMT
Message-ID: <lCsh8.18094$gK2.1353734@bgtnsc04-news.ops.worldnet.att.net>

Create a set of VIEWS that select the differing sets of columns and grant these people SELECT on the VIEWS, not on the base tables; or more properly, create ROLES, grant those ROLEs the appropriate SELECTs on the views, and grant the appropriate ROLEs to the appropriate users.

This is a classic example of a very important, nay, fundamental reason for VIEWs to exist; allowing different users different VIEWs of the same data (hence the name, I guess).

So

TABLE EMP <-- ya gotta love it

SSN
LNAM
FNAM
MNAM
OFFICE_TELNO
TITLE_CODE
HOME_TELNO
CELL_TELNO

EMERG_CT_LNAM
EMERG_CT_FNAM
EMERG_CT_MNAM
EMERG_CT_WKNO
EMERG_CT_CELLNO
EMERG_CT_HOMENO
EMERG_CT_REL

SAL_GRADE

LAST_RAISE
CURR_SAL
LAST_BONUS_DATE
LAST_BONUS_AMT

you could

CREATE VIEW EMERG_SECURITY AS
SELECT EMP_LNAM, EMP_FNAM, EMP_MNAM, {and the office,home,cell #'s, and the EMERG stuff} <-NOT TYPING ALL THAT ;

as SYSTEM

CREATE ROLE EMERGENCY_CONTACT; as the table owner

GRANT SELECT ON EMERG_SECURITY to EMERGENCY_CONTACT;

and as system

create user id's for people in Company Medical, Security, etc; then GRANT EMERGENCY_CONTACT to them, ensure it is a default role;

So EMS, security people, company medical gets the info they need without digging into someone's SSN, what they make, etc.

Do the same thing with the other groups of people thinking what they should and should not be able to see.

Savvy?

RSH. "Lee Farrant" <leefarrant_at_hotmail.com> wrote in message news:97ad5817.0203060736.74047415_at_posting.google.com...
> Hi
>
> I have a customer that wants to give some users access to some columns
> and other users access to other columns on the same table.
>
> they cannot implement Column Permissions because this does not support
> the SELECT column permission.
>
> One restriction they have is that the end users must only reference
> one view or table. So there needs to be some logic under this table or
> view.
>
> Is there a clever way around his :-(
>
> Thanks Lee
>
> leefarrant_at_hotmail.com
Received on Wed Mar 06 2002 - 11:46:57 CST