[
https://issues.apache.org/jira/browse/DERBY-4551?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12864952#action_12864952
]
Thomas Hill commented on DERBY-4551:
------------------------------------
Although my requirement is not to hide data structure from the end user, being
able to specify the security context in which SQL routines (stored procedures
and functions) are executed would improve SQL standard compliance and allow a
security layer to be implemented by which access to data (primarily inserts,
updates, deletes / but also selects if needed) would only be possible via SQL
routines. When designing database applications, one rarely wants users to have
full permissions to access the tables in the database. Many applications are
designed to perform all database access through stored procedures (which have
all been created by / are owned by the database owner), and it is through the
stored procedures application users can access and update data. The procedures
perform validations of business rules to protect the integrity of the database.
This concept would also work when accessing data with SQL Query tools like IJ.
Raising questions about routine permission granting on the Derby mailing list,
I was advised by Dag Wanvik that "Derby routines execute with the invoker's
current privileges. SQL has a provision for defining routines to run with the
definer's privileges as well, but this is not yet implemented in Derby. Feel
free to file an improvement request!". As I found this existing request which I
consider asking largerly for the same, I decided to add my comments here.
Having looked at other data bases, to execute routines with the invoker's
privileges seems to be the default commonly found (at least in MS SQL 2005 and
PostgreSQL).
Note: For SQL Server I found the SQL text from Erland Sommarskog published at
http://www.sommarskog.se/grantperm.html very helpful. After having read this,
it was sufficient to look at the create function statement syntax for
PostgreSQL to understand how ownership chaining is implemented there. I would
have liked to read the SQL standard on this, but am not sure where this can be
found.
However these databases (MS SQL, PostgreSQL) in contrast to Derby also allow
executing routines with the definer's privilege.
Two additional topics come to mind which I think need to be mentioned:
1) As logging the user who manipulated the data is also a frequent requirement,
there would also need to be a function made available which would unlike
CURRENT_USER not return the name of the user under whos privileges the SQL is
executed (i.e. the database owner), but the session user who is connected to
the database / has called the routine (e.g. FredMeyer).
2) Not sure if there is a dependency on schema privileges. I noted when testing
on PostgreSQL that usage privileges need to be granted on the schema(s) - the
grant statement in Derby has not option allowing to grant permissions on
schemas.
I will look into the workaround suggested by Rick above and might use it hoping
for this request to receive attention and support from the community to get
implemented soon. (Unfortunately I am not a programmer myself, so am afraid
can't contribute more than raising the request and potentially writing
documentation on it.
Regards
Thomas
> Allow database user to execute stored procedures with same permissions as
> database owner
> ----------------------------------------------------------------------------------------
>
> Key: DERBY-4551
> URL: https://issues.apache.org/jira/browse/DERBY-4551
> Project: Derby
> Issue Type: Improvement
> Affects Versions: 10.5.3.0
> Reporter: Tushar Kale
>
> Curretnly there is no way to hide data and database structure in embedded
> derby from the end user.
> One way to accomplish the above requirement is as follows:
> 1. Create encrypted database so data is protected
> 2. Enable authentication and sql authorization in database
> 3. Create two users, dbUser and dbOwner
> 4. Store application logic as stored procedure in the databse so dbUser does
> not know what tables are accecced by the application logic, thus hiding table
> structure
> 5. Revoke select permission from dbUser so he cannot describe tables thus
> protecting table structures
> 6. Give only Execute permissions on stored procedures to dbUser
> The above steps will ensure that data and data structure is hidden when
> application is delivered to end user.
> The problem is, if user does not have select permission, the stored
> procedures will not execute. So I am requesting the following enhancement to
> Derby:
> If dbOwner has given Execure permission to stored procecure to a dbUser, then
> allow stored procedure to execute even if the dbUser has no select
> permission.
> In otherwords, When dbUser calls stored procedure, database will use dbOwners
> authorization to execute stored procedure rather than dbUsers.
> This may be implemented by creating new permission called RunAsDbOwner.
> DbOwner can then grant permission to dbUser to execute a stored procedure
> with RunAsDbOwner.
> If this is implemented, applications can be created which will truely hide
> the database structure and data from end users. Database will behave as a
> blackbox with only in/out data exposed in stored procedures.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
