Home » SQL & PL/SQL » SQL & PL/SQL » Data restriction at the column level
Data restriction at the column level [message #309482] Thu, 27 March 2008 12:40 Go to next message
ricupg
Messages: 2
Registered: March 2008
Junior Member
Hi, I would like to know if there is a way to select values from a table, but depending on the user role display the value or not. I'm thinking on using policy, but I can restrict the data at the row level.

Example:

SSN_Table
Name SSN
Joe Duh 111-11-2222
Me Ma 123-43-2222

If someone that should not see the SSN when they do the following select the SSN should come up blank.

Select Name, SSN from SSN_Table

Returns:
Name SSN
Joe Duh
Me Ma

We want to implement this type of security without changing sql scripts or creating view for them.

Hopefully someone can help.

Thank you
Re: Data restriction at the column level [message #309488 is a reply to message #309482] Thu, 27 March 2008 12:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Create a view and give access to the view not to the base table.

Regards
Michel
Re: Data restriction at the column level [message #309491 is a reply to message #309482] Thu, 27 March 2008 12:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Search the docs for column-level security

[Updated on: Thu, 27 March 2008 12:55]

Report message to a moderator

Re: Data restriction at the column level [message #309500 is a reply to message #309482] Thu, 27 March 2008 13:45 Go to previous messageGo to next message
ricupg
Messages: 2
Registered: March 2008
Junior Member
Hi Michel,

Creating new views is not a feasible solution because we would have to rewrite massive amounts of reports.

Frank,

I think your suggestion would work, I will try it out and let you know if it works.

Thank you for you quick replies. Very appreciated.
Re: Data restriction at the column level [message #309501 is a reply to message #309500] Thu, 27 March 2008 13:48 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Creating new views is not a feasible solution because we would have to rewrite massive amounts of reports.

Not at all, you create view with a new name and then create a synonym with the name of the table. Nothing change in your application if your application use individual accounts and not generic one.

Don't use VPD unless you are sure you can't do it with views.

Regards
Michel


Previous Topic: Code Road map
Next Topic: Compilation error
Goto Forum:
  


Current Time: Wed Dec 07 20:11:19 CST 2016

Total time taken to generate the page: 0.10834 seconds