To improve security, SQL Anywhere 17 has made a number of new changes related to how passwords are managed and accessed via the various tools and utilities.  I have included here a brief description of the changes.

Direct Access to Password Hashes in System Tables

As a best practice for database security, access to password hash values in the database  should require two separate actors – an administrator (the user with SELECT ANY TABLE privilege) and a security officer (the user with the ACCESS  USER PASSWORD admin privilege).

In order to better protect passwords stored in the database, in version 17 the server will no longer return even the hashes for passwords in queries.  The following views will have password-containing column(s) replaced with ‘***’ for all users:

• SYSUSER
• SYSUSERPERM
• SYSUSERAUTH
• SYSEXTERNLOGIN
• SYSLDAPSERVER
• SYSSYNC2

The following synchronization-related views will now select from SYSSYNC2 view rather than ISYSSYNC table and will, as a result, have sensitive columns replaced with ‘***’:

• SYSSYNCS
• SYSSYNCUSERS
• SYSSYNCPUBLICATIONDEFAULTS
• SYSSYNCSUBSCRIPTIONS
• SYSSYNCPROFILE

Access to the actual password hashes and password values stored in the database will now require two privileges: SELECT ANY TABLE privilege and the new ACCESS USER PASSWORD privilege.  The “ACCESS USER PASSWORD” privilege allows a user to access views that contain password hashes (see list below), and perform operations that involve accessing passwords, such as unloading, extracting, or comparing databases.

The privilege ACCESS USER PASSWORD along with SELECT ANY TABLE is required to access the following views that report sensitive information or passwords:

• SYSSYNC
• SYSSYNCPROFILE
• SYSUSERPASSWORD
• SYSLDAPSERVERPASSWORD
• SYSEXTERNUSERPASSWORD

As a result of these changes, you may notice a difference in the behaviour of the Sybase Central schema diff utility, as well as Copy/Paste options for user, external login, LDAP server and synchronization definition options.

DBUnload/DBXTRACT Changes

By default, passwords are now no longer unloaded by DBUnload or DBXtract.  DBUnload should only attempt to unload password hashes when the result is to be reloaded into a database with the purpose of recreating the database with the same (or slightly modified) schema and data.  When a database is unloaded without passwords, GRANT CONNECT, CREATE EXTERNLOGIN and CREATE LDAP SERVER statements will not have the IDENTIFIED BY clause specified. A GRANT CONNECT statement for default DBA user will be added (with default password).

DBUnload and DBXtract utilities will unload passwords if the –up option is specified, and the user performing the unload/extract has the appropriate privileges (see below).  The –up option is implied if you use any of the reload options (-ac, -an, -aob, etc…)

DBUNLOAD with the –no option (used when performing database schema comparisons) will never unload password hashes and values containing passwords.

For an unload with a reload into a new database (-an, -ao, -aob), you must have the SELECT ANY TABLE, SERVER OPERATOR, and ACCESS USER PASSWORD system privileges. For an unload with a reload into an existing database (-ac) you do not need the SERVER OPERATOR system privilege.  It is recommended that the user doing the rebuild only be granted both SELECT ANY TABLE and ACCESS USER PASSWORD temporarily for the duration of the rebuild process.  Note that the compatibility DBA user has all roles necessary to perform any of the above operations.

Improvements to the EncryptedPassword Connection Parameter

While the best practice for security is to never store passwords as part of a DSN definition (or elsewhere on a client computer), many developers do this in order to make application deployment and management easier.  In these cases, in order to prevent a user from seeing a plain text password on their local machine (in a DSN definition for example), SQL Anywhere provides the EncryptedPassword (ENP) connection parameter that can be used as a substitute for the Password (PWD) connection password.

The intent of the ENP connection parameter is to disguise the actual password used to authenticate to a database. However, prior to version 17, the encrypted password is subject to abuse.  For example, it is obfuscated and so could be decrypted with a little effort.  In addition, the ODBC administrator could be used to convert an encrypted password back to a plaintext password.

Note: While described as a new feature for version 17, the changes to encryptedpassword described here actually were made available in version 16 build 2039 and later

For version 17, encrypted password support has been enhanced so that a database administrator can restrict database access to a user on a particular computer without revealing the actual plain text password to the user. It also prevents the current password from being decrypted to memory and consequently subject to inspection. Since successful decryption can now be restricted to a particular computer or computer/user combination, displaying the encrypted password in plain text is much less of an issue.

For example, in the following connection string, the encrypted passed (ENP=) cannot be used by anyone other than the specific computer/user combination for whom it was created.

dbping -d -c “Host=server-pc;Server=DemoServer;UID=DBA;ENP=05a17731bca92f97002100c39d906b70f3272fe76ad19c0e8bd452ad4f9ea9”

To better secure the EncryptedPassword connection parameter, the following changes were made:

1. Better encryption algorithms are used to ensure that the encrypted password cannot be easily decrypted.
2. EncryptedPassword can be restricted to a particular computer or a particular computer/user combination, such that
   1. the encrypted password can only be decrypted on that specific computer. It cannot be used on any other computer. However, anyone who can log on to the computer can still use the encrypted password and corresponding user ID to authenticate to a database.
   2. the encrypted password can only be decrypted on that specific computer for that specific user. It cannot be used on any other computer by the same or other user.
3. Plaintext passwords can no longer be reverse-engineered from the EncryptedPassword value using the ODBC Configuration for SQL Anywhere dialog. The Encrypt password option is no longer a checkbox but is now used to select from different encryption options including
   1. none,
   2. for use on any computer,
   3. for use on this computer only,
   4. for use on this computer and this user only.

The dialog can no longer be used to change the level of password encryption for an existing password, unless it was previously unencrypted. If the level of encryption is to be changed, then the password must be reentered.

DBDSN Changes

The Data Source utility (dbdsn) supports a new option -pet a|c|u, a specifying how the encrypted password may be used.

• If -pet a is specified, the password is encrypted for use on any computer.
• If -pet c is specified, the password is encrypted for use on this computer only.
• If -pet u is specified, the password is encrypted for use on this computer by this user only.  This option should not be used if your client application is running as a windows service.  If your client application runs as a service, use the -pet c option instead.
• The pre-existing -pe option which provides simple obfuscation continues to be supported; however, its use is deprecated.

Note that encryption for options -pet c and -pet u must be performed on the computer or computer/user for which it is intended to be used (decrypted).  You cannot export the DSN definitions to another machine and continue to use the EncryptedPassword

Note: The new encrypted password features are not supported by client libraries that are older than 17.0.0.1272 and 16.0.0.TBD.

DBFHide Changes

The File Hiding utility dbfhide tool can be used to encrypt an entire connection string to a file for use by most of the database tools that accept connection strings (e.g., dbping -d -c @credentials.hidden).  It now supports the new options -wm (computer-only) and -w (computer/user-only)