[
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.
