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 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
, 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
. 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')