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')
Related concepts
Configuring coarse-grained user authorization