Ask the Expert

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.

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: