Configuring coarse-grained user authorization You can manipulate coarse-grained access by using the builtin procedure SYSCS_SET_DATABASE_PROPERTY to set the database properties derby.database.fullAccessUsers and derby.database.readOnlyAccessUsers. databasescoarse-grained authorization, configuring authorizationcoarse-grained derby.database.fullAccessUsers property propertiesderby.database.fullAccessUsers derby.database.readOnlyAccessUsers property propertiesderby.database.readOnlyAccessUsers

The following example shows how to do this. The example assumes that you are reusing the credentials-protected database you created in . The example commands first set the read/write and read-only users and then verify that the settings work correctly.

java org.apache.derby.tools.ij ij> ij version 10.11 ij> connect 'jdbc:derby:testdb;user=tquist;password=tquist'; ij> -- -- Prevent our settings from being overridden on the -- command line or in derby.properties. -- call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY ( 'derby.database.propertiesOnly', 'true' ); 0 rows inserted/updated/deleted ij> -- -- Now we can configure read/write and read-only users. -- call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY ( 'derby.database.fullAccessUsers', 'tquist,mchrysta' ); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY ( 'derby.database.readOnlyAccessUsers', 'thardy,jhallett' ); 0 rows inserted/updated/deleted ij> -- -- Next verify that a read/write user has those powers: -- connect 'jdbc:derby:testdb;user=mchrysta;password=mchrysta'; ij(CONNECTION1)> create table mchrysta.t1( a varchar( 20 ) ); 0 rows inserted/updated/deleted ij(CONNECTION1)> insert into mchrysta.t1( a ) values ( 'mchrysta' ); 1 row inserted/updated/deleted ij(CONNECTION1)> select * from mchrysta.t1; A -------------------- mchrysta 1 row selected ij(CONNECTION1)> -- -- Finally, verify that a read-only user can read data but not write it: -- connect 'jdbc:derby:testdb;user=thardy;password=thardy'; ij(CONNECTION2)> -- the user can select from public data select count(*) from sys.systables; 1 ----------- 24 1 row selected ij(CONNECTION2)> -- but this user can't even create a table create table thardy.t1( a varchar( 20 ) ); ERROR 25503: DDL is not permitted for a read-only connection, user or database.
Coarse-grained authorization details

Use a CALL statement to call the SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY system procedure.

To specify multiple user IDs, use a comma-separated list, with no spaces between the comma and the next user ID.

To specify read-write access for a user ID that contains special characters, use delimited identifiers for the user ID. For example:

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY( 'derby.database.fullAccessUsers', '"Elena!"')

For extra security, you should configure the derby.database.propertiesOnly property so that users cannot override database behavior using system-wide properties specified on the command line or in the derby.properties file.