Re: Disallow access to column of a table

From: Stephen Booth <stephenbooth.uk_at_gmail.com>
Date: Tue, 6 Oct 2009 08:12:14 +0100
Message-ID: <687bf9c40910060012n4ccebdb8yd1588c6466d7cdf8_at_mail.gmail.com>

On 05/10/2009, Jeffrey Beckstrom <JBECKSTROM_at_gcrta.org> wrote:
>
>
> We have a requirement to disallow access to a few columns of a table. Any
> suggestions on how to do this?

If it's just one table then a view is the way to go. If the column to be hidden is a non-mandatory one (or mandatory but has a default) then the view can even be updateble if needed. Give the users access to the view but not the table.

An alternative, which may be useful or necessary in some situations, might be to provide access through a package. That way you can control access via the code. This is a definite case of only do if there is a definite advantage. It would however resolve the issue Chet mentions of wanting users to be able to use information in a lookup or insert data into a table but not then be able to extract the data or only be able to extract it in a munged form. You could implement this in the front end application logic but by putting it in a database package you can make sure it's enforced if someone access the data using another tool.

Obviously all these precautions are only effective if users are not accessing the database as the table owner, i.e. they login as a different user which has been granted permissions on the table owners objects.

Stephen

-- 
It's better to ask a silly question than to make a silly assumption.

http://stephensorablog.blogspot.com/ |
http://www.linkedin.com/in/stephenboothuk | Skype: stephenbooth_uk

Apparently I'm a "Eierlegende Woll-Milch-Sau", I think it was meant as
a compliment.
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Oct 06 2009 - 02:12:14 CDT