Q

Practical use for views in Oracle

What is the practical use for views in Oracle? Also what effect does FORCE/NO Force have in Oracle 8i? I shall be thankful for you help on these items.

Views are used for two things: to reduce complexity and to enhance security. Views can hide the complexity of your

query. I could have a query similar to the following:

SELECT d.dname,count(*) as NUM_EMPS
FROM emp e, dept d
WHERE e.deptno=d.deptno
GROUP BY dname;

Instead of typing that complex query (which really isn't that complex by today's standards) I could query a view as follows:

SELECT dname,num_emps FROM my_view;

You should be able to come up with some pretty complex queries that could be stored in a view to make querying much simpler. In this way, the view is sort of a macro, doing many things behind the scenes but making it look easy to the end user or application.

Views can also be used to enforce security. Let's assume that I only want the user BOB to see just the ENAME and DEPTNO columns of the EMP table. I could use the following:

GRANT select ON emp TO bob;

But the above would let BOB see the entire contents of the table. I could write a view with just the columns that BOB can see as follows:

CREATE VIEW bob_emp AS SELECT ename,deptno FROM emp; GRANT select ON bob_emp TO bob;

With these two commands, BOB can only see the two columns of this table through this view.

Let's take the security concept a step further. Let's suppose we want to let everyone query the EMP table, but only for their record. We could write a view as follows:

CREATE VIEW my_emp AS SELECT * FROM emp WHERE ename=USER; GRANT select ON my_emp TO public;

When a user queries from MY_EMP, it will only return those rows where the ENAME column contains their userid (defined by the USER psuedo column). The user is not granted SELECT privileges on the EMP table directly, but accesses that table through the view. This concept can be extended even further to support Virtual Private Databases (VPD).

This was first published in November 2004

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close