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] Updated: (DERBY-3200) Developer's Guide: Add examples showing use of SQL authorization with user authentication
Date Wed, 20 Aug 2008 17:34:49 GMT

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

Dag H. Wanvik updated DERBY-3200:
---------------------------------

    Attachment: AuthExampleEmbeddedSQLAuth.java.dhw

Hi Kim, 

thanks for the new patch! I think the code essentially shows the
behavior well by now!

Here are some comments on the embedded code example (some of them may
apply to the client examples; I didn't have the time to check yet)

I upload a version of AuthExampleEmbeddedSQLAuth which is tweaked a
bit; feel free to take what you like from my mods :) The comments
below are reflected the uploaded version.

- Currently, fullAccessUsers are set to "sa, mary" (full access is
  also the default), whereas in the code, user "sqlsam" is used to
  illustrate a user which is granted privileges to access mary's
  tables. I think it would be more illustrative to let the default
  access mode be "noAccess", and change the code to grant full access
  to "sqlsam, mary", and then show that user "sa" can't connect in
  spite of having been defined as a user.

  If the default access mode is changed to be noAccess, you need to
  supply a user which has access (I used mary) when you first shut
  down the database.

- I suggest you let the error handling method exit as well, not sure
  it makes sense to continue.

- I suggest you close some connections which are left open

- In the section labeled "Log in as user with select and insert
  privileges on the table, but not delete privileges", I suggest you
  split the try catch region in two, since only the final stretch is
  expected to throw.

- Some code after an error is detected can be removed; just exit.

- I suggest some comment changes here and there, cf. the code.


> 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