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.

The following example shows how to do this. The example assumes that you are reusing the credentials-protected database you created in Configuring NATIVE authentication. 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.

Related concepts
Configuring fine-grained user authorization