Securing Schemas in Pivotal SQLFire

Authentication/authorization is off by default in SQLFire if servers have not specified it. That is why SQLFire connections don't need a user/password if not configured explicitly on server. Once authorization has been configured on server, users will have access to only their database objects. This is illustrated below. In this example we use PLAIN text for the passwords just to illustrate how we secure a schema

1. Create a locator script as follows

sqlf locator start -user=admin -password=adminpassword -sqlfire.sql-authorization=true -peer-discovery-address=Pas-Apicellas-MacBook-Pro.local -peer-discovery-port=41111 -client-bind-address=Pas-Apicellas-MacBook-Pro.local -client-port=1527 -dir=locator

2. Create a server start script as follows

sqlf server start $JVM_OPTS -user=admin -password=adminpassword -sqlfire.sql-authorization=true -max-heap=$MAXHEAP -initial-heap=$MINHEAP -critical-heap-percentage=85 -eviction-heap-percentage=75 -server-groups=MYGROUP -conserve-sockets=false -locators=Pas-Apicellas-MacBook-Pro.local[41111] -client-bind-address=Pas-Apicellas-MacBook-Pro.local -client-port=1528 -dir=server1

Note: Ensure that a sqlfire.properties file exists for the locator/server as shown below. 

sqlfire.properties

sqlfire.auth-provider=BUILTIN
sqlfire.sql-authorization=true
sqlfire.user.admin=adminpassword
sqlfire.user.locatoradmin=adminpassword
sqlfire.user.serveradmin=adminpassword

3. As the admin using connect with sqlf and create a database user account which will be the schema owner.

  
sqlf> connect client '127.0.0.1:1527;user=admin;password=adminpassword';
sqlf> CALL SYS.CREATE_USER('pas', 'pas');
Statement executed. 

4. Connect with a JDBC client as shown below.

  
private Connection getConnection() throws SQLException
 {
  Connection conn = null;
  conn = DriverManager.getConnection
      ("jdbc:sqlfire://127.0.0.1:1527/", "pas", "pas");
  return conn; 
 }

When the JDBC connection connects successfully it will then be in the context of the schema "pas" and will have full rights / access to that schema to be able to create database objects etc.

If we provide an invalid username/password we get an error as follows, hence securing access to the schema with a built in username we created at step #3 above.


Exception in thread "main" java.sql.SQLNonTransientConnectionException: Connection authentication failure occurred.  Reason: userid 'pas' or password invalid.