[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-3200: ----------------------------- Attachment: AuthExampleClientSQLAuth2.java AuthExampleClientSQLAuth1.java rdevcsecuresqlauthembeddedex.dita Sorry about the long delay getting back to this. I've created a topic for the example that shows SQL authorization with the embedded driver. I've run into a problem, though, with the client driver. I'm trying to do something similar to the two programs shown in the topic http://db.apache.org/derby/docs/dev/devguide/rdevcsecureclientexample.html. I have two programs similar to the ones in that example, except that the first one sets sqlAuthorization to true along with the other properties, and creates a new user. In the second program, AuthExampleClientSQLAuth2.java, a user with full access tries to grant the new user select and insert privileges. However, when I run this program, I get an inexplicable error indicating that sqlAuthorization isn't set, although it is. (I put in a debugging routine to display the values of the properties just before I attempt the grant.) Here's a snippet of the output showing the error: Trying to connect to jdbc:derby://localhost:1527/authClientDB;user=mary;password=little7xylamb Connected to database authClientDB Created table accessibletbl Value of accessibletbl/textcol is hello Reporting property values: Value of requireAuthentication is true Value of sqlAuthorization is true Value of defaultConnectionMode is null Value of fullAccessUsers is sa,mary Value of readOnlyAccessUsers is guest ---SQLException Caught--- SQLState: 42Z60 Severity: -1 Message: GRANT not allowed unless database property derby.database.sqlAuthorization has value 'TRUE'. java.sql.SQLSyntaxErrorException: GRANT not allowed unless database property derby.database.sqlAuthorization has value 'TRUE'. at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source) at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source) at org.apache.derby.client.am.Statement.executeUpdate(Unknown Source) at AuthExampleClientSQLAuth2.main(AuthExampleClientSQLAuth2.java:92) Caused by: org.apache.derby.client.am.SqlException: GRANT not allowed unless database property derby.database.sqlAuthorization has value 'TRUE'. at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source) at org.apache.derby.client.am.Statement.completeExecuteImmediate(Unknown Source) The result is that the new user has the default full access and is able to delete a row from the table. I can't figure out why Derby doesn't know that sqlAuthorization is on. What is wrong with the program? Any suggestions appreciated ... > 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: AuthExampleClientSQLAuth1.java, > AuthExampleClientSQLAuth2.java, AuthExampleEmbeddedSQLAuth.java, > rdevcsecuresqlauthembeddedex.dita > > > 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.