Re: Need advice, Grant access to View or Table?

setiawan_at_singnet.com.sg (Eddy Setiawan) writes:

>I need to set-up an Oracle database for our in-house application
>development. My question is, should I:
 

> 1. Grant access directly to the base tables, or
> 2. Create views for the tables and grant access to views.

There probably aren't many advantages to creating views and granting access to them. (Just because I can't think of any doesn't mean someone else won't have some good ones, though.) One disadvantage is that you'll have to recreate the views every time you add or modify a column to propogate the revised table to your developers. (use 'create or replace view ...') In my experience the performance hit for non-joined views is minimal, so don't worry about it.

Assuming that there are multiple developers that will be sharing tables, a better solution might be to have all tables that will be shared owned by a 'master' account, with public synonyms and grants through roles. (I'm assuming Oracle 7 here.)

One advantage this has is you can control who can do what more closely, and using roles makes it easy to grant or deny access to groups of tables as projects and tasks change.

It also makes it easy to substitute another table for the real one whenever needed for testing by creating a private synonym for the user(s) who need the test version.

---
Michael Nolan, Sysop for the DBMS RoundTable on GEnie
nolan_at_tssi.com, dbms_at_genie.com, nolan_at_inetnebr.com
(posted from nolan_at_helios.unl.edu)