[jira] Updated: (DERBY-3200) Developer's Guide: Add examples showing use of SQL authorization with user authentication
[ 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. Bu
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples showing use of SQL authorization with user authentication
[ 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
[ 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] Updated: (DERBY-3200) Developer's Guide: Add examples showing use of SQL authorization with user authentication
[ 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] Updated: (DERBY-3200) Developer's Guide: Add examples showing use of SQL authorization with user authentication
[ 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
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples showing use of SQL authorization with user authentication
[ 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] Updated: (DERBY-3200) Developer's Guide: Add examples showing use of SQL authorization with user authentication
[ 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] Updated: (DERBY-3200) Developer's Guide: Add examples showing use of SQL authorization with user authentication
[ 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
[ 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
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples showing use of SQL authorization with user authentication
[ 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 writ
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples showing use of SQL authorization with user authentication
[ 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] Updated: (DERBY-3200) Developer's Guide: Add examples showing use of SQL authorization with user authentication
[ 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
[ 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] Updated: (DERBY-3200) Developer's Guide: Add examples showing use of SQL authorization with user authentication
[ 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] Updated: (DERBY-3200) Developer's Guide: Add examples showing use of SQL authorization with user authentication
[ 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] Updated: (DERBY-3200) Developer's Guide: Add examples showing use of SQL authorization with user authentication
[ 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] Updated: (DERBY-3200) Developer's Guide: Add examples showing use of SQL authorization with user authentication
[ 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] Updated: (DERBY-3200) Developer's Guide: Add examples showing use of SQL authorization with user authentication
[ 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] Updated: (DERBY-3200) Developer's Guide: Add examples showing use of SQL authorization with user authentication
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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: 4 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
[ 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
[ 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] Updated: (DERBY-3200) Developer's Guide: Add examples showing use of SQL authorization with user authentication
[ 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] Updated: (DERBY-3200) Developer's Guide: Add examples showing use of SQL authorization with user authentication
[ 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
[ 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] Updated: (DERBY-3200) Developer's Guide: Add examples showing use of SQL authorization with user authentication
[ 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
[ 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
[ 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-00134f30|SYSCS_UTIL |APP 8000-00d2-b38f-4cda-000a0a412c00|APP |APP 0ddd00a9-011a-351d-3249-d494cc61|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 o
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples showing use of SQL authorization with user authentication
[ 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] Updated: (DERBY-3200) Developer's Guide: Add examples showing use of SQL authorization with user authentication
[ 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
[
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
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples showing use of SQL authorization with user authentication
[ 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
[ 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 abili
[jira] Updated: (DERBY-3200) Developer's Guide: Add examples showing use of SQL authorization with user authentication
[ 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.
