Re: masking column values

chopras_at_gmail.com wrote:

> Well maybe I was not clear:
> What I want accomplished is the following:
>
>
>
> Say there are 3 rows in the database:
>
> Name Job Dept SAL
> Jones Clerk 10 5000
> Smith Analyst 20 10000
> Ford Consultant 10 20000
>
> Say user Smith issues the query on the emp_clm (as define above) such
> as
>
> select * from emp_clm,
>
> I want Smith to only view his salary and nothing else. So I want the
> result of a query to return:
>
>
> Name Job Dept SAL
> Jones Clerk 10
> Smith Analyst 20 10000
> Ford Consultant 10
>
> Note that Jones' and Ford's Sal information is missing (the SAL column
> has been masked)
>
> One way to do this is using the decode command in the view definition
> for emp_clm
>
> create ore place view emp_clm
> as
> select name, id, dept,
> DECODE(name, USER, sal, NULL) sal
> from emp
>
> However, the DECODE or CASE WHEN statements are written in the Select
> clause of queries. I want to accomplish the same thing but provide the
> column masking using the WHERE clause of a query. I do not think this
> is actually possible, but if anyone knows how, that would be great.
>
> ----------------------------------------------------------------------------
> Additionally I wanted to know if the CASE WHEN clause in SQL is
> powerful enough to include queries. In general is it possible to do
> something like:
>
> select name, id, dept
> case when
> 5000 = select sal from emp_clr
> then
> sal
> else
> null
> end as sal
> from emp_clr
>
> In other words, can case statements include queries as part of the when
> clause.
>
>
> Thanks...
>
>
> PS: This is not a homework :)

Simple solution if that is all you want:

exec dbms_application_info.set_client_info('747');

DECLARE x VARCHAR2(100);

BEGIN
   dbms_application_info.read_client_info(x);    dbms_output.put_line(x);
END;
/

CREATE OR REPLACE FUNCTION app_info_wrapper RETURN VARCHAR2 IS x VARCHAR2(64);

BEGIN
   dbms_application_info.read_client_info(x);    RETURN x;
END app_info_wrapper;
/

CREATE OR REPLACE VIEW airplanes_view AS SELECT *
FROM airplanes
WHERE program_id = app_info_wrapper;

SELECT * FROM airplanes_view;

exec dbms_application_info.set_client_info('777');

SELECT * FROM airplanes_view;

The full demo is at http://www.psoug.org click on Morgan's Library
click on DBMS_APPLICATION_INFO

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