[jira] Commented: (DERBY-4551) Allow database user to execute stored procedures with same permissions as database owner and/or routine definer

["Thomas Hill (JIRA)" <ji...@apache.org>]

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

Thomas Hill commented on DERBY-4551:
------------------------------------

I have tested using the daily build from 26th June. Regression testing, i.e. recreating all my database objects under 10.7.0.0 and embedding my current Java procedures in which I use a workaround of running a connect with hardcoded credentials of the data base owner at the beginning of each procedure (=> Connection conn =  DriverManager.getConnection("jdbc:derby:DB1;bootPassword=xxxx;user=dbo;password=zzzz;");) worked as expected, so everything continued to run fine. Then I have:
1) recreated the procedures with External Security Definer, 
2) changed the Java code to re-use an existing connection (=> Connection conn = DriverManager.getConnection("jdbc:default:connection");)  
3) created a 'normal' login/user without permissions on the (base) table, 
4) created a role 'data_updater', 
5) wrote a Java stored procedure to update the base table and created a corresponding stored procedure  when being logged in as database owner, 
6) granted permission to execute the procedure to role 'data_updater', 
7) granted role 'data_updater' to my 'normal' application user 
8) logged in as the 'normal' application user
9) set role to 'data_updater' and finally
10) tried to run the procedure
==> I am getting a SQLException that the user does not have update permissions on the (first column in the) table. 

Can you please advise if you are spotting anything I have forgotten or done incorrectly as I was expecting step 10 to succeed with the new feature being available in this build?


> 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
>
>
> 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.