db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] Issue Comment Edited: (DERBY-4551) Allow database user to execute stored procedures with same permissions as database owner and/or routine definer
Date Sun, 19 Sep 2010 17:32:32 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4551?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12912261#action_12912261
] 

Dag H. Wanvik edited comment on DERBY-4551 at 9/19/10 1:30 PM:
---------------------------------------------------------------

Attaching a patch (followup-1a) which fixes the issue seen. It problem doesn't have
to do with the implementation of definer's rights *per se*, but showed
up in this case due to the feature's heavy dependence of the
correctness of the SQL session context introduced in DERBY-3327.

The problem here is that the substatement executed as part of
ResultSet.{insertRow, updateRow,deleteRow} pushes a new statement
context. This statement context is consulted when constructing the
activation for the substatement, to see if the activation shall have a
parent activation (which is used to get the correct SQL session
context),
cf. GenericLanguageConnectionContext#getCurrentSQLSessionContext.

However, the newly pushed statement context was missing its parent's
activation, so the substatement instead get the top level session
context, whose current user is not the DEFINER (in this case instead
of "DBO" it was "Thomas"), cf
BaseActivation#setupSQLSessionContextForChildren, hence the
authorization error.

The patch makes sure the nested statement context initially gets the
(new) parent context set.

The repro now works with this patch. Running regression tests.

The patch is not for commit, I need to add new regression tests for
this case.


      was (Author: dagw):
    Attaching a patch (followup-1a) which fixes the issue seen. It problem doesn't have
to do with the implementation of definer's rights *per se*, but showed
up in this case due to the feature's heavy dependence of the
correctness of the SQL session context introduced in DERBY-3327.

The problem here is that the substatement executed as part of
ResultSet.{insertRow, updateRow,deleteRow} pushes a new statement
context. This statement context is consulted when constructing the
activation for the substatement, to see if the activation shall have a
parent activation (which is used to get the correct SQL session
context),
cf. GenericLanguageConnectionContext#getCurrentSQLSessionContext.

However, the newly pushed statement context was missing its parent's
activation, so the substatement instead get the top level session
context, whose current user is not the DEFINER (in this case instead
of "DBO" it was "Thomas"), cf
BaseActivation#setupSQLSessionContextForChildren, hence the
authorization error.

The patch makes sure the nested statement context initially get the
(new) parent context set.

The repro now works with this patch. Running regression tests.

The patch is not for commit, I need to add new regression tests for
this case.

  
> Allow database user to execute stored procedures with same permissions as database owner
and/or routine definer
> ---------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-4551
>                 URL: https://issues.apache.org/jira/browse/DERBY-4551
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.5.3.0
>            Reporter: Tushar Kale
>            Assignee: Dag H. Wanvik
>         Attachments: definers_rights.html, definers_rights.html, definers_rights.html,
definers_rights.html, definers_rights.html, definers_rights.html, definers_rights.html, definers_rights_typos-1.diff,
derby-4551-1.diff, derby-4551-1.stat, derby-4551-1.txt, derby-4551-2.diff, derby-4551-2.stat,
derby-4551-3.diff, derby-4551-3.stat, derby-4551-3b.diff, derby-4551-3b.stat, derby-4551-4.diff,
derby-4551-4.stat, derby-4551-followup-1a.diff, derby-4551-followup-1a.stat, reproTH-derby-4551.7z
>
>
> 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.


Mime
View raw message