Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <16308045.1216130852235.JavaMail.jira@brutus>
Date: Tue, 15 Jul 2008 07:07:32 -0700 (PDT)
From: "Kim Haase (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Updated: (DERBY-3200) Developer's Guide: Add examples
 showing use of SQL authorization with user authentication
In-Reply-To: <11025538.1194907370799.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


     [ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Kim Haase updated DERBY-3200:
-----------------------------

    Attachment: AuthExampleEmbedded_dhw.java

Thanks very much, Dag -- I tried that, and found that indeed the database did shut down normally the first time, specifying just the user "sa" and no password.

However, at the end of the program it did *not* shut down normally:

Turned off all the user-related properties
Closed connection

---SQLException Caught---

SQLState:   08004
Severity: 40000
Message:  Connection authentication failure occurred.  Reason: Invalid authentication..
Database did not shut down normally
Derby system shut down normally

I tried several things to make it shut down. What worked eventually was to not delete one user, and to shut down the database specifying both the user name and the password for that user. I used mary, though I'm sure I could have used sa too. I wanted to make sure that the user at the first database shutdown doesn't have to be the same as the user for the second shutdown.

Does this make sense? If so I will edit the plain authentication example and also use the same process for the SQL authorization embedded program. I'm attaching the actual program I ran (AuthExampleEmbedded_dhw.java).


> 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, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, DERBY-3200-2.diff, DERBY-3200-2.zip, DERBY-3200.diff, DERBY-3200.stat, DERBY-3200.zip, 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.