Re: Granting SELECT on specific COLS. How?

Juan Ho (se7107297_at_v9001.ntu.ac.sg) wrote:
: I want to create a table which allows everyone to see SOME columns
: only, and the rest are private to me. How would I go about doing this?
:
: I am using Vax Oracle 6.

I am not aware if oracle rdbms has this feature! But there is a workaround that may be of some help.

Create a *VIEW* object from your TABLE where it includes only your SELECTED (public) columns. Then GRANT SELECT privilege TO PUBLIC on your VIEW object.

Oh! BTW, view does not create a copy of the base table. It only stores the view definitions.

Example:

	Table Name : Creditor
	Columns    : creditor_name
                     creditor_id
                     credit_limit
                     credit_balance

	View Name  : Creditor_view
	Columns    : creditor_name
                     creditor_id

     	Note : credit_limit and credit_balance do not exist in creditor_view.

SQL SCRIPTS

	Create table creditor (
		creditor_name		varchar(60),
		credit_id		number(10),
		credit_limit		number(6,2),
		credit_balance		number(6,2)) ;

	Create view creditor_view (
		creditor_name,
		credit_id)
	AS Select creditor_name,credit_id from creditor ;

	Grant select on creditor_view to public ;

Hope this help.
cheers :)
jeff

--
=============================================================================
Jeff Cerrafon
cerrafon_at_iconz.co.nz      "Hard work makes a good idea possible"
+64 25 955762
=============================================================================