db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thomas Hill (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 Sat, 24 Jul 2010 09:11:49 GMT

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

Thomas Hill edited comment on DERBY-4551 at 7/24/10 5:10 AM:
-------------------------------------------------------------

I have now produced a repro (see file reproTH-derby-4551). Unfortunately all I can say (after
quite some hours of testing) is, that my repro sometimes works (= user Thomas@000 can insert
a new row into table rte."TBL_Clients" (on which he holds no permissions) via stored procedure
rte."SP_addClient" created as DBO with external security definer and sometimes not (then receiving
an error that user has no permission to update app."TBL_ApplIDs" from which I am fetching
the next available client ID). Note: I have included a call to the stored procedure incrementing
the ClientID into the addClient procedure as this is a concept I use for assigning next available
values to IDs, knowing that in the repro nothing is done with the result returned. The zipped
file also includes the database DB - selecting from rte."TBL_Clients" shows that the row was
inserted by Thomas@000. I hope the scripts included in the repro to reproduce the situation
are self-explanatory. Thanks again for your help and willingness to look into this.

      was (Author: thomashill):
    I have now produced a repro (see file reproTH-derby-4551). Unfortunately all I can say
(after quite some hours of testing) is, that my repro sometimes works (= user Thomas@000 can
insert a new row into table rte."TBL_Clients" (on which he holds no permissions) via stored
procedure rte."SP_addClient" created as DBO with external security definer and sometimes not
(then receiving an error that user has no permission to update app."TBL_ApplIDs" from which
I am fetching the next available client ID). Note: I have included a call to the stored procedure
incrementing the ClientID into the addClient procedure as this is a concept I use for assigning
next available values to IDs, knowing that in the repro nothing is done with the result returned.
The zipped file also includes the database DB - selecting from rte."TBL_Clients" shows that
the row was inserted by Thomas@000. I hope the scripts included in the repro to reproduce
the situation are self-explainator. Thanks again for your held and willingness to look into
this.
  
> 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, 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