Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <6687629.285771284940172559.JavaMail.jira@thor>
Date: Sun, 19 Sep 2010 19:49:32 -0400 (EDT)
From: "Dag H. Wanvik (JIRA)" <jira@apache.org>
To: derby-dev@db.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
In-Reply-To: <1760926860.327031266376467896.JavaMail.jira@brutus.apache.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


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

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

Regressions ran OK. 

Uploading version followup-1b, which adds tests for the problem seen,
cf. the new stored procedure with definer's rights:
RoutinesDefinersRightsTest#updateWage.  This procedure exercises
ResultSet.insertRow, ResultSet.updateRow and ResultSet.deleteRow to
check that their nested SQL statement run with the proper SQL session
context.  Rerunning regressions.

Ready for review.


      was (Author: dagw):
    Regressions ran OK. Uploading version followup-1b, which adds
tests for the problem seen, cf. the new stored procedure with definer's rights:
RoutinesDefinersRightsTest#updateWage. This exercise ResultSet.insertRow,
ResultSet.updateRow and ResultSet.deleteRow to check that their nested SQL statement run with the proper SQL session context.
Rerunning regressions.

Ready for review.
  
> 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.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.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, derby-4551-followup-1b.diff, derby-4551-followup-1b.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.