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.
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:
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 ‘***’:
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:
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.
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.
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:
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.
The Data Source utility (dbdsn) supports a new option -pet a|c|u, a specifying how the encrypted password may be used.
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.
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)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
21 | |
16 | |
13 | |
12 | |
11 | |
10 | |
9 | |
9 | |
8 | |
7 |