SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-27-2008, 03:06 AM
| ||||
| ||||
 Newbie Query: Is this an appropriate place for a VIEW? greetings, all. Have base table with a confidentiality flag element. When flag = Y then certain other elements should not be displayed by casual SELECTors (null display). Can a VIEW be created and granted SELECT for casual users that accomplishes this? Would this be 'less expensive' than creating two separate base tables from source data, granted to more secure vs. casual users (current solution)? My gut says a VIEW is the way to do this, but I dont know how to place null values into an element based on the flag test, and I dont want to make a 'more expensive' application for the user. BTW - data is updated only twice a year, but accessed all the time. Feel like I have one foot nailed to floor and unable to move formward with project. . .      |
|
02-27-2008, 03:06 AM
| |||
| |||
| Re: Newbie Query: Is this an appropriate place for a VIEW? SteveInTallyFl wrote: > My gut says a VIEW is the way to do this, but I dont know > how to place null values into an element based on the flag > test, and I dont want to make a 'more expensive' application > for the user. Well, assuming that this table isn't gigantic (600 gbytes, etc) then i'd probably leave all the data in a single table and just use a view. If you really need to have the view return nulls then I think this could work for you: CREATE VIEW non_confidential_v ( customer_id , purchase_date , product_name , quantity , purchase_price ) AS ( SELECT customer_id , purchase_date , CASE confidentiality_flag WHEN 'Y' THEN NULL ELSE product_name END AS product_name , quantity , purchase_price ) ken |
|
02-27-2008, 03:06 AM
| |||
| |||
| Re: Newbie Query: Is this an appropriate place for a VIEW? SteveInTallyFl wrote: > greetings, all. > > > Have base table with a confidentiality flag element. When flag = Y then > > certain other elements should not be displayed by casual SELECTors > (null display). Can a VIEW be created and granted SELECT for casual > users that accomplishes this? Would this be 'less expensive' than > creating two separate base tables from source data, granted to more > secure vs. casual users (current solution)? > > > My gut says a VIEW is the way to do this, but I dont know how to place > null values into an element based on the flag test, and I dont want to > make a 'more expensive' application for the user. > > > BTW - data is updated only twice a year, but accessed all the time. > > > Feel like I have one foot nailed to floor and unable to move formward > with project. . . > A view sounds fine. To answer yoru question CASE WHEN confidential = 'Y' THEN c1 END AS c1 For the relevant columns. But why not simply ommit the columns in question from the view. Let's use a very typical example: As an employee of my company I can look up employees in our employee tables (we call this bluepages ;-). But I can only see public information. Thus the "public" view simply does not include salary, rank, bonus, .... The CASE expression esample gets more interesting when you want everyone to access the same view. Cheers Serge -- Serge Rielau DB2 SQL Compiler Development IBM Toronto Lab |
|
02-27-2008, 03:06 AM
| ||||
| ||||
| Re: Newbie Query: Is this an appropriate place for a VIEW? In this project this table/view serve as the demographic elements on an employee and are parent to two other tables of more changeable data. All employees are represented here. Those without the flag can have all their demographic detail disclosed. Those with the flag can have some demographic elements disclosed, but nothing that could lead to a personal identification. They exist side-by-side with those who can be personally identified. Omitting the columns for all employees is not an option. |
Linear Mode
