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

I've wrestled with the problem in a number of applications.

My fast answer now is create views and grant to them, especially if you've got Oracle 7 because you can use the create or replace option that will preserve the Grants to the view if you've changed the viewed table.

The Create Views method has the advantage that you can change the names of the Base tables let's say to include a version number like a table named EMP_V2 then Create View EMP as select * from EMP_V2. Now, developers can work with a EMP_V3 as a next version. When time comes to release it just Drop View EMP and Create View EMP as select * from EMP_V3. If you screw up you can then just fall back to EMP_V2.

Some disavantages are that you've got another layer to keep track of and some scripting to do to simplify the process for each user. I haven't really found much of a performance disadvantage but my databases are smaller in the 10 to 100 thousand row range.

You can also use Create Synonyms to control access.

-ed Received on Wed Jul 12 1995 - 00:00:00 CEST