db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas <Thomas.K.H...@t-online.de>
Subject Re: SQL authorisation and routine permissions
Date Tue, 04 May 2010 06:03:17 GMT
Do I understand correctly that, if my procedures had been defined by the
database owner (which they were in my case), then additional grants would not be
required => would Derby support running routines with definer's privileges? If
so, I would file an improvement request.

For the time being, is there any possibility to achieve the following:
1) I do NOT want any user to have direct access to data in my base tables.
2) the ability to READ data off tables is handled by the definition of VIEWS and
granting rights on these views to roles/users. This works fine.
3) However I also want to prevent people from connecting to the database
directly and issuing data manipulation statements (insert, update, delete) as I
would like to have full control over database content. Therefore I have written
stored procedures that, for each object/table, for example handle the insertion
of data (e.g. a proc SP_addClient to insert into TBL_Clients). Within this
procedure I do include business logic that ensures that certain fields are only
populated in adherence to certain business rules. For example when inserting a
german client I want a VAT field filled with 19%. If I now need to grant insert
rights on the underlying base table to the role/user as well, I do open up the
possibility for a user using - let's say IJ - to (intentionally or not) fill the
VAT field with a different value than 19% although setting-up a german client.
This is just a simple example of business logic made up here to illustrate and I
understand I could probably prevent this set-up mistake from happening with a
check constraint. However I do think this brings across why I want only
procedures to change my data.
4) I have looked into whether it is possible to grant the stored procedure
insert rights on the table to prevent the error message that the user is not
authorised to insert into the table from being thrown. Although I could issue
the command 'GRANT INSERT ON table "TBL_Clients" to "SP_addClient" without
receiving an error, the situation remained unchanged (the user still gets the
error that he is not allowed to insert data [obviously I had granted the user
the rights to execute the procedure upfront and set his role accordingly before
calling the procedure].

Any tips?


View raw message