Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <1707706212.1219269764329.JavaMail.jira@brutus>
Date: Wed, 20 Aug 2008 15:02:44 -0700 (PDT)
From: "Kim Haase (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (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: quoted-printable


    [ https://issues.apache.org/jira/browse/DERBY-3200?page=3Dcom.atlassian=
.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=3D1262=
4159#action_12624159 ]=20

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

Thanks, Dag! That's very helpful.

Only the database owner can shut down the database -- so that's probably th=
e only other place in these programs where replacing "mary" with "dbOwner" =
would make sense. (We're not doing anything with encryption or upgrades.)

I'll see about creating a method for cleanup and shutdown.

> Developer's Guide: Add examples showing use of SQL authorization with use=
r 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, AuthExampleClient1.java, AuthExampleClien=
t1.java, AuthExampleClient1.java, AuthExampleClient2.java, AuthExampleClien=
t2.java, AuthExampleClient2.java, AuthExampleClientSQLAuth1.java, AuthExamp=
leClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQL=
Auth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java,=
 AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampl=
eClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLA=
uth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, =
AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEm=
bedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampl=
eEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQL=
Auth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java=
, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java.dhw, DER=
BY-3200-2.diff, DERBY-3200-2.zip, DERBY-3200-3.diff, DERBY-3200-3.zip, DERB=
Y-3200-4.diff, DERBY-3200-4.zip, DERBY-3200-5.diff, DERBY-3200-5.zip, DERBY=
-3200.diff, DERBY-3200.stat, DERBY-3200.zip, rdevcsecuresqlauthembeddedex.d=
ita, sqlauthclient.txt, sqlauthclientshutdown.txt, sqlauthembedded.txt, sql=
authembedded.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 use=
r access, not to expand it.
> For example, if you set the default connection mode to noAccess, a user w=
ith fullAccess can't grant any privileges to a user with noAccess. And pres=
umably if the default connection mode is readOnlyAccess, a user with fullAc=
cess can't grant any privileges beyond SELECT, which the user has anyway.
> Only if the default connection mode is fullAccess is SQL authorization mo=
de 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 pr=
ogram, 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 'SYSC=
S_UTIL'.'SYSCS_SET_DATABASE_PROPERTY'.
> This seems a bit extreme. I know that with SQL authorization on, "the abi=
lity 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?=20
> I realize that having access to SYSCS_SET_DATABASE_PROPERTY would allow m=
e 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 de=
rby.database.sqlAuthorization.=20
> 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 el=
se off, either!
> I'll attach the program I've been using. Most of the stacktraces are expe=
cted, but I'm stumped by that last one.

--=20
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.