db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3200) Developer's Guide: Add examples showing use of SQL authorization with user authentication
Date Wed, 20 Aug 2008 22:32:44 GMT

    [ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12624168#action_12624168
] 

Dag H. Wanvik commented on DERBY-3200:
--------------------------------------

> Only the database owner can shut down the database 

If SQL authorization has been enabled, yes.

Some comments on the html for the embedded case:

> The program does the following:
> 
>    1. Starts Derby and creates a database named sqlAuthEmbDB, using
>       the embedded driver. The connection URL creates the database as
>       the user mary, who is therefore the database owner. When SQL
>       authorization is on, only the database owner has the right to
>       set and read database properties.

After SQL authorization is enabled, only the database owner will have
the right to set and read database properties.

> 
>    2. Sets database properties that create users with different levels
>       of access (read-only and full access), that require

If you follow the suggestion from my last posting, it should read: "no
access, read-only and full access"

>       authentication, and that turn on SQL authorization. The user
>       mary has full access.

One more user is given full access (?)

> 
>    3. Closes the connection, then stops and restarts the database so
>       that the authentication changes can take effect.

.. and SQL authorization changes

> 

(add item) Tries to connect to the database with a user that has no
access

>    4. Tries to connect to the database without a username and
>       password, raising an exception.
> 
>    5. Connects to the database as a user with read-only access; the
>       connection succeeds, but an attempt to create a table raises an
>       exception.
> 
>    6. Connects to the database as mary, who has full access; this user
>       creates and populates a table. This user also grants select and
>       insert privileges on this table to another user.
> 
>    7. Connects to the database as the user who has been granted select

This user also has full (i.e. r/w) access on the connection level (but
limited powers since SQL authorization is active). Connection level full access" is not
really full access when SQL authorization is enabled, though... a bit confusing, this...

>       and insert privileges. The user successfully performs select and
>       insert operations on the table, but an attempt to delete a row
>       from the table raises an exception.
> 
>    8. Connects to the database again as the user with full access, who

You seem to imply that only mary has connection level "full access" here..?

>       then deletes the table.
> 
>    9. Turns off authentication, then removes the users (except the one

If you reboot once again after removing authentication, you can remove the 
last user property as well; you would be able to shut down without credentials then.
You may choose not to mention this... ;)

> 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, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient1.java,
AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClientSQLAuth1.java,
AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java,
AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java,
AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java,
AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java,
AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java,
AuthExampleEmbedded.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java,
AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java,
AuthExampleEmbeddedSQLAuth.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.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.


Mime
View raw message