restSQL Security

restSQL access may be encrypted, authenticated, authorized and validated. The JEE Container provides encryption and authentication. restSQL provides resource authorization capability, ensuring users only access the SQL Resources and methods appropriate to their assigned role(s). Applications provide Java-based Triggers to perform input validation. The framework protects against SQL Injection with input parameter type checking and prepared statements for database access.

Encryption and Authentication

The JEE Web Application Deployment Descriptor, web.xml, provides declarative mechanisms to enable encryption and authentication. restSQL's web.xml may be configured to match application needs. See the default web.xml. For a complete tutorial, see Web Application Security using Deployment Descriptors.

The <login-config> element defines the HTTP authentication method (BASIC or DIGEST) and the realm. Here is an example of BASIC authentication with Tomcat's file-based realm, UserDatabase:

   <login-config>
      <auth-method>BASIC</auth-method>
      <realm-name>UserDatabase</realm-name>
   </login-config>

The UserDatabase realm is configured using the $TOMCAT_HOME/conf/tomcat-users.xml file. The file defines the roles, user names, passwords and role assignments. File-, RDBMS- and LDAP-backed implementations are also available in most JEE containers. See your container's documentation for instructions.

The Deployment Descriptor's <security-role> element defines the roles. For example:

   <security-role>
       <role-name>all</role-name>
   </security-role>
   <security-role>
       <role-name>limited</role-name>
   </security-role>
   <security-role>
       <role-name>readonly</role-name>
   </security-role>  
   <security-role>
       <role-name>admin</role-name>
   </security-role>

The <security-constraint> element defines URLs to be protected, the roles that may access them and the transport guarantee (NONE = HTTP, CONFIDENTIAL = HTTPS). Following is an example that declares the "all", "limited", and "readonly" roles may access /res/*, or all SQL Resources. (The next section will describe how those roles are mapped to SQL Resources and operations). The example also coerces all connections to HTTPS, i.e. HTTP over SSL/TLS.

   <security-constraint>
       <web-resource-collection>
           <web-resource-name>SQL Resources</web-resource-name>
           <url-pattern>/res/*</url-pattern>
       </web-resource-collection>
       <auth-constraint>
           <role-name>all</role-name>
           <role-name>limited</role-name>
           <role-name>readonly</role-name>
       </auth-constraint>
       <user-data-constraint>
           <transport-guarantee>CONFIDENTIAL</transport-guarantee>
       </user-data-constraint>
   </security-constraint>

The following <security-constraint> requires the "admin" role for all administrative URLs and no encryption:

   <security-constraint>
       <web-resource-collection>
           <web-resource-name>Admin Resources</web-resource-name>
           <url-pattern>/conf/*</url-pattern>
           <url-pattern>/log/*</url-pattern>
           <url-pattern>/stats/*</url-pattern>
       </web-resource-collection>
       <auth-constraint>
           <role-name>admin</role-name>
       </auth-constraint>
       <user-data-constraint>
           <transport-guarantee>NONE</transport-guarantee>
       </user-data-constraint>
   </security-constraint>

Authorization

Role to SQL Resource and request type assignments (privileges) are provided via a simple configuration file. The restsql.properties defines the absolute path to the privileges configuration file. Here is an example:

   # security.privileges=/absolute/path
   security.privileges=/etc/opt/business/restsql/privileges.properties

The privileges.properties file concisely defines the operations on SQL Resources available to each role. Here is an example file:

   # SQL Resource Privilege definitions
   
   # [SqlResource,*].[requestType,*]=[*,role]      Note: comma-separate multiple roles
   
   Country.select=*                     select on all Country to all roles
   *.select=all,limited,readonly        select on all SQL Resources to "all", "limited" and "readonly" roles
   *.insert,update,delete=all           insert, update and delete methods on all SQL Resources to the "all" role
   Film.insert=creator                  insert method on Film to the "editor" role
   Film.insert,update,delete=editor     insert, update and delete methods on Film to the "editor" role

Privileges properties control access to /res/* URLs only. Authorization of admin resources (/conf/*, /log/*, /stats/*) is controlled solely by the Deployment Descriptor (web.xml) constraints.

Invalid Definitions: Any invalid definitions are discarded, with a warning in the internal.log. If no valid definitions are found, or the file is is not found, an error appears in the internal.log and no operations are permitted on an any SQL Resources.

Disabling Authorization: If security.privileges is missing from the restsql.properties, authorization is disabled and all operations are permitted regardless of user role.

Access http://yourhost:port/restsql for links to the effective runtime configuration.

In restSQL WAR mode, authorization is implicit in HTTP requests. In restSQL Jar mode, authorization is explicit. Neither Request nor SQL Resource methods are protected. The app must create a SecurityContext implementation and pass it as well as the Request to the Authorizer , as in Authorizer.isAuthorized(securityContext, request) .

Realm Configuration

Following is an example of the UserDatabase realm configuration in $TOMCAT_HOME/conf/tomcat-users.xml file:

   <tomcat-users>
      <role rolename="all"/>
      <role rolename="limited"/>
      <role rolename="readonly"/>
      <role rolename="admin"/>
      <user username="all" password="all" roles="all"/>
      <user username="limited" password="limited" roles="limited"/>
      <user username="readonly" password="readonly" roles="readonly"/>
      <user username="admin" password="admin" roles="admin"/>
   </tomcat-users>

File-, RDBMS- and LDAP-backed implementations are also available in most JEE containers. See your container's documentation for instructions.

Input Validation

Server-side input validation (data types, values, etc.) may be provided by application-provided, Java-based Triggers.

restSQL protects against SQL Injection with input parameter type checking and prepared statements for database access. The value portion of an SQL expression is set as a question mark (and with PostgreSQL a type case, e.g. ?::integer ) and a JDBC method setXxx() is called with the value. The database will process column values as values. Harmful execution statements cannot be injected into restSQL parameter values. Addtionally the framework now converts all String input parameter values for numeric and boolean column type to Inter, Long, Float, Double or Boolean. An exception is thrown if the value cannot be converted.