[
https://issues.apache.org/jira/browse/DERBY-4551?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12920303#action_12920303
]
Thomas Hill commented on DERBY-4551:
------------------------------------
Would need advice on a related question:
At this stage I have hard coded a connection string opening a new connection as
'dbo' into my stored procedures (i.e. implemented the work around as per Rick's
advice) which I intend to remove once this feature becomes available. Recently
I have moved my network server to an environment hosted by a java hoster on the
internet. This network server uses SSL and authentication. SSL encryption is
based on certificates which were self generated using the keytool utility. The
network server is started with default/simple security policy and with
references to the keystore file on the command line. I am able to connect to
the server and issue SQL commands (e.g.selects, inserts) via IJ just fine when
starting IJ via a reference to the clientkeystore file and connecting with
ssl=basic. However when trying to call my stored procedures I am getting
security errors.
Questions:
1) on my hard coded connection strings at the start of each procedure do I need
to specify ssl=basic or is ssl not relevant in this context as the code is
executed within the server? When trying the connect with ssl=basic I am getting
'access denied (java.util.PropertyPermission javax.net.ssl.keyStore write):
java.security.AccessControlException' - my hoster tells me properties are set
to allow read,write access, i.e. permission java.util.PropertyPermission
"*","read,write" is in place;
a) do I also need the java code System.setProperty("javax.net.ssl.keyStore",
vcKeyStoreFileName) and System.setProperty("javax.net.ssl.keyStorePassword",
vcKeyStorePassword) prior to the connect?; - tried this as well, but no success.
2) if I ommit the ssl parameter completely, I am getting a different err:
'access denied (java.net.SocketPermission 127.0.0.1:31540 connect,resolve):
java.security.AccessControlException', again the property file setting is
permission java.net.SocketPermission "*","accept, connect, listen, resolve"
Not sure what the right approach would be. Hope my explanation was clear enough
and someone can offer some advice.
Thanks
> 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
> Fix For: 10.7.0.0
>
> 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,
> derby4551-trial.diff, 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.
