SQL Anywhere 17 – Database Isolation
By default, when you run a database server, you have access to all features of the server, regardless of whether or not you actually use them. Some of these features can also be leveraged by 3rd parties (malicious or not) to do things you did not intend as part of your application deployment. For example, a 3rd party could compromise the security of a machine by using the database servers’ abilities to access the file system, send e-mail, or call web services.
If you are using these features in your application, you are most likely aware of the possibilities for mis-use, and can take appropriate steps to mitigate them, but if you are not using these features, you may not even be aware of them until a problem occurs.
In version 10, in order to prevent 3rd parties from supplanting a deployed SQL Anywhere server and mis-using it, the SQL Anywhere added the ability for developers to better control what features of the server were available by default when they deploy their applications. This is called the “Secure Feature” feature. It allows you to enable/disable specific features of the database server. For example, if you never use the xp_start/stop/sendmail procedures, you can disable that feature when you run the server.
Secure features are enabled via a commandline switch (-sf <features>). You can also provide a key (-sk <key>), which can be used to enable/disable specific features at runtime. There are a number of features which can be controlled, which is discussed here:
To demonstrate how the Secure Feature feature works, we will take a look at a new secure feature added to SQL Anywhere 17 that allows you to enable database isolation. If enabled, each database running on a database server acts as if it is the only database running on that database server. Connections to one database are not allowed to query properties or connection information for any other database running on the same server, nor are they allowed to start or stop other databases.
A new server command line option -edi (enable database isolation) has been added to turn database isolation on. There is also a new secure feature database_isolation which allows applications to turn database isolation off for a given connection. This new secure feature is on by default and can only be turned off for a specific database, not globally.
For this example I’ve created 2 databases, demo.db and demoprime.db, If we start a server with these two databases running:
dbsrv17 -n J -edi demo.db demoprime.db
And then connect to it from dbisql, running the statement “SELECT * FROM sa_db_info()” shows us that we can “see” both databases running on the server:
A user can leverage the knowledge of what databases are running on a server to look up information about the other databases running (database and connection properties and information about the database file for example). To prevent this from happening, we can run the server with database isolation enabled:
dbsrv17 -n J -edi demo.db demoprime.db
Connecting to the demo database from dbisql and issuing the same “SELECT * FROM sa_db_info()” statement only tells us about the database we are connected to.
The results would be similar for other system procedures like sa_conn_info(), sa_db_properties(), etc… There is no way for a connected user to ‘see’ any information for other databases running on the server.
But what if you wanted to allow an admin user to access this information? The way the server is currently being run, there is no way to disable database isolation without restarting the server with different options. To allow the database isolation feature to be disabled for a connection, you must use the sf/sk options to set a key. The key can be used to modify secure features in a running server.
dbsrv17 -n J –edi -sf none -sk mysfkey demo.db demoprime.db
With our server running and database isolation enabled, we cannot access information on any other databases running on the server. To disable database isolation for a connection, we can execute the following procedure call to allow us to manage keys for secure feature access.
CALL sp_use_secure_feature_key( 'system', 'mysfkey' );
We then create a key to associate with the database_isolation feature:
CALL sp_create_secure_feature_key( 'dbiso', 'dbisokey', 'database_isolation' );
Now, we can distribute the key to our administrative users, and they can use it to disable the database_isolation feature for their connection:
CALL sp_use_secure_feature_key( 'dbiso', 'dbisokey' );
In order to be able to re-enable database isolation for the connection, we can create another key, and use it:
CALL sp_use_secure_feature_key( 'system', 'mysfkey' ); CALL sp_create_secure_feature_key( 'disabledbiso', 'dbisokey', '-database_isolation' ); CALL sp_use_secure_feature_key( 'disabledbiso', 'dbisokey' );
Note that with database isolation turned on applications can still connect to the utility database with the appropriate utility userid and password and can perform utility actions; however, starting and stopping databases while connected to the utility database will still be restricted and applications will still need to use the manage_database_isolation secure feature in order to start and stop databases.