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] Commented: (DERBY-3200) Developer's Guide: Add examples showing use of SQL authorization with user authentication
Date Fri, 04 Apr 2008 13:55:24 GMT

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

Kim Haase commented on DERBY-3200:
----------------------------------

Thanks, Dag! No problem with the delay -- I had heard you were away, and this is not such
an urgent issue. I played some more with the programs a few weeks ago but made no further
progress, so I'm glad you are back.

Thanks for the clarifications. They help A LOT! I am afraid I still need some more help, though.


I was puzzled by one thing in the auth2.log showing the execution of the second program: why
did it let you connect without a username or password, when the first program set the default
connection mode to noAccess? The embedded program reports "Connection authentication failure"
in that situation, I think.

It looks as if for the SQL authorization programs it might be better to go with the default
fullAccess connection mode, so that it is easier to show what is being restricted. Not even
being able to set or display the database properties is rather inconvenient!

Another question -- when you say I should reboot the database after setting derby.database.sqlAuthorization,
does that mean I should stop and restart the Network Server after I run the first of the two
client programs? If so, I'll do that. However, I haven't gotten there yet! The first one isn't
working for me any more.

To check that things were still working the way they used to, using Derby 10.3.2.1, I ran
the existing embedded authentication example first, AuthExampleEmbedded.java, the one shown
in "User authentication and authorization embedded example" (http://db.apache.org/derby/docs/dev/devguide/rdevcsecure26537.html).
It ran as expected. Then I ran my new embedded example using SQL authorization, AuthExampleEmbeddedSQLAuth.java,
and it also ran as expected. 

Then I started the Network Server and tried running the existing authentication client example,
AuthExampleClient1.java, the one shown in "User authentication and authorization client example"
(http://db.apache.org/derby/docs/dev/devguide/rdevcsecureclientexample.html). It gave the
following error the first time I tried to set a database property:

java -cp ${CLASSPATH}:${DERBY_HOME}/lib/derbyclient.jar AuthExampleClient1
org.apache.derby.jdbc.ClientDriver loaded.
Trying to connect to jdbc:derby://localhost:1527/jdbcDemoDB;create=true
Connected to database jdbc:derby://localhost:1527/jdbcDemoDB;create=true
Turning on authentication.

---SQLException Caught---

SQLState:   42504
Severity: -1
Message:  User 'APP' does not have execute permission on PROCEDURE 'SYSCS_UTIL'.'SYSCS_SET_DATABASE_PROPERTY'.
java.sql.SQLSyntaxErrorException: User 'APP' does not have execute permission on PROCEDURE
'SYSCS_UTIL'.'SYSCS_SET_DATABASE_PROPERTY'.
        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 AuthExampleClient1.turnOnBuiltInUsers(AuthExampleClient1.java:53)
        at AuthExampleClient1.main(AuthExampleClient1.java:31)
        ...

It looks to me as if SQL authorization is set. The AuthExampleEmbeddedSQLAuth program turned
on SQL authorization, but as a database property, not a system property. The database used
by the old authentication programs, jdbcDemoDB, is not the same as the one used by AuthExampleEmbeddedSQLAuth,
so I didn't think SQL authorization would apply to it. Does SQL authorization actually apply
to all databases forever starting from the time you set it??

So I thought, well, maybe I should try Derby 10.4.1.0 Beta and see if it behaves any differently
-- this doc will go in post-10.4 anyway. So I set DERBY_HOME to my installation of db-derby-10.4.1.0-bin
and tried again. This time I first ran AuthExampleEmbedded, which ran as expected once again.
Then, instead of trying AuthExampleEmbeddedSQLAuth, which I thought might mess things up again,
I started the Network Server and tried to run AuthExampleClient1. This time I got a different
exception:

 jdench 58 =>java -cp ${CLASSPATH}:${DERBY_HOME}/lib/derbyclient.jar AuthExampleClient1
org.apache.derby.jdbc.ClientDriver loaded.
Trying to connect to jdbc:derby://localhost:1527/jdbcDemoDB;create=true

---SQLException Caught---

SQLState:   XJ040
Severity: -1
Message:  DERBY SQL error: SQLCODE: -1, SQLSTATE: XJ040, SQLERRMC: Failed to start database
'jdbcDemoDB', see the next exception for details.::SQLSTATE: XCW00Unsupported upgrade from
'10.3' to '10.4 beta'.
java.sql.SQLException: DERBY SQL error: SQLCODE: -1, SQLSTATE: XJ040, SQLERRMC: Failed to
start database 'jdbcDemoDB', see the next exception for details.::SQLSTATE: XCW00Unsupported
upgrade from '10.3' to '10.4 beta'.
        at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
        at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
        at org.apache.derby.jdbc.ClientDriver.connect(Unknown Source)
        at java.sql.DriverManager.getConnection(DriverManager.java:582)
        at java.sql.DriverManager.getConnection(DriverManager.java:207)
        at AuthExampleClient1.main(AuthExampleClient1.java:28)

Now I'm completely mystified, because I delete the database between program runs. What's being
upgraded??

I'm using jdk1.6.0_04, in case that makes any difference.

BTW, I did not grant the ASF license for the programs yet, because they're not working yet
and they shouldn't be used -- I was planning to grant it once they are working.

Thanks again! Once again, no hurry. 

> 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: auth2.log, 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.


Mime
View raw message