Now the Database Owner creates an encrypted database, turns on coarse-grained authorization, and creates some data that everyone can read but only he can write.
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'.