[
https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kim Haase updated DERBY-3200:
-----------------------------
Attachment: AuthExampleClientSQLAuth2.java
AuthExampleClientSQLAuth1.java
rdevcsecuresqlauthembeddedex.dita
Sorry about the long delay getting back to this. I've created a topic for the
example that shows SQL authorization with the embedded driver.
I've run into a problem, though, with the client driver. I'm trying to do
something similar to the two programs shown in the topic
http://db.apache.org/derby/docs/dev/devguide/rdevcsecureclientexample.html. I
have two programs similar to the ones in that example, except that the first
one sets sqlAuthorization to true along with the other properties, and creates
a new user.
In the second program, AuthExampleClientSQLAuth2.java, a user with full access
tries to grant the new user select and insert privileges. However, when I run
this program, I get an inexplicable error indicating that sqlAuthorization
isn't set, although it is. (I put in a debugging routine to display the values
of the properties just before I attempt the grant.) Here's a snippet of the
output showing the error:
Trying to connect to
jdbc:derby://localhost:1527/authClientDB;user=mary;password=little7xylamb
Connected to database authClientDB
Created table accessibletbl
Value of accessibletbl/textcol is hello
Reporting property values:
Value of requireAuthentication is true
Value of sqlAuthorization is true
Value of defaultConnectionMode is null
Value of fullAccessUsers is sa,mary
Value of readOnlyAccessUsers is guest
---SQLException Caught---
SQLState: 42Z60
Severity: -1
Message: GRANT not allowed unless database property
derby.database.sqlAuthorization has value 'TRUE'.
java.sql.SQLSyntaxErrorException: GRANT not allowed unless database property
derby.database.sqlAuthorization has value 'TRUE'.
at
org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.client.am.SqlException.getSQLException(Unknown
Source)
at org.apache.derby.client.am.Statement.executeUpdate(Unknown Source)
at AuthExampleClientSQLAuth2.main(AuthExampleClientSQLAuth2.java:92)
Caused by: org.apache.derby.client.am.SqlException: GRANT not allowed unless
database property derby.database.sqlAuthorization has value 'TRUE'.
at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
at
org.apache.derby.client.am.Statement.completeExecuteImmediate(Unknown Source)
The result is that the new user has the default full access and is able to
delete a row from the table.
I can't figure out why Derby doesn't know that sqlAuthorization is on. What is
wrong with the program? Any suggestions appreciated ...
> Developer's Guide: Add examples showing use of SQL authorization with user
> authentication
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3200
> URL: https://issues.apache.org/jira/browse/DERBY-3200
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Kim Haase
> Assignee: Kim Haase
> Priority: Minor
> Attachments: AuthExampleClientSQLAuth1.java,
> AuthExampleClientSQLAuth2.java, AuthExampleEmbeddedSQLAuth.java,
> rdevcsecuresqlauthembeddedex.dita
>
>
> This is the followup to DERBY-1823 that Francois Orsini suggested.
> I've been experimenting and reading the Developer's Guide section on SQL
> authorization (User authorizations, cdevcsecure36595).
> It appears that the only use of SQL authorization mode is to restrict user
> access, not to expand it.
> For example, if you set the default connection mode to noAccess, a user with
> fullAccess can't grant any privileges to a user with noAccess. And presumably
> if the default connection mode is readOnlyAccess, a user with fullAccess
> can't grant any privileges beyond SELECT, which the user has anyway.
> Only if the default connection mode is fullAccess is SQL authorization mode
> meaningful. That means that a fullAccess user can use GRANT to restrict
> another user's privileges on a particular database that the user owns.
> I'm running into a problem at the end, though. At the beginning of the
> program, as nobody in particular, I was able to create several users, some of
> them with full access. But at the end of the program, it seems that even a
> user with full access isn't allowed to turn off those database properties:
> Message: User 'MARY' does not have execute permission on PROCEDURE
> 'SYSCS_UTIL'.'SYSCS_SET_DATABASE_PROPERTY'.
> This seems a bit extreme. I know that with SQL authorization on, "the ability
> to read from or write to database objects is further restricted to the owner
> of the database objects." But the ability to execute built-in system
> procedures? Can I log in as SYSCS_UTIL? How?
> I realize that having access to SYSCS_SET_DATABASE_PROPERTY would allow me to
> in effect delete myself -- but that's essentially what I do at the end of the
> program that sets derby.connection.requireAuthentication but not
> derby.database.sqlAuthorization.
> The documentation does say that once you have turned on SQL authorization,
> you can't turn it off. But it doesn't say that you can't turn anything else
> off, either!
> I'll attach the program I've been using. Most of the stacktraces are
> expected, but I'm stumped by that last one.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
