Restrict user access to Oracle table by view v.s grant object privilege on column level

[Chris Fung]

Hi all,

Just wondering the performance impact on the method of restricting user access to Oracle tables through

1. View
2. Grant

[Deepak Extross]

A view is little more than a SELECT SQL. When you do a 'Select from my_view ...', the database engine actually does a select from the underlying table(s) based on the definition of the view.
Therefore, from the performance point of view, there is no difference whether you run a query against the view or against the columns of the table.

A view is normally used when you do not want to expose the table structure to the users. Or to shield your programs from changes in DB schema.

[Brian Crabtree]

The Indexes are generally only used when you choose a specific criteria in the where clause (ie: select * from orders where salesperson = 'SMITH') would use an index based on the "salesperson" column, while no 'where' clause (select * from orders) would perform a full table scan by default.

Views can be used to restrict access on a user basis, and are the more common ways to report data. Granting privileges would be required in some cases (there are updatable views as well) to allow data manipulation.

Overall, you are not going to see a performance impact using views, or having permissions set on a table for a user. At most, you might see a performance impact if you hardcode a HINT into the view definition.

Brian

[Yogeeraj_1]

hi Chris,

Views are a development tool, a thing of convienence. They provide a layer of abstraction.

I cannot tell you how many times people ask "how can I rename a column", "how can I change the order of the columns", "how can I add a new column in the 'middle' of a table".

If you EVER think you'll be asking those questions yourself someday, use views. Views let you do all of the above trivially.

If you want to put the complex SQL into views and have simple SQL in your apps -- use views (i use them ALOT in my apps).

A view is nothing more then a stored query. It will run no slower nor faster then a query directly against the base tables.

Hope this helps!
Best Regards
Yogeeraj

[Raynald Boucher]

Hello Chris.

As mentioned before, views are very practical and versatile.

I beleive howerver than they do carry a performance costs exactly for the reason specified. They are another query that must be executed and therefore use extra cycles.

Good planning and design are still the best tools.

Take care.

[Volker Borowski]

Hi Chris,

in terms of privileges, in times of odbc, you have to be very carefull with object privileges.
Haveing DELETE-permission on a table, means the user is just three mouseclicks away from deleting the entire table via an MS-Access ODBC-Link.

Do not underestimate the ingenious end-user :-)
If you have an application with application users defined on database level, I'll never give them DELETE on TABLE. Instead I'd give them EXECUTE on the DELETE_PROCEDURE that they might need.
Which could be
DELETE_WITH_KEY_GIVEN
or
DELETE_WITH_DATE_RANGE
or whatever they really need.

Otherwise, it could go like:
0) link table via ODBC in MS-ACCESS
1) click top-left-button in table grid (select all rows)
2) click delete
3) click yes

So it is not performance! It is:
"What can the user do with this privileges?", esp. if he does not use my application, but connects to the database with another tool !

Stuff to think about
Volker

PS: as far as performance is concerned, I think you are right.