Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Chris Fung
Frequent Advisor

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

Hi all,

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

1. View
2. Grant
Any performance impact in terms of the underlying index created for the table ? From my understanding if the index fields are not in the select criteria, then the query cannot enjoy the performance improvement from the underlying indexes.

So, I would suspect that the 2 methods described above should make no difference if the indexed field are not present / not granted to the user / not in the selection criteria of the query.

Appreciated for your comments.

Many thanks,

Chris
5 REPLIES
Deepak Extross
Honored Contributor

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

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
Honored Contributor

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

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
Honored Contributor

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

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Raynald Boucher
Super Advisor

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

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
Honored Contributor

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

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.