Re: Query table vs query Stored procedures/views???

"Daniel A. Morgan" <Daniel.Morgan_at_attws.com> wrote in message news:3B3CB835.6555E3FD_at_attws.com...
> Charles McDonald wrote:
>
> > Hello all
> >
> > Re: Run inserts and select querys directly on the database tables or
> > should all table access be via views /stored procedures?
> >
> > I have mentioned this ont this newsgroup before, but did not actually
> > come up with a definitive answer. I realise that it is more an 'it
> > depends' type of issue, but the application architecture is now better
> > known, and this may have impact.
> >
> > The application is to be an n-tier app developed in VB6.
> >
> > The client will be developed in VB and will access a Business Services
> > layer which will reside in COM+ on an application server. This Business
> > layer will apply business rules.
> > The Business services layer will in turn access a Data services layer,
> > which will reside inside COM+ on the same application server. The Data
> > services layer will be responsible for all database access, which will
> > include kicking off stored procedures. Since we wish to be able to use
> > the connection pooling facility of COM+ a single user ID will be used
> > for all dB access with security being applied in the middle tier.
> >
> > Keeping in mind that a primary goal of the architecture is ease of
> > maintanance and enhancement what approach should be followed?
> >
> > We could use the data services layer to build SQL strings as required,
> > and then use these directly on the tables, or we could call stored
> > procedures with parameters for the filters which then builds and
> > executes the query, returning the result to the data services layer.
> >
> > Conversly, when writing records to the dB we could use stored procedures
> > with a host array which does the inserts rather than firing off a host
> > of inserts from the data services layer.
> >
> > I kinda imagine a primary reason for limiting access to be via views and
> > stored procedures rather than direct table access is for data security.
> > This should not be an issue since the users do not have Oracle logins.
> > The feeling is that it will be complicating the issue if we need to keep
> > track of a large set of oracle objects.
> >
> > Later
> >
> > Charles
>
> I think this is more a question of religion than hard cold fact. And
> application security requirements weigh heavily in the decision making
> process.
>
> My answer would be as follows:
> Given no performance issues I would perform all table maintenance
 activities
> through procedures written in packages. When performance becomes an issue
> ... you make the compromises necessary to achieve adequate performance
> without compromising data integrity and security.
>
> Daniel A. Morgan
>

i'd have to agree with Daniel. you are going to get a variety of approaches... based more on personal bias and experience...

to achieve maximum availability, security, performance, and flexibility...

the application should connect to the database as a user OTHER than the owner of the objects, and be granted only the required privileges...

all DML (insert/update/delete) statements should be in PL/SQL packages. complex SELECT statements should also be stored in PL/SQL packages (functions that return result sets as ref cursors.) any other queries in the
application should be performed against views... or, actually, a private synonym that refers to a view...

i agree that this adds a level of "complexity" on the database, and that complexity is to be avoided... not avoided "at all costs"... but avoided where it does not provide any benefit...

as far as avoiding "vendor lockin" for the database, you've already selected several technologies that are available only from a single vendor that run only
on that vendor's platform...

again, as Daniel says, there are several factors that you need to weigh in making architecture decisions... but if you do not plan to use features like
views or PL/SQL packages, which factors led you to you choose Oracle as the RDBMS ? Received on Fri Jun 29 2001 - 22:21:38 CDT