Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <223679948.1220046464580.JavaMail.jira@brutus>
Date: Fri, 29 Aug 2008 14:47:44 -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: quoted-printable


     [ https://issues.apache.org/jira/browse/DERBY-3200?page=3Dcom.atlassia=
n.jira.plugin.system.issuetabpanels:all-tabpanel ]

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

           Derby Info: [Patch Available]
    Affects Version/s: 10.4.1.3

> 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
>    Affects Versions: 10.4.1.3
>            Reporter: Kim Haase
>            Assignee: Kim Haase
>            Priority: Minor
>         Attachments: auth2.log, AuthExampleClient1.java, AuthExampleClien=
t1.java, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClien=
t2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClien=
t2.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, Au=
thExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleCl=
ientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth=
1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, Aut=
hExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleCli=
entSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2=
.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, Auth=
ExampleEmbedded-dhw.java, AuthExampleEmbedded.java, AuthExampleEmbedded.jav=
a, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded_=
dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java,=
 AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExam=
pleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbedd=
edSQLAuth.java.dhw, DERBY-3200-2.diff, DERBY-3200-2.zip, DERBY-3200-3.diff,=
 DERBY-3200-3.zip, DERBY-3200-4.diff, DERBY-3200-4.zip, DERBY-3200-5.diff, =
DERBY-3200-5.zip, DERBY-3200-6.diff, DERBY-3200-6.zip, DERBY-3200-7.diff, D=
ERBY-3200-7.stat, DERBY-3200-7.zip, DERBY-3200.diff, DERBY-3200.stat, DERBY=
-3200.zip, rdevcsecuresqlauthembeddedex.dita, sqlauthclient.txt, sqlauthcli=
entshutdown.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 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.