Re: Retrieving data using views & stored procedures. (doing it right?)

In article <3AD454A4.4DAE0B86_at_nospam.ford.com>, Charles McDonald <cmcdon12_at_nospam.ford.com> writes:
>Hi all
>
>We are about to embark on a new development where the dB will be 8i. Now
>I have read a few references about accessing data in the dB by use of
>stored procedures, functions and views rather than direct table access.
>
>What is the *right* way? Soley stored procedures & views, a hybrid or
>direct access to the tables?
>
>Cheers
> Charles

The correct answer is, "it depends."

If you have a table that needs to be accessed by a wide range of users but that also contains some columns whose content should not be visible to most of those users then one option is to make a view, which does not reference the sensitive columns, available instead of the table. Now you can say that the application screen that the users will have access to will only show them columns that they should be seeing. But if the user has any adhoc query or reporting tools, which are ever more common, then they can get around application security. By granting them access via a view you keep them away from the actual table.

When updates can come from many sources using stored code to perform the table inserts, updates, and deletes can prevent bad data because one application failed to follow one of the business rules. Also when a DML operation on one table requires several other related changes then by coding the unit of work into the procedure you can make it impossible for an application developer to write code that only performs half the transaction. And by codeing all the business logic in the database you remove complexity from the front-end product.

I am sure other posters can provide additional views on this topic.

• Mark D. Powell --
  • Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web ----- http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups NewsOne.Net prohibits users from posting spam. If this or other posts made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net