You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Kim Haase (JIRA)" <ji...@apache.org> on 2007/11/12 23:42:50 UTC
[jira] Created: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
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
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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-3200:
-----------------------------
Attachment: AuthExampleEmbeddedSQLAuth.java
AuthExampleClientSQLAuth2.java
AuthExampleClientSQLAuth1.java
Attaching SQL authorization examples.
> 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, 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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12612864#action_12612864 ]
Dag H. Wanvik commented on DERBY-3200:
--------------------------------------
Hi Kim,
I had a look at the code of AuthExampleEmbedded and
propose some changes to make it better:
- Generally in embedded: You should shut down the database before shutting down the Derby system
to get a clean shutdown state. If not, when you restart, recovery must be made.
- To set the static database level properties in this example, is is sufficient to reboot the database,
you do not need to shutdown the Derby system and reload the driver.
- I would prefer the example to show in the code which exceptions are expected instead
of just mentioning that two exceptions are to be expected when you run the example. This
makes the program run cleanly and make it easier to read I think.
I attach my proposed revision as AuthExampleEmbedded-dhw.java, see what you think :)
I did not look at the other example this time; perhaps some of the comments apply to those as well.
> 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, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, DERBY-3200-2.diff, DERBY-3200-2.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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-3200:
-----------------------------
Attachment: DERBY-3200-2.zip
DERBY-3200-2.diff
Sorry for the long delay on this. I have updated the two example topics to remove the code that retrieves the defaultConnectionMode, since this property was not set programmatically and its displayed value is therefore misleading. I added a mention of the default connection mode to the comment on the turnOnBuiltInUsers method.
I also updated the descriptions of the programs to explain why I am creating the database as "mary" in addition to specifying her as the full-access user.
> 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, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, DERBY-3200-2.diff, DERBY-3200-2.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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12542190 ]
Dag H. Wanvik commented on DERBY-3200:
--------------------------------------
The problem is that only the database owner can call that procedure once you have set sqlAuthorization to true.
This is to avoid a security hole: Otherwise any user could gain access by switching sqlAuthorization off.
The database owner here is the effective user authorization identifier when creating the database.
Since you didn't specify a user at the original connect that created the database, this defaults to user "APP".
Changing owner to user mary, which is the effective user you have when trying to switch things off, will make it work:
String connectionURL = "jdbc:derby:" + dbName + ";user=mary;create=true";
Hope this helps :)
> 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: AuthExampleEmbeddedSQLAuth.java
>
>
> 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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12613421#action_12613421 ]
Kim Haase commented on DERBY-3200:
----------------------------------
Thanks very much, Dag. I have been looking at this new code, and I am a bit puzzled by what happens when we try to shut down the database in embedded mode. It appears *not* to raise a SQLException. I modified the code slightly so that something is printed out no matter what happens:
boolean gotSQLExc = false;
try {
DriverManager.getConnection("jdbc:derby:authEmbDB;shutdown=true");
} catch (SQLException se) {
if ( se.getSQLState().equals("08006") ) {
gotSQLExc = true;
} else {
errorPrint(se);
}
}
if (!gotSQLExc) {
System.out.println(
"Database did not shut down normally");
} else {
System.out.println("Database shut down normally");
}
The only output is the line "Database did not shut down normally", indicating that the exception wasn't raised. An exception is raised when you shut down the database in Network Server mode but apparently not in embedded mode. I don't know if the database was actually shut down or not.
> 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, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, DERBY-3200-2.diff, DERBY-3200-2.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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.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 user 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, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClientSQLAuth1.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, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.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-6.diff, DERBY-3200-6.zip, DERBY-3200-7.diff, DERBY-3200-7.stat, DERBY-3200-7.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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12624104#action_12624104 ]
Kim Haase commented on DERBY-3200:
----------------------------------
I have a question about this section of the revised code:
try {
// this should fail
System.out.println("Trying to connect to " + connectionURL +
" without username or password");
conn = DriverManager.getConnection(connectionURL);
System.out.println(
"ERROR: Unexpectedly connected to database " + dbName);
System.exit(1);
} catch (SQLException e) {
...
Is it safe to call System.exit(1) on line 77, without at least closing the connection first? Does the application need to shut down the database and Derby gracefully? Admittedly this code is highly unlikely to be executed. There are similar situations at lines 96, 121 and 209. I wonder if the code that shuts down the database and Derby should be a separate method so it can be called at various points?
BTW, the line length limit for PDF is 73 characters, not 72.
> 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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12624139#action_12624139 ]
Kim Haase commented on DERBY-3200:
----------------------------------
FYI so we don't duplicate effort -- I am working on fixes to the other programs while I wait for answers, so you don't need to do that. Thanks again, Dag!
> 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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12602137#action_12602137 ]
Dag H. Wanvik commented on DERBY-3200:
--------------------------------------
Kim > Should I modify the topics that say the default connection mode is fullAccess, to say that when SQL authorization is on the default is null?
Hmm, this is a bit tricky to explain. We have two authorization systems that work in conjunction. The connection level authorization and the SQL authorization (if enabled). When SQL authorization is enabled, this *further restricts* whatever restrictions are in place by the connection level authorization (and vice versa). That is, for a user that has full access connection level access, she cannot update a table owned by someone else (unless she is the database owner, or has been granted the relevant SQL permission). If a user has read only connection level authorization, she can not *read* another user's tables (unless ... as before). But in this case, she cannot update the tables *even if* suitable SQL level access has been granted. So, the most restrictive authorization "wins", if you will...
So, the default for the connection level authorization (fullAccess) still applies even in the presence of SQL authorization, but it is no carte blanche...
Hope this helps...
> 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, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, 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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12624170#action_12624170 ]
Kim Haase commented on DERBY-3200:
----------------------------------
Thanks again! I had just started looking at those steps. This will be a big help.
> 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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12617485#action_12617485 ]
Kim Haase commented on DERBY-3200:
----------------------------------
One more question. I'm working on DERBY-503, about making sure to use Class.forName(driver).newInstance() to instantiate a driver. I think it may affect these examples.
In the client examples, where the driver is instantiated twice (once in the first program and again in the second), newInstance() is called only on the second instantiation. In the embedded examples, where the driver is instantiated only once, newInstance is not called at all. Should it be called every time Class.forName() is called?
I should probably also add the note about not having to call Class.forName() if you are using JDK 1.6.
> 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, AuthExampleClient2.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, DERBY-3200-2.diff, DERBY-3200-2.zip, DERBY-3200-3.diff, DERBY-3200-3.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.
[jira] Issue Comment Edited: (DERBY-3200) Developer's Guide: Add
examples showing use of SQL authorization with user authentication
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12612864#action_12612864 ]
dagw edited comment on DERBY-3200 at 7/11/08 6:01 AM:
---------------------------------------------------------------
Hi Kim,
I had a look at the code of AuthExampleEmbedded and
propose some changes to make it better:
- To set the static database level properties in this example, is is sufficient to reboot the database,
you do not need to shutdown the Derby system and reload the driver.
- I would prefer the example to show in the code which exceptions are expected instead
of just mentioning that two exceptions are to be expected when you run the example. This
makes the program run cleanly and make it easier to read I think.
I attach my proposed revision as AuthExampleEmbedded-dhw.java, see what you think :)
I did not look at the other example this time; perhaps some of the comments apply to those as well.
was (Author: dagw):
Hi Kim,
I had a look at the code of AuthExampleEmbedded and
propose some changes to make it better:
- Generally in embedded: You should shut down the database before shutting down the Derby system
to get a clean shutdown state. If not, when you restart, recovery must be made.
- To set the static database level properties in this example, is is sufficient to reboot the database,
you do not need to shutdown the Derby system and reload the driver.
- I would prefer the example to show in the code which exceptions are expected instead
of just mentioning that two exceptions are to be expected when you run the example. This
makes the program run cleanly and make it easier to read I think.
I attach my proposed revision as AuthExampleEmbedded-dhw.java, see what you think :)
I did not look at the other example this time; perhaps some of the comments apply to those as well.
> 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, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, DERBY-3200-2.diff, DERBY-3200-2.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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-3200:
-----------------------------
Attachment: DERBY-3200-6.zip
DERBY-3200-6.diff
Attaching DERBY-3200-6.diff and DERBY-3200-6.zip.
These revised examples are based on Dag's latest suggestions and example, including the error-handling and exit strategy. I've added a method that closes the connection and shuts down the database, which is called if unexpected errors occur as well as at the end of the programs.
Some offline consultation with Dag has led to another change. Instead of turning off the security properties at the end of each program, it seems best to model the behavior we want to see in real-world examples and to leave the properties set on each database when we exit the program. This has the side effect of shortening some very long examples.
The only drawback is that the documentation of how to turn off Derby properties is spotty -- these examples were the main illustration of it, I think. Setting a property value to null in effect restores the built-in default setting for that property. This information needs to be added to the description of Derby properties in the Tuning Guide, probably in the topic "Derby properties" (http://db.apache.org/derby/docs/dev/tuning/ctunproper22250.html). The behavior for turning off user settings is slightly different and is documented in several places; it's the general case that seems to be missing. I will create a JIRA issue for this.
New versions of sample programs to follow.
> 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-6.diff, DERBY-3200-6.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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12613648#action_12613648 ]
Dag H. Wanvik commented on DERBY-3200:
--------------------------------------
Yes, this is correct :)
If you comment the shutdown connect to explain this nuance, I think you can use that connect to illustrate this point!
> 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, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, DERBY-3200-2.diff, DERBY-3200-2.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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-3200:
-----------------------------
Attachment: DERBY-3200-4.zip
DERBY-3200-4.diff
Thanks very much, Dag. I've modified the topics to use newInstance in all cases. I'm attaching the latest patch, DERBY-3200-4.diff, and the output files DERBY-3200-4.zip. Will attach the source files shortly.
> 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, AuthExampleClient2.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, 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.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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12585079#action_12585079 ]
Dag H. Wanvik commented on DERBY-3200:
--------------------------------------
Some comments on the initial description section.
> 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.
I guess he could, but it would be of little use, since the noAccess
would override the privilege.
> And presumably if the default connection mode is readOnlyAccess, a
> user with fullAccess can't grant any privileges beyond SELECT,
Again, the full access user could grant update privilege but it would
be of no use, since the grantee and only read (connection level
authorization overrides again).
> which the user has anyway.
No, that is not the case: when sqlAuthorization mode is active, a user
only has access to her own tables by default (and read access to
system tables it seems).
Cf section "How user authorization properties work together" in the
dev guide:
(quote)
- The access mode specified for the
derby.database.defaultConnectionMode property overrides the
permissions that are granted by the owner of a database object. For
example, if a user is granted INSERT privileges on a table but the
user only has read-only connection authorization, the user cannot
insert data into the table.
I interpret this "overrides" as "further restricts".
> Only if the default connection mode is fullAccess is SQL authorization
> mode meaningful.
No, even for connections with readOnly connection access, the
GRANT/REVOKE machinery can be used to further limit access (but not to
broaden it beyond readOnly).
> That means that a fullAccess user can use GRANT to restrict another
> user's privileges on a particular database that the user owns.
GRANT/REVOKE provides more fine graned access control that connection
level authorization. The way I think of this is that, when both are
used, the more limiting access provided by each authorization
mechanism (connection or sqlAUthorization) rules the day in any
particular case.
Hope this makes sense :) It is a bit confusing, so making running
examples to check understanding is very useful here. Sorry if I
misconstrued something.
> 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, 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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12627687#action_12627687 ]
Dag H. Wanvik commented on DERBY-3200:
--------------------------------------
Looks ok now, thanks! Having these examples is really helpful, I
think!
A minor note; I noticed some UNIX-centric command lines that may confuse
some readers, e.g in src/devguide/rdevcsecuresqlauthembeddedex.dita:
java -cp .:${DERBY_HOME}/lib/derby.jar AuthExampleEmbeddedSQLAuth
You may fix this by just saying "On, *nix, this would look like this"
or some such, or by proving the equivalent stanza for Windows. Just
above in the text, the CLASSPATH content is accounted for by showing
both OS variants, already.
The "HTML pages" rendering makes some sections look a bit funny sometimes,
when the paragraph starts with "The following...", e.g. in
rdevcsecuresqlauthexs.dita/html, since what you see on the HTML page
is 1) the bullet and the link, then 2) the text starting with "The
following....". Maybe using the more directionally neutral "This...." would work
better? (the PDF doesnt have this problem)
> 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
> Affects Versions: 10.4.1.3
> Reporter: Kim Haase
> Assignee: Kim Haase
> Priority: Minor
> Attachments: auth2.log, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.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, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.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-6.diff, DERBY-3200-6.zip, DERBY-3200-7.diff, DERBY-3200-7.stat, DERBY-3200-7.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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ 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
Thanks very much, Dag, for the help!
I have now put in code to shut down the database at the end of the first client program. I am trying to make these as similar as possible in structure to the client built-in authentication/authorization programs under http://db.apache.org/derby/docs/dev/devguide/devguide-single.html#rdevcsecure125. I no longer remember why we had the two programs in that section -- maybe to show that you can continue to run the Derby engine while running more than one program? I am not sure why those two programs work fine when you don't shut down the database between the first and second programs -- because they set some static properties too (derby.connection.requireAuthentication). Maybe they aren't working fine after all, though they seem to be doing the right things.
So now when I shut down the database at the end of the first program and connect at the start of the second, everything works the same way the built-in authentication/authorization programs do -- until the very end.
(The reason I put in the comment about the defaultConnectionMode being fullAccess was that previously, if I didn't shut down the database, I was allowed to log in without a username or password, as if the default connection mode really was fullAccess. But that is no longer the case.)
What happens at the end is that I cannot shut down the database -- I get an authentication failure. (I also can't call the SYSCS_UTIL procedures.) I realize that only the owner of the database can shut it down if SQL authorization is on -- so who was I when I created the database, and how do I become that user again? I think I was APP -- the authorized user for both the APP and SYSCS_UTIL schemas according to the output of the "select * from sys.sysschemas" statement:
ij> select * from sys.sysschemas;
SCHEMAID |SCHEMANAME |AUTHORIZATIONID
...
c013800d-00fb-2649-07ec-000000134f30|SYSCS_UTIL |APP
80000000-00d2-b38f-4cda-000a0a412c00|APP |APP
0ddd00a9-011a-351d-3249-ffffd494cc61|MARY |MARY
But how would I connect to the database as the user "APP"? What is APP's password? I tried using APP123 (that was the password for Service Registry's Java DB instance) but I got authentication errors.
I tried calling "SET SCHEMA APP" before shutting down the database, but that had no effect either.
I then tried creating the database as the user who will eventually shut it down (mary), but that user cannot call the SYSCS_UTIL procedures to set the database properties. So there seems to be a catch-22: you have to be APP to create a database and users and set SQL authorization; but then you can't seem to revert to that user in order to shut down the database.
I'm attaching the files in their current state for you to try out. Thanks for any ideas!
> 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, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, 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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12613620#action_12613620 ]
Kim Haase commented on DERBY-3200:
----------------------------------
I should add that I think what happened was that although I turned off requireAuthentication at the end, it is a static property, so it was still in effect when I shut down the database.
Although I had reset defaultConnectionMode to fullAccess, and this is a dynamic property so the reset took effect immediately, it had no effect because requireAuthentication was still on.
Is that correct?
> 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, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, DERBY-3200-2.diff, DERBY-3200-2.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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12585056#action_12585056 ]
Dag H. Wanvik commented on DERBY-3200:
--------------------------------------
Btw, you may want to reattach your code samples with ASF license granted.
Not important here, but good rule.
> 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, 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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12626771#action_12626771 ]
Dag H. Wanvik commented on DERBY-3200:
--------------------------------------
Examples look logical and good now, I think, thank you, :)
+1
Just some minor notes
- Maybe comment on why there are two programs in the client example
(instead of just one)? Arbitrary choice?
- Examples show $CLASSPATH being used in addition to Derby jars, but
to run the program CLASSPATH needs contain ".", which I think is not
called out?
- Funny looking javadoc (/** line is usually empty):
/** Exception reporting methods
* with special handling of SQLExceptions
*/
for errorPrintAndExit
- SQLExceptionPrint has //-style comment, other methods have Javadoc
style.
In the context that these examples are placed, I presume therre is some
discussion/explanation of the levels of authorization?
Thanks for your tireless work on this issue!
> 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, AuthExampleClient1.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClientSQLAuth1.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, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.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-6.diff, DERBY-3200-6.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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-3200:
-----------------------------
Attachment: sqlauthclientshutdown.txt
sqlauthclient.txt
sqlauthembedded.txt
I figured out most of the weird results -- sorry for the noise. (The database was not where I thought it was and must have been a leftover from a previous run after all.) The old authentication/authorization examples work fine.
I changed the SQL authorization programs so that the default connection mode for the network client example is fullAccess (the default), so that the GRANT statement can restrict privileges.
The embedded example works as expected: see the attached file sqlauthembedded.txt.
But I am still getting an error on the GRANT statement in the SQL authorization client example:
s.execute("GRANT SELECT, INSERT ON accessibletbl TO sqlsam");
System.out.println("Granted select/insert privileges to sqlsam");
This is what happens:
---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)
The program goes on to allow sqlsam to do everything he wants, because he has the default fullAccess privilege. See the attached file sqlauthclient.txt.
I tried stopping and restarting the network server between the first and second client programs, but that caused worse things to happen. See the attached file sqlauthclientshutdown.txt.
I am using different jar files to invoke the programs: derby.jar for the embedded program, and derbyclient.jar for the client programs. Could that make any difference?
I'll attach updated versions of the programs in addition to the program output files.
> 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, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbeddedSQLAuth.java, rdevcsecuresqlauthembeddedex.dita, sqlauthclient.txt, sqlauthclientshutdown.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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12603568#action_12603568 ]
Kim Haase commented on DERBY-3200:
----------------------------------
Thanks for that explanation!
I wonder if it would be useful to add this information to the description of SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY in the Reference Manual (http://db.apache.org/derby/docs/dev/ref/rrefgetdbpropfunc.html)?
> 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, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, 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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-3200:
-----------------------------
Attachment: (was: AuthExampleClientSQLAuth2.java)
> 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, AuthExampleClient2.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, DERBY-3200-2.diff, DERBY-3200-2.zip, DERBY-3200-3.diff, DERBY-3200-3.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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-3200:
-----------------------------
Attachment: sqlauthembedded.txt
Attaching correct version of embedded example run.
> 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, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, 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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12613619#action_12613619 ]
Dag H. Wanvik commented on DERBY-3200:
--------------------------------------
Thanks for your continued patience on this issue, Kim; this is a tricky topic :)
Yes, this makes sense. Since authentication is still active (until after reboot; it is a static property),
when you remove all users, you will be able to make a connection to shut down the database.
When you also have sqlAuthorization enabled you would not even be able to shut down
the system (which does work in this case).
Btw, even when the shutdown fails (as it did here the first time), the rest
of the example doesn't really show the effects of the fact that sqlAuthentication
is not active, that is, when the first shutdown fails, the passwords are not checked.
It may be instructive to add a connect case where you try to use a wrong password to show that this
fails as well.
> 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, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, DERBY-3200-2.diff, DERBY-3200-2.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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-3200:
-----------------------------
Attachment: AuthExampleEmbeddedSQLAuth.java
AuthExampleClientSQLAuth2.java
AuthExampleClientSQLAuth1.java
These are the latest versions of the test programs.
> 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, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, rdevcsecuresqlauthembeddedex.dita, sqlauthclient.txt, sqlauthclientshutdown.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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-3200:
-----------------------------
Attachment: AuthExampleEmbeddedSQLAuth.java
AuthExampleClientSQLAuth2.java
AuthExampleClientSQLAuth1.java
Note: the latest changes are in DERBY-3200-2.diff and DERBY-3200-2.zip.
Also attaching the revised source files.
> 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, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, DERBY-3200-2.diff, DERBY-3200-2.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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12585534#action_12585534 ]
Kim Haase commented on DERBY-3200:
----------------------------------
Thanks, Dag! No problem with the delay -- I had heard you were away, and this is not such an urgent issue. I played some more with the programs a few weeks ago but made no further progress, so I'm glad you are back.
Thanks for the clarifications. They help A LOT! I am afraid I still need some more help, though.
I was puzzled by one thing in the auth2.log showing the execution of the second program: why did it let you connect without a username or password, when the first program set the default connection mode to noAccess? The embedded program reports "Connection authentication failure" in that situation, I think.
It looks as if for the SQL authorization programs it might be better to go with the default fullAccess connection mode, so that it is easier to show what is being restricted. Not even being able to set or display the database properties is rather inconvenient!
Another question -- when you say I should reboot the database after setting derby.database.sqlAuthorization, does that mean I should stop and restart the Network Server after I run the first of the two client programs? If so, I'll do that. However, I haven't gotten there yet! The first one isn't working for me any more.
To check that things were still working the way they used to, using Derby 10.3.2.1, I ran the existing embedded authentication example first, AuthExampleEmbedded.java, the one shown in "User authentication and authorization embedded example" (http://db.apache.org/derby/docs/dev/devguide/rdevcsecure26537.html). It ran as expected. Then I ran my new embedded example using SQL authorization, AuthExampleEmbeddedSQLAuth.java, and it also ran as expected.
Then I started the Network Server and tried running the existing authentication client example, AuthExampleClient1.java, the one shown in "User authentication and authorization client example" (http://db.apache.org/derby/docs/dev/devguide/rdevcsecureclientexample.html). It gave the following error the first time I tried to set a database property:
java -cp ${CLASSPATH}:${DERBY_HOME}/lib/derbyclient.jar AuthExampleClient1
org.apache.derby.jdbc.ClientDriver loaded.
Trying to connect to jdbc:derby://localhost:1527/jdbcDemoDB;create=true
Connected to database jdbc:derby://localhost:1527/jdbcDemoDB;create=true
Turning on authentication.
---SQLException Caught---
SQLState: 42504
Severity: -1
Message: User 'APP' does not have execute permission on PROCEDURE 'SYSCS_UTIL'.'SYSCS_SET_DATABASE_PROPERTY'.
java.sql.SQLSyntaxErrorException: User 'APP' does not have execute permission on PROCEDURE 'SYSCS_UTIL'.'SYSCS_SET_DATABASE_PROPERTY'.
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 AuthExampleClient1.turnOnBuiltInUsers(AuthExampleClient1.java:53)
at AuthExampleClient1.main(AuthExampleClient1.java:31)
...
It looks to me as if SQL authorization is set. The AuthExampleEmbeddedSQLAuth program turned on SQL authorization, but as a database property, not a system property. The database used by the old authentication programs, jdbcDemoDB, is not the same as the one used by AuthExampleEmbeddedSQLAuth, so I didn't think SQL authorization would apply to it. Does SQL authorization actually apply to all databases forever starting from the time you set it??
So I thought, well, maybe I should try Derby 10.4.1.0 Beta and see if it behaves any differently -- this doc will go in post-10.4 anyway. So I set DERBY_HOME to my installation of db-derby-10.4.1.0-bin and tried again. This time I first ran AuthExampleEmbedded, which ran as expected once again. Then, instead of trying AuthExampleEmbeddedSQLAuth, which I thought might mess things up again, I started the Network Server and tried to run AuthExampleClient1. This time I got a different exception:
jdench 58 =>java -cp ${CLASSPATH}:${DERBY_HOME}/lib/derbyclient.jar AuthExampleClient1
org.apache.derby.jdbc.ClientDriver loaded.
Trying to connect to jdbc:derby://localhost:1527/jdbcDemoDB;create=true
---SQLException Caught---
SQLState: XJ040
Severity: -1
Message: DERBY SQL error: SQLCODE: -1, SQLSTATE: XJ040, SQLERRMC: Failed to start database 'jdbcDemoDB', see the next exception for details.::SQLSTATE: XCW00Unsupported upgrade from '10.3' to '10.4 beta'.
java.sql.SQLException: DERBY SQL error: SQLCODE: -1, SQLSTATE: XJ040, SQLERRMC: Failed to start database 'jdbcDemoDB', see the next exception for details.::SQLSTATE: XCW00Unsupported upgrade from '10.3' to '10.4 beta'.
at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
at org.apache.derby.jdbc.ClientDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(DriverManager.java:582)
at java.sql.DriverManager.getConnection(DriverManager.java:207)
at AuthExampleClient1.main(AuthExampleClient1.java:28)
Now I'm completely mystified, because I delete the database between program runs. What's being upgraded??
I'm using jdk1.6.0_04, in case that makes any difference.
BTW, I did not grant the ASF license for the programs yet, because they're not working yet and they shouldn't be used -- I was planning to grant it once they are working.
Thanks again! Once again, no hurry.
> 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, 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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-3200:
---------------------------------
Attachment: auth2.log
Sorry not to reply to this one earlier, Kim. Have been away for some time.
You need to reboot the database after setting the property derby.database.sqlAuthorization,
for the change to take effect. It is a static property.
When I did that I got the results shown in auth2.log (attached).
> 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, 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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-3200:
-----------------------------
Attachment: AuthExampleEmbedded.java
DERBY-3200-3.zip
DERBY-3200-3.diff
Thanks again for all your help, Dag. I've revised the other programs along the same lines you suggested in your version of the embedded example. The biggest change, I think, is in the simpler client examples: previously they only closed the connection between the first and second programs, but now they shut down the database as the other programs do.
Hope the comments in the programs are now helpful.
I'm attaching DERBY-3200-3.diff and DERBY-3200-3.zip with the changes. I'll attach the new versions of the source files too. They've all changed significantly.
> 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, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, DERBY-3200-2.diff, DERBY-3200-2.zip, DERBY-3200-3.diff, DERBY-3200-3.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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-3200:
-----------------------------
Attachment: AuthExampleEmbeddedSQLAuth.java
AuthExampleClient2.java
AuthExampleClient1.java
Updated source files.
> 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, AuthExampleClient2.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, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, DERBY-3200-2.diff, DERBY-3200-2.zip, DERBY-3200-3.diff, DERBY-3200-3.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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-3200:
-----------------------------
Attachment: AuthExampleEmbeddedSQLAuth.java
AuthExampleClientSQLAuth2.java
AuthExampleClientSQLAuth1.java
Attaching SQL authorization examples for DERBY-3200-7 patch.
> 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
> Affects Versions: 10.4.1.3
> Reporter: Kim Haase
> Assignee: Kim Haase
> Priority: Minor
> Attachments: auth2.log, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.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, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.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-6.diff, DERBY-3200-6.zip, DERBY-3200-7.diff, DERBY-3200-7.stat, DERBY-3200-7.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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12601647#action_12601647 ]
Kim Haase commented on DERBY-3200:
----------------------------------
Thank you, Dag, that works! I tried versions of the programs using both APP and MARY (in lowercase) as the user who creates the database and they both worked. I will now write up the topics for the sample programs. I think I will use mary as the creator just to show that it doesn't have to be APP who calls the SYSCS_UTIL routines.
I think when I previously tried to create the database as mary, I tried to specify a password too -- but that did not work.
Should I modify the topics that say the default connection mode is fullAccess, to say that when SQL authorization is on the default is null? I think that might be necessary. The two topics are devguide/cdevcsecure36595.dita and tuning/rtunproper24846.dita. In the first ("User authorizations"), there's a section on "How user authorization properties work together" but it doesn't say that explicitly, at least not that I can see.
> 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, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, 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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-3200:
-----------------------------
Attachment: AuthExampleEmbedded.java
AuthExampleClient2.java
AuthExampleClient1.java
Attaching basic user authentication examples (client and embedded).
> 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, 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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-3200:
-----------------------------
Attachment: (was: AuthExampleEmbeddedSQLAuth.java)
> 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, AuthExampleClient2.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, DERBY-3200-2.diff, DERBY-3200-2.zip, DERBY-3200-3.diff, DERBY-3200-3.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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12613433#action_12613433 ]
Kim Haase commented on DERBY-3200:
----------------------------------
I should add that the strange behavior (not raising an exception for database shutdown) happens only when this code is executed in the middle of the program, after the various database properties have been set.
When the same code is executed at the end of the program, after the properties have been turned off, it succeeds: both the database and Derby shut down correctly ---
Database shut down normally
Derby system shut down normally
Why might this be happening?
> 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, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, DERBY-3200-2.diff, DERBY-3200-2.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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-3200:
-----------------------------
Attachment: DERBY-3200-8.zip
DERBY-3200-8.stat
DERBY-3200-8.diff
Thanks, Dag -- excellent suggestions as usual.
Attaching DERBY-3200-8.diff, DERBY-3200-8.stat, and DERBY-3200-8.zip. This patch includes the changes from the DERBY-3200-7 patch. In addition, it changes the introductory sentences to make them clearer and provides clearly introduced platform-specific Java commands. (Just one platform per example.)
The example programs have not changed.
> 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
> Affects Versions: 10.4.1.3
> Reporter: Kim Haase
> Assignee: Kim Haase
> Priority: Minor
> Attachments: auth2.log, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.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, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.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-6.diff, DERBY-3200-6.zip, DERBY-3200-7.diff, DERBY-3200-7.stat, DERBY-3200-7.zip, DERBY-3200-8.diff, DERBY-3200-8.stat, DERBY-3200-8.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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-3200:
-----------------------------
Attachment: AuthExampleEmbedded.java
AuthExampleClient2.java
AuthExampleClient1.java
Attaching latest basic authentication/authorization examples.
> 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, AuthExampleClient1.java, AuthExampleClient2.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.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-6.diff, DERBY-3200-6.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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-3200:
-----------------------------
Attachment: AuthExampleEmbeddedSQLAuth.java
Here's the example I've been playing with. I'm not marking it for inclusion; I'll wait till a later version is part of a topic to grant a license for use.
> 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: AuthExampleEmbeddedSQLAuth.java
>
>
> 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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12635761#action_12635761 ]
Kim Haase commented on DERBY-3200:
----------------------------------
I plan to commit the latest patch in one week (10/7) unless I hear that further changes are needed. Thanks.
> 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
> Affects Versions: 10.4.1.3
> Reporter: Kim Haase
> Assignee: Kim Haase
> Priority: Minor
> Attachments: auth2.log, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.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, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.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-6.diff, DERBY-3200-6.zip, DERBY-3200-7.diff, DERBY-3200-7.stat, DERBY-3200-7.zip, DERBY-3200-8.diff, DERBY-3200-8.stat, DERBY-3200-8.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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12613636#action_12613636 ]
Kim Haase commented on DERBY-3200:
----------------------------------
Our comments crossed in the mail. I think I'm starting to figure this out ...
You suggest using a connect case with a wrong password -- is that in addition to the case where no username/password are specified at all?
Halfway through when I specify the user in order to shut down the database, I can give a wrong password with no problem:
/* Shut down the database to make static properties take
effect. */
boolean gotSQLExc = false;
try {
DriverManager.getConnection( "jdbc:derby:authEmbDB;user=sa;password=badpass;shutdown=true");
Would that illustrate the point? I think you said that's because the default connection mode of noAccess is in effect right away but derby.connection.requireAuthentication is not in effect till after the database restarts. I need to specify a user, but the password is not checked.
> 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, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, DERBY-3200-2.diff, DERBY-3200-2.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.
[jira] Resolved: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase resolved DERBY-3200.
------------------------------
Resolution: Fixed
Fix Version/s: 10.5.0.0
Derby Info: (was: [Patch Available])
Got a verbal okay to commit this patch. Thank you, Dag, for all your help.
Committed patch DERBY-3200-8.diff to documentation trunk at revision 701206.
> 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
> Affects Versions: 10.4.1.3
> Reporter: Kim Haase
> Assignee: Kim Haase
> Priority: Minor
> Fix For: 10.5.0.0
>
> Attachments: auth2.log, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.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, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.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-6.diff, DERBY-3200-6.zip, DERBY-3200-7.diff, DERBY-3200-7.stat, DERBY-3200-7.zip, DERBY-3200-8.diff, DERBY-3200-8.stat, DERBY-3200-8.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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-3200:
-----------------------------
Attachment: AuthExampleEmbeddedSQLAuth.java
AuthExampleClientSQLAuth2.java
AuthExampleClientSQLAuth1.java
Attaching the latest SQL authorization versions of the examples, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, and AuthExampleEmbeddedSQLAuth.java.
> 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, AuthExampleClient2.java, AuthExampleClient2.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, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, 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.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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12624145#action_12624145 ]
Dag H. Wanvik commented on DERBY-3200:
--------------------------------------
Hi Kim,
> Since you created the "dbOwner" variable at the beginning, would it
> make sense to replace other occurrences of "mary" in strings with
> this variable? I think you used it only at the beginning. I could
> fix it elsewhere in the main program.
Right, my intention was to highlight those places in which the
authorization id needs to be the database owner. In other cases "mary"
is used where any full access user would do, e.g. to create a table
and grant privileges on it to test the privileges. In such cases, just
using "mary" is probably better to highlight the different usage?
> Would you like to make similar fixes to the client programs or would
> you rather I did it? Many of the fixes may also apply to the other
> authentication examples (the ones that don't use SQL authorization).
I'd rather you did it :) I probably made too many gratuitous changes
anyway, feel free to use only what you think improves things.
> The changed code needs some further edits to keep the line lengths
> within 72 characters. Longer lines wrap badly in the PDF manuals.
Right, I aimed for less than 80, to avoid breaking too many lines..
> Is it safe to call System.exit(1) on line 77, without at least
> closing the connection first? Does the application need to shut down
> the database and Derby gracefully? Admittedly this code is highly
> unlikely to be executed.
This being just a test program which should not fail in these cases
unless Derby malfunctions, I neglected to shut down the db properly to keep
the example shorter. In a real app that should be probably done before
exitting. Your call.
> There are similar situations at lines 96,
> 121 and 209. I wonder if the code that shuts down the database and
> Derby should be a separate method so it can be called at various
> points?
Seem like a good idea.
> 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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12627497#action_12627497 ]
Dag H. Wanvik commented on DERBY-3200:
--------------------------------------
> I am not sure what you mean by "levels of authorization" -- full access and read only access?
I was thinking of connection authorization vs SQL authorization; but you addressed that I think
by removing link to SQL authorization topic from the examples that don't use SQL authorization, thanks.
I'll have another look at how it all appears now in context.
> 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
> Affects Versions: 10.4.1.3
> Reporter: Kim Haase
> Assignee: Kim Haase
> Priority: Minor
> Attachments: auth2.log, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.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, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.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-6.diff, DERBY-3200-6.zip, DERBY-3200-7.diff, DERBY-3200-7.stat, DERBY-3200-7.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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12599541#action_12599541 ]
Dag H. Wanvik commented on DERBY-3200:
--------------------------------------
It seems AuthExampleClientSQLAuth1 lacks code to shut down the database cleanly, e.g.
by connecting with:
String shutdownConnectionURL = "jdbc:derby://localhost:1527/" + dbName + ";shutdown=true";
This is needed to the static properties whose values have changed to take effect. You should not shut down the
server without first shutting down the database. Btw, since you make the properties database properties, shutting down the server is not necessary, so you could make do with one program to show what you want.
In AuthExampleClientSQLAuth2 you have this code:
// Try to log in with no username or password
try {
// this works because the defaultConnectionMode is fullAccess
System.out.println("Trying to connect to " + connectionURL +
" without username or password");
conn = DriverManager.getConnection(connectionURL);
System.out.println("Connected to database " + dbName);
// close statement and connection
conn.close();
System.out.println("Closed connection");
} catch (Throwable e) {
errorPrint(e);
}
I don't this this is what you want; after authentication is enabled, one should not be able to log in
without supplying user credentials. But maybe I misunderstand you intention here.
Hope this helps!
> 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, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, 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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12620429#action_12620429 ]
Dag H. Wanvik commented on DERBY-3200:
--------------------------------------
I think it is OK to use newInstance in all cases since it seems we are going to recommend this anyway (cf DERBY-503), at least for the embedded case (to guarantee booting Derby). In a client only setting it is redundant, but does no harm I think.
> 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, AuthExampleClient2.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, DERBY-3200-2.diff, DERBY-3200-2.zip, DERBY-3200-3.diff, DERBY-3200-3.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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-3200:
-----------------------------
Attachment: AuthExampleEmbedded.java
AuthExampleClient2.java
AuthExampleClient1.java
Attaching basic examples for DERBY-3200-7 patch.
> 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
> Affects Versions: 10.4.1.3
> Reporter: Kim Haase
> Assignee: Kim Haase
> Priority: Minor
> Attachments: auth2.log, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.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, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.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-6.diff, DERBY-3200-6.zip, DERBY-3200-7.diff, DERBY-3200-7.stat, DERBY-3200-7.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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12602488#action_12602488 ]
Kim Haase commented on DERBY-3200:
----------------------------------
Well, I *think* I understand this -- and I should probably update the topic to make clear why I had to create the database as "mary" -- so she would be the owner and therefore be able to do certain things. And possibly to make other clarifications on the ways the SQL authorization programs differ from the other ones.
I find that in fact, whenever I do not explicitly set the derby.database.defaultConnectionMode property, the value displays as null when I retrieve it. So it's nothing specific to SQL authorization, it's just a quirk in the display of that property, I guess. Possibly a bug.
> 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, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, 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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-3200:
-----------------------------
Attachment: AuthExampleEmbedded_dhw.java
Thanks very much, Dag -- I tried that, and found that indeed the database did shut down normally the first time, specifying just the user "sa" and no password.
However, at the end of the program it did *not* shut down normally:
Turned off all the user-related properties
Closed connection
---SQLException Caught---
SQLState: 08004
Severity: 40000
Message: Connection authentication failure occurred. Reason: Invalid authentication..
Database did not shut down normally
Derby system shut down normally
I tried several things to make it shut down. What worked eventually was to not delete one user, and to shut down the database specifying both the user name and the password for that user. I used mary, though I'm sure I could have used sa too. I wanted to make sure that the user at the first database shutdown doesn't have to be the same as the user for the second shutdown.
Does this make sense? If so I will edit the plain authentication example and also use the same process for the SQL authorization embedded program. I'm attaching the actual program I ran (AuthExampleEmbedded_dhw.java).
> 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, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, DERBY-3200-2.diff, DERBY-3200-2.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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-3200:
-----------------------------
Derby Info: [Patch Available]
> 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, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, 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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-3200:
-----------------------------
Attachment: AuthExampleEmbeddedSQLAuth.java
AuthExampleClientSQLAuth2.java
AuthExampleClientSQLAuth1.java
Attaching latest versions of the test programs.
> 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, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, 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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12624081#action_12624081 ]
Kim Haase commented on DERBY-3200:
----------------------------------
Wow, thanks, Dag, for looking at this so carefully and finding all these improvements.
Since you created the "dbOwner" variable at the beginning, would it make sense to replace other occurrences of "mary" in strings with this variable? I think you used it only at the beginning. I could fix it elsewhere in the main program.
Would you like to make similar fixes to the client programs or would you rather I did it? Many of the fixes may also apply to the other authentication examples (the ones that don't use SQL authorization).
The changed code needs some further edits to keep the line lengths within 72 characters. Longer lines wrap badly in the PDF manuals.
> 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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-3200:
-----------------------------
Attachment: AuthExampleEmbedded.java
AuthExampleClient2.java
AuthExampleClient1.java
Attaching the latest versions of the basic authentication and authorization examples, AuthExampleClient1.java, AuthExampleClient2.java, and AuthExampleEmbedded.java.
> 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, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, 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.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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12612867#action_12612867 ]
Dag H. Wanvik commented on DERBY-3200:
--------------------------------------
Btw, I think in general you can limit your example to catch SQLException (or Exception).
It is seldom useful to catch Throwable.
> 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, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, DERBY-3200-2.diff, DERBY-3200-2.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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-3200:
-----------------------------
Attachment: (was: AuthExampleClientSQLAuth1.java)
> 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, AuthExampleClient2.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, DERBY-3200-2.diff, DERBY-3200-2.zip, DERBY-3200-3.diff, DERBY-3200-3.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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-3200:
-----------------------------
Attachment: DERBY-3200-5.zip
DERBY-3200-5.diff
Attaching DERBY-3200-5.diff and DERBY-3200-5.zip.
After more consultation, I decided it makes sense to call Class.forName(driver).newInstance() only when it is necessary: it's necessary only if you use the embedded driver, and then only when you shut down Derby and restart it in the middle of the application. It's not necessary to call it from any of the sample programs in these examples.
I've done a little more tidying up of the comments and tested the examples using both JDK 5 and JDK 6.
Revised examples to follow.
> 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, AuthExampleClient2.java, AuthExampleClient2.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, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, 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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12613603#action_12613603 ]
Dag H. Wanvik commented on DERBY-3200:
--------------------------------------
Right, what happens is that when you try to close down the first time
you need to use one of the the users you have defined (although no password is required yet, since
derby.connection.sqlAuthentication is not yet in effect until we reboot).
This is because you also set the default accessmode to noAccess, which is effective immediately.
So, by changing the first shutdown statement to:
DriverManager.getConnection("jdbc:derby:authEmbDB;user=sa;shutdown=true");
You should get the expected exception 08006. Sorry I didn't notice the first time :)
> 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, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, DERBY-3200-2.diff, DERBY-3200-2.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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-3200:
-----------------------------
Attachment: AuthExampleEmbeddedSQLAuth.java
AuthExampleClientSQLAuth2.java
AuthExampleClientSQLAuth1.java
Attaching latest SQL authorization examples.
> 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, AuthExampleClient1.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClientSQLAuth1.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, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.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-6.diff, DERBY-3200-6.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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12603272#action_12603272 ]
Dag H. Wanvik commented on DERBY-3200:
--------------------------------------
The default value would still apply, even if the property isn't set. If you did not set the value programmatically, you cannot retrieve it either, so this is not a bug. This caused some problems for the JMX monitoring, if I remember correctly.
> 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, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, 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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12626980#action_12626980 ]
Kim Haase commented on DERBY-3200:
----------------------------------
Thanks again, Dag! I seem to be always missing something. What I am going to do is commit what I have so far and then make the changes, so that the diffs files will not be so unwieldy and the changes will be easier to see. Then I will make your fixes.
Yes, the reason there are two client programs and only one embedded is arbitrary at this point. At some point during my work on DERBY-1823 I think the programs were written in such a way that they only worked the way I had them, but that is no longer the case. I combined the two client examples into one and it worked fine. I split the embedded example into two and that also worked fine. I should say that we just want to show users that they can do it either way.
I also realize that it may be confusing to point to the section on SQL authorization in the topics that have the non-SQL-authorization examples. I'll pull that out and leave it only in the SQL authorization examples.
I am not sure what you mean by "levels of authorization" -- full access and read only access? The topics in which the first (non-SQL-auth) examples are nested talk about setting system properties and database properties and also about what the full-access and read-only-access authorizations mean. It would be useful to add pointers to the documentation on properties there, but I'll save that for DERBY-2389.
I also noticed a comment from Francois on DERBY-1823 (about where to point to in the Getting Started guide) that I missed before, so I'll make that fix too.
> 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, AuthExampleClient1.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClientSQLAuth1.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, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.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-6.diff, DERBY-3200-6.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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-3200:
-----------------------------
Attachment: DERBY-3200-7.stat
DERBY-3200-7.zip
DERBY-3200-7.diff
Attaching DERBY-3200-7.diff, DERBY-3200-7.zip, and DERBY-3200-7.stat, with the following incremental fixes:
- Referred more precisely to topic in Getting Started, in client examples
- Explained that the one-program vs. two-program structure of the examples was not significant, merely showing two ways to do the same thing
- Put current directory in classpath for examples
(it's the only essential item other than the relevant Derby JAR file)
- Put comments on exception reporting methods in standard javadoc format
- Removed link to SQL authorization topic from the examples that don't use SQL authorization
> 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
> Affects Versions: 10.4.1.3
> Reporter: Kim Haase
> Assignee: Kim Haase
> Priority: Minor
> Attachments: auth2.log, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClientSQLAuth1.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, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.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-6.diff, DERBY-3200-6.zip, DERBY-3200-7.diff, DERBY-3200-7.stat, DERBY-3200-7.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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-3200:
---------------------------------
Attachment: AuthExampleEmbedded-dhw.java
> 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, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, DERBY-3200-2.diff, DERBY-3200-2.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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ 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
More new source files. I think I will delete some of the older versions of these.
> 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, AuthExampleClient2.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, DERBY-3200-2.diff, DERBY-3200-2.zip, DERBY-3200-3.diff, DERBY-3200-3.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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-3200:
---------------------------------
Attachment: AuthExampleClientSQLAuth2.java
AuthExampleClientSQLAuth1.java
I upload slightly modified versions of your programs, Kim.
You were almost there; what was missing was that you need to create
the APP user also in program one. This is required, or you paint
yourself into a corner, not being able to shut down; which, as you
state, can only be done by database owner now that authentication and
authorization is enabled.
The alternative is to specify the user you plan to user as dbo
database owner when you first create the database (even before the
users are created, say as "create=true;user=mary").
Note also that the bug DERBY-3150 forces us to specify app (and app's
password) with lower case in the shutdown url (program 2).
> 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, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, 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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-3200:
-----------------------------
Derby Info: (was: [Patch Available])
Committing current patch so as to make diffs easier to work with.
Committed patch DERBY-3200-6.diff to documentation trunk at revision 690270.
> 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, AuthExampleClient1.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClientSQLAuth1.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, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.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-6.diff, DERBY-3200-6.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.
[jira] Closed: (DERBY-3200) Developer's Guide: Add examples showing
use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase closed DERBY-3200.
----------------------------
Updates appear in Latest Alpha Manuals doc, so this issue can be closed.
> 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
> Affects Versions: 10.4.1.3
> Reporter: Kim Haase
> Assignee: Kim Haase
> Priority: Minor
> Fix For: 10.5.0.0
>
> Attachments: auth2.log, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient1.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClient2.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.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, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbedded-dhw.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded.java, AuthExampleEmbedded_dhw.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.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-6.diff, DERBY-3200-6.zip, DERBY-3200-7.diff, DERBY-3200-7.stat, DERBY-3200-7.zip, DERBY-3200-8.diff, DERBY-3200-8.stat, DERBY-3200-8.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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-3200:
-----------------------------
Attachment: DERBY-3200.zip
DERBY-3200.stat
DERBY-3200.diff
Here are the new topics that describe the SQL authorization examples. I've also made some tweaks to the earlier examples (the ones these are based on), mainly some formatting fixes (so the code fits in the PDF version's shorter page width) plus some clarifications and more distinctive names for the databases.
The files are DERBY-3200.diff, DERBY-3200.stat, and DERBY-3200.zip.
I haven't added the fixes about the default connection mode (yet).
> 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, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth1.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleClientSQLAuth2.java, AuthExampleEmbeddedSQLAuth.java, AuthExampleEmbeddedSQLAuth.java, 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.
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12542553 ]
Kim Haase commented on DERBY-3200:
----------------------------------
Thanks very much for the help, Dag. That works! I didn't think you could log in as a user that hadn't been created yet -- but apparently you can.
I'll start working on topics for this material now.
> 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: AuthExampleEmbeddedSQLAuth.java
>
>
> 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.
[jira] Commented: (DERBY-3200) Developer's Guide: Add examples
showing use of SQL authorization with user authentication
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12624159#action_12624159 ]
Kim Haase commented on DERBY-3200:
----------------------------------
Thanks, Dag! That's very helpful.
Only the database owner can shut down the database -- so that's probably the only other place in these programs where replacing "mary" with "dbOwner" would make sense. (We're not doing anything with encryption or upgrades.)
I'll see about creating a method for cleanup and shutdown.
> 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.