Fine-grained authorization is automatically turned on because we are using
NATIVE authentication.
Connection URLs are shown on multiple lines, but must be entered on one
line.
java -Djavax.net.ssl.trustStore=/Users/me/vault/ClientTrustStore \
-Djavax.net.ssl.trustStorePassword=secretClientTrustStorePassword \
-Djavax.net.ssl.keyStore=/Users/me/vault/ClientKeyStore \
-Djavax.net.ssl.keyStorePassword=secretClientPassword \
org.apache.derby.tools.ij
ij version 10.9
ij> connect 'jdbc:derby://localhost:8246/mchrystaEncryptedDB;create=true;
user=mchrysta;password=mchrysta;dataEncryption=true;
encryptionAlgorithm=Blowfish/CBC/NoPadding;
bootPassword=mySuperSecretBootPassword;ssl=peerAuthentication';
ij> --
-- Prevent our authentication properties from being overridden on the
-- command line or in derby.properties.
--
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY
( 'derby.database.propertiesOnly','true');
Statement executed.
ij> --
-- This time around, there is no need to add credentials for the database
-- owner. That is because the database owner's credentials were
-- automatically added when we created the NATIVE database, advertised to
-- the server by setting
-- -Dderby.authentication.provider=NATIVE:mchrystaEncryptedDB.
--
--call SYSCS_UTIL.SYSCS_CREATE_USER( 'mchrysta', 'mchrysta' );
-- now add other users
call SYSCS_UTIL.SYSCS_CREATE_USER( 'thardy', 'thardy' );
Statement executed.
ij> call SYSCS_UTIL.SYSCS_CREATE_USER( 'jhallett', 'jhallett' );
Statement executed.
ij> call SYSCS_UTIL.SYSCS_CREATE_USER( 'tquist', 'tquist' );
Statement executed.
ij> --
-- Turn on coarse-grained authorization
--
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY
( 'derby.database.fullAccessUsers', 'tquist,mchrysta' );
Statement executed.
ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY
( 'derby.database.readOnlyAccessUsers', 'thardy,jhallett' );
Statement executed.
ij> --
-- Shut down the database and bring it back up. This will turn on NATIVE
-- authentication and fine-grained authorization.
--
connect 'jdbc:derby://localhost:8246/mchrystaEncryptedDB;shutdown=true;
user=mchrysta;password=mchrysta;ssl=peerAuthentication';
ERROR 08006: DERBY SQL error: SQLCODE: -1, SQLSTATE: 08006, SQLERRMC:
Database 'mchrystaEncryptedDB' shutdown.
ij> --
-- Reboot the encrypted, password-protected database.
--
connect 'jdbc:derby://localhost:8246/mchrystaEncryptedDB;user=mchrysta;
password=mchrysta;bootPassword=mySuperSecretBootPassword;
ssl=peerAuthentication';
ij(CONNECTION1)> --
-- Create some data and let everyone see it.
--
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)> grant select on table mchrysta.t1 to public;
0 rows inserted/updated/deleted
ij(CONNECTION1)> --
-- Verify that another user can read the newly created data but not write
-- it:
--
connect 'jdbc:derby://localhost:8246/mchrystaEncryptedDB;user=tquist;
password=tquist;ssl=peerAuthentication';
ij(CONNECTION2)> --
-- Verify that this user can see the data ...
--
select * from mchrysta.t1;
A
--------------------
mchrysta
1 row selected
ij(CONNECTION2)> --
-- ... but not write the data:
--
insert into mchrysta.t1( a ) values ( 'tquist' );
ERROR 42500: User 'TQUIST' does not have INSERT permission on table
'MCHRYSTA'.'T1'.