db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kim Haase (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-3200) Developer's Guide: Add examples showing use of SQL authorization with user authentication
Date Tue, 29 Jan 2008 20:58:34 GMT

     [ 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

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

View raw message