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