# Configuring fine-grained user authorization

You can use fine-grained user authorization, also called SQL standard authorization, to restrict access to specific pieces of data.

You can use fine-grained authorization by itself or in conjunction with coarse-grained authorization.

Fine-grained authorization, like coarse-grained authorization, requires that we run Derby with authentication turned on. If you are using LDAP authentication, then you will need to enable fine-grained authorization by setting the `derby.database.sqlAuthorization` property to `true`.

The following example uses the same database you created in Configuring NATIVE authentication, the database that relies on NATIVE authentication. If you use NATIVE authentication, there is no need to set the `derby.database.sqlAuthorization` property. NATIVE authentication automatically enables fine-grained authorization.

The example creates two tables. One table can be viewed by anyone. The other table can be viewed only by specific users.

    java org.apache.derby.tools.ij
    ij version 10.11
    ij> connect 'jdbc:derby:testdb;user=mchrysta;password=mchrysta';
    ij> -- create and populate some tables
    create table publicTable( a int );
    0 rows inserted/updated/deleted
    ij> create table restrictedTable( a int );
    0 rows inserted/updated/deleted
    ij> insert into publicTable( a ) values ( 1 );
    1 row inserted/updated/deleted
    ij> insert into restrictedTable( a ) values( 100 );
    1 row inserted/updated/deleted
    ij> -- set up fine-grained checks
    grant select on publicTable to public;
    0 rows inserted/updated/deleted
    ij> grant select on restrictedTable to thardy;
    0 rows inserted/updated/deleted
    ij> --
    --Now verify that thardy can view both tables...
    --
    connect 'jdbc:derby:testdb;user=thardy;password=thardy';
    ij(CONNECTION1)> select * from mchrysta.publicTable;
    A
    -----------
    1

    1 row selected
    ij(CONNECTION1)> select * from mchrysta.restrictedTable;
    A
    -----------
    100

    1 row selected
    ij(CONNECTION1)> --
    -- ...but other users can only view the public table:
    --
    connect 'jdbc:derby:testdb;user=jhallett;password=jhallett';
    ij(CONNECTION2)> select * from mchrysta.publicTable;
    A
    -----------
    1

    1 row selected
    ij(CONNECTION2)> select * from mchrysta.restrictedTable;
    ERROR 42502: User 'JHALLETT' does not have SELECT permission on column 'A' of
    table 'MCHRYSTA'.'RESTRICTEDTABLE'.

You can also use the GRANT command to restrict write access to your tables, to control who executes your functions and procedures, to limit who can add triggers to your tables, and to limit who can create foreign keys referencing your tables. You can also control users' ability to create, set, and drop roles.

Coarse-grained and fine-grained authorization are not mutually exclusive. You may want to configure both. Using coarse-grained authorization, you can prevent truly read-only users from creating and populating any table; this defends your database against an unbounded growth vulnerability (see Vulnerabilities of unsecured databases. Using additional fine-grained authorization checks prevents your read-write users from accessing restricted data.

After the `derby.database.sqlAuthorization` property has been set to `true`, you cannot set the property back to `false`.

You can set the `derby.database.sqlAuthorization` property as a system property or as a database property. If you set this property as a system property before you create the databases, all new databases will automatically have SQL authorization enabled:

    derby.database.sqlAuthorization=true

If the databases already exist, you can set this property only as a database property:

    CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
        'derby.database.sqlAuthorization',
        'true')

- Using fine-grained user authorization
  When the SQL standard authorization mode is enabled, object owners can use the GRANT and REVOKE SQL statements to set the user privileges for specific database objects or for specific SQL actions. They can also use roles to administer privileges.
- Privileges on views, triggers, constraints, and generated columns
  Views, triggers, constraints, and generated columns operate with the privileges of the owner of the view, trigger, constraint, or generated column.
- Using SQL roles
  When the SQL standard authorization mode is enabled, object owners can use the SQL roles facility to administer privileges.
- Upgrading an old database to use SQL standard authorization
  An old, unprotected database can be shielded with authentication and SQL authorization later on.
- SQL standard authorization exceptions
  SQL exceptions are returned when errors occur with SQL authorization.
- NATIVE authentication and SQL authorization example
  This example consists of the program NativeAuthenticationExample.java, which shows how to use Derby's NATIVE user authentication and SQL authorization with either the embedded or the client driver.

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