# 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.

- Read-only and full access permissions
  The actions that users can perform on a Derby database with coarse-grained authorization are determined by the type of access that users have to the database objects.
- Setting the default connection access mode
  You can use the derby.database.defaultConnectionMode property to specify the default type of access that users have when they connect to the database.
- User authorization exceptions
  SQL exceptions are returned when errors occur with coarse-grained user authorizations.

Parent topic: Configuring user authorization
Related concepts
Configuring fine-grained user authorization