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] Commented: (DERBY-4551) Allow database user to execute stored procedures with same permissions as database owner
Date Thu, 06 May 2010 22:31:49 GMT

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

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

Although my requirement is not to hide data structure from the end user, being able to specify
the security context in which SQL routines (stored procedures and functions) are executed
would improve SQL standard compliance and allow a security layer to be implemented by which
access to data (primarily inserts, updates, deletes / but also selects if needed) would only
be possible via SQL routines. When designing database applications, one rarely wants users
to have full permissions to access the tables in the database. Many applications are designed
to perform all database access through stored procedures (which have all been created by /
are owned by the database owner), and it is through the stored procedures application users
can access and update data. The procedures perform validations of business rules to protect
the integrity of the database. This concept would also work when accessing data with SQL Query
tools like IJ.

Raising questions about routine permission granting on the Derby mailing list, I was advised
by Dag Wanvik that "Derby routines execute with the invoker's current privileges. SQL has
a provision for defining routines to run with the definer's privileges as well, but this is
not yet implemented in Derby. Feel free to file an improvement request!". As I found this
existing request which I consider asking largerly for the same, I decided to add my comments
here.

Having looked at other data bases, to execute routines with the invoker's privileges seems
to be the default commonly found (at least in MS SQL 2005 and PostgreSQL). 
Note: For SQL Server I found the SQL text from Erland Sommarskog published at http://www.sommarskog.se/grantperm.html
very helpful. After having read this, it was sufficient to look at the create function statement
syntax for PostgreSQL to understand how ownership chaining is implemented there. I would have
liked to read the SQL standard on this, but am not sure where this can be found.
However these databases (MS SQL, PostgreSQL) in contrast to Derby also allow executing routines
with the definer's privilege. 

Two additional topics come to mind which I think need to be mentioned:
1) As logging the user who manipulated the data is also a frequent requirement, there would
also need to be a function made available which would unlike CURRENT_USER not return the name
of the user under whos privileges the SQL is executed (i.e. the database owner), but the session
user who is connected to the database / has called the routine (e.g. FredMeyer).
2) Not sure if there is a dependency on schema privileges. I noted when testing on PostgreSQL
that usage privileges need to be granted on the schema(s) - the grant statement in Derby has
not option allowing to grant permissions on schemas.

I will look into the workaround suggested by Rick above and might use it hoping for this request
to receive attention and support from the community to get implemented soon. (Unfortunately
I am not a programmer myself, so am afraid can't contribute more than raising the request
and potentially writing documentation on it.

Regards
Thomas

> Allow database user to execute stored procedures with same permissions as database owner
> ----------------------------------------------------------------------------------------
>
>                 Key: DERBY-4551
>                 URL: https://issues.apache.org/jira/browse/DERBY-4551
>             Project: Derby
>          Issue Type: Improvement
>    Affects Versions: 10.5.3.0
>            Reporter: Tushar Kale
>
> 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