Problem solve
Get help with specific problems with your technologies, process and projects.
Best way to restrict user access to columns in a table
Just wondering which of the following methods is better in restricting users from accessing the confidential columns in a table.
- Create a view to hide those columns and then grant select on the view to the users.
- Just grant select on the ordinary columns on the table to users directly.
The answer all depends on how complex your SQL statement is. Either method works fine for most cases. But once in a while, when a view is used a complex SQL statement (i.e. in a join), it doesn't always perform as one would hope. Using Cost Based Optimization can go a long way towards helping you out. In either case, make sure that you test your method in your application.
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
