You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Kim Haase (JIRA)" <ji...@apache.org> on 2008/05/29 17:35:45 UTC
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
[ 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
Thanks very much, Dag, for the help!
I have now put in code to shut down the database at the end of the first client program. I am trying to make these as similar as possible in structure to the client built-in authentication/authorization programs under http://db.apache.org/derby/docs/dev/devguide/devguide-single.html#rdevcsecure125. I no longer remember why we had the two programs in that section -- maybe to show that you can continue to run the Derby engine while running more than one program? I am not sure why those two programs work fine when you don't shut down the database between the first and second programs -- because they set some static properties too (derby.connection.requireAuthentication). Maybe they aren't working fine after all, though they seem to be doing the right things.
So now when I shut down the database at the end of the first program and connect at the start of the second, everything works the same way the built-in authentication/authorization programs do -- until the very end.
(The reason I put in the comment about the defaultConnectionMode being fullAccess was that previously, if I didn't shut down the database, I was allowed to log in without a username or password, as if the default connection mode really was fullAccess. But that is no longer the case.)
What happens at the end is that I cannot shut down the database -- I get an authentication failure. (I also can't call the SYSCS_UTIL procedures.) I realize that only the owner of the database can shut it down if SQL authorization is on -- so who was I when I created the database, and how do I become that user again? I think I was APP -- the authorized user for both the APP and SYSCS_UTIL schemas according to the output of the "select * from sys.sysschemas" statement:
ij> select * from sys.sysschemas;
SCHEMAID |SCHEMANAME |AUTHORIZATIONID
...
c013800d-00fb-2649-07ec-000000134f30|SYSCS_UTIL |APP
80000000-00d2-b38f-4cda-000a0a412c00|APP |APP
0ddd00a9-011a-351d-3249-ffffd494cc61|MARY |MARY
But how would I connect to the database as the user "APP"? What is APP's password? I tried using APP123 (that was the password for Service Registry's Java DB instance) but I got authentication errors.
I tried calling "SET SCHEMA APP" before shutting down the database, but that had no effect either.
I then tried creating the database as the user who will eventually shut it down (mary), but that user cannot call the SYSCS_UTIL procedures to set the database properties. So there seems to be a catch-22: you have to be APP to create a database and users and set SQL authorization; but then you can't seem to revert to that user in order to shut down the database.
I'm attaching the files in their current state for you to try out. Thanks for any ideas!
> 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, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, rdevcsecuresqlauthembeddedex.dita, sqlauthclient.txt, sqlauthclientshutdown.txt, sqlauthembedded.txt, sqlauthembedded.txt
>
>
> 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.