# Creating and using a secure database

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

Parent topic: Putting it all together
Related concepts
Starting a secured Network Server
Stopping the secured Network Server