db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Nielsen <Thomas.Niel...@Sun.COM>
Subject Re: Derby database design issues
Date Fri, 03 Aug 2007 09:45:34 GMT
John C. Turnbull wrote:

>> - the database engine *may* be able to precompile the stored procedure,
>> and reuse that precompiled block at a later invocation of the stored
>> procedure for a (very slight) performance gain. Impact depends on the
>> nastyness of the expression and the engine itself.
>> This is no different from what you would get from a PreparedStatement
>> in the same engine though.
>>     
> [JCT] I guess then that if the procedure is being used heavily and multiple
> times there might be a real performance gain from using procedures over
> straight SQL.
>   
Most likely. There is really no need to use stored procedures, unless 
administrative ease is a necessity. You will get the same advantage (for 
sure) if you use straight SQL in a PreparedStatement that you keep a 
handle to in your jdbc client.
The purpose of PreparedStatements are just that - they are precompiled 
(or prepared if you like) - and ready to use to harvest the performance 
gain.

>>> 2.       Is it possible to disable direct access to tables and provide
>>>       
>>> the only access via procedures as it is in other RDBMS?
>>>       
>> Except using GRANT/REVOKE? I don't think so.
>> But you might be able to do it anyway - grant access to the table(s)
>> only to a "sqlproc" (or whatever) user, and then execute the stored
>> procedure as that user?
>>     
>
> [JCT] In Sybase what I would do would be to revoke access to all tables for
> a particular group to which all users of the system would belong.  Then I
> would grant access to the set of procedures to that same group.  The result
> was that all data manipulations were of a pre-defined type and it was
> impossible for a user to do anything undesirable.  I see that Derby doesn't
> seem to have the user group concept so to achieve the same effect every user
> would have to have their privileges to the tables and procedures set
> individually.  No big problem I guess but having groups would be nice.
>   
I'm still new to derby, but I think you are correct in that derby 
doesn't have usergroup access implemented (yet?).

Cheers,
Thomas

Mime
View raw message