RE: Disallow access to column of a table

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Mon, 5 Oct 2009 16:07:59 -0400
Message-ID: <D1DC33E67722D54A93F05F702C99E2A9046EE082_at_usahm208.amer.corp.eds.com>

With 10gR2 and probably R1 VPD can be applied at the column level.  

From Security manual >>
14.1.1.1 Column-Level VPD

Column-level VPD enables you to enforce row-level security when a security-relevant column is referenced in a query. You can apply column-level VPD to tables and views, but not to synonyms. By specifying the security-relevant column name with the sec_relevant_cols parameter of the DBMS_RLS.ADD_POLICY procedure, the security policy is applied whenever the column is referenced, explicitly or implicitly, in a query. <<

For 9.2 and earlier using a view as Mark Boback suggested is about your only means to limit user access to column data when the use has table privileges other than doing so in the application logic.

• Mark D Powell -- HP Enterprise Services Phone (313) 592-5148

  ---

        From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman

	Sent: Monday, October 05, 2009 3:53 PM
	To: JBECKSTROM_at_gcrta.org; oracle-l-freelists; oracle-db-l
	Subject: Re: Disallow access to column of a table
	
	
	Jeff,
	 
	You have to use a view to restrict columns.
	 
	If permissions or synonyms won't do the job correctly, you can

use VPD to restrict a particular community of users from accessing the table, and permit them to use the view instead, and vice-versa.          

        Hope this helps!          

        -Tim          

		-----Original Message-----
		From: Jeffrey Beckstrom [mailto:JBECKSTROM_at_gcrta.org]
		Sent: Monday, October 5, 2009 01:35 PM
		To: 'oracle-l-freelists', 'oracle-db-l'
		Subject: Disallow access to column of a table
		
		
				We have a requirement to disallow access

to a few columns of a table. Any suggestions on how to do this? I was thinking of Virtual Private Database but that would exclude the entire row.                  

		Jeffrey Beckstrom
		Database Administrator
		Greater Cleveland Regional Transit Authority
		1240 W. 6th Street
		Cleveland, Ohio 44113

			

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Oct 05 2009 - 15:07:59 CDT