This article is intended to give an overview of how to set up usernames and passwords to control access to resources in Caché. It includes the following topics:
Caché Server Security
This section covers the following topics:
Securing the System
Before you start to secure Caché, secure the system. The major part of keeping the system secure is outside the realm of Caché. It lies in the physical security of the computers, the network, and the operating system. It assumes that the server is in a locked room behind a firewall, with all the usual operating system protections, such as passwords and privileges. If users must connect from outside the secure network, they must use private wide area networks (WANs) or virtual private networks (VPNs) over the Internet. If the data is to be secured against snooping on the network, the data should be encrypted on the network with a standard network encryption product.
Usernames and Passwords
There are three distinct types of username used by different parts of Caché:
The first two are controlled through the Caché Control Panel, the third through the Caché SQL Manager.
If an application only uses one method of access, the other types of username should be tightly controlled to effectively disable these access paths from anyone except the most trusted system developers and administrators.
Caché Direct Security
Setting up and enforcing security checks for Caché Direct involves the following:
Creating Caché Direct Usernames
Use the Caché Control Panel to add Caché Direct users:
  1. Expand the Security folder by clicking its plus sign (+). There are three sub folders for Windows systems and only two for OpenVMS and UNIX remote systems, one for each type of username.
  2. Click Caché Direct Users to list the defined usernames. For new systems, there are most likely no existing users; the right pane is empty.
  3. Right-click Caché Direct Users and choose New User from the shortcut menu.
  4. Complete the Add User dialog box and click OK:
    Adding a Caché Direct User
To change an existing username, double-click the username, edit the information in the Edit User dialog box, and click OK.
Note:
Take careful note of Username and Password; these fields are case sensitive for Caché Direct.
Enabling Caché Direct Security Checking
After creating usernames, you must enable security checking:
  1. Again, from the Control Panel, expand the Security folder.
  2. Right-click Caché Direct Users.
  3. To enable security checking, click Security Checking Enabled to place a check mark next to this item. Clicking the menu item toggles the check mark.
The password entered when creating the Caché Direct username controls access to the Control Panel as well as other Caché GUI tools. Before closing the Control Panel, verify that your password works:
  1. Start another Caché tool, for example Caché Explorer, by choosing it from the Caché Cube.
  2. Enter your username and password when prompted.
  3. If the username and password you just created do not work, return to the original Control Panel session and verify the values of these fields.
  4. If you cannot resolve the issue and you have to close the Control Panel, disable security checking temporarily so that you can later use the Control Panel. Contact the InterSystems Worldwide Response Center (WRC) for help in resolving this matter.
If security is enabled and you forget the password, an undocumented command can disable security and allow you access to reset passwords and re-enable security. Contact the WRC for this command.
UIC for User Processes
User processes run under a User Identification Code (UIC). This is used to determine certain permissions of a process.
On OpenVMS and UNIX this is taken from the UIC of the operating system process in which Caché is running. On Windows there is no concept of UIC and Caché introduces its own concept of a user account. A UIC is associated with each user account. These accounts are used when running Caché Terminal or telnet sessions. It is assumed that passwords for all OS and Caché user accounts are managed securely.
Controlling SQL Access
To control SQL access to Caché, first check that SQL security is enabled:
  1. Open the Caché Configuration Manager from the Caché Cube.
  2. From the Advanced tab, expand the SQL properties and locate two items near the middle of the list: SQL Security Enabled and ODBC Namespace Security Enabled.
  3. If either is set to “No”, change it and restart Caché.
When SQL Security Enabled = Yes, all Caché SQL security is active. Each user must specify a valid username and password when logging in through ODBC (or by setting the %msql variable directly). Privilege-based table/view security is active, and the user may only perform actions on a table or view to which they have been granted access.
When ODBC Namespace Security Enabled = Yes, users are only allowed to connect to a namespace through ODBC if they have been granted access to the namespace.
Access to SQL resources, such as tables and views, is controlled by SQL usernames and roles. A separate mechanism is required to adhere to the SQL standard. The concepts are similar to any relational database, although the details may vary. Access can be managed through the Caché SQL Manager or through SQL statements such as CREATE USER and GRANT.
To modify namespace-level privileges you must use the SQL GRANT command. You can alter administrative and object system-level privileges by using the SQL GRANT command or by using the Caché SQL Manager.
If you have enabled ODBC namespace security you must issue the explicit SQL GRANT command before accessing Caché with non-system users:
 GRANT ACCESS ON namespace TO username_or_rolename
Any code running at the Terminal/Telnet session level can access globals directly, and therefore security at the SQL level is not relevant. It is necessary to specify a username to perform many SQL operations, but password checking is not enforced. Security is enforced by setting UIC protection on databases and globals.
Using the Caché SQL Manager
You can perform many security-related tasks using the Caché SQL Manager. This utility is described in detail in Using Caché SQL Manager and in the Users, Roles, and Privileges chapter of Using Caché SQL. Brief descriptions of some tasks necessary to control SQL access follow:
Create a New Username
To create a new username using the Caché SQL Manager:
  1. Expand the Users folder. Initially there are two users: _PUBLIC and _SYSTEM.
  2. Right-click Users and choose New User from the shortcut menu.
  3. In the New User dialog box, enter the name, description, and password information and click OK. Ignore the Threshold fields.
The default SQL username, _SYSTEM, comes with administrator privileges. Its password, “SYS”, is common to all fresh installations and should be changed following the procedures described in the Change a Password section.
The default SQL username and password are not case sensitive.
Change a Password
To change a user's password, expand the Users folder and right-click the username. Choose Modify Definition from the shortcut menu. Enter a new password in the Password and Confirm Password boxes, and click OK.
If you change a password, you need to update DSNs that have the password included in their definition. Where a user is prompted for a password, no change is necessary.
Allow Access to Tables
A username does not have access to a table unless it is either the owner of the table (normally the username under which the table was created) or the username is explicitly given access. It is possible to directly give a username permission to access a table, but it is better to define a role. You can then associate individual users with this role.
Please read the Users, Roles, and Privileges chapter in Using Caché SQL for detailed information on creating roles and associating users with roles.
Once you have defined a role, you can grant it object privileges for a specific table using the Object tab in the Role Properties display of your newly created role. The Assign Object Privileges to Roles section in the “Roles and Privileges” chapter of Using Caché SQL Manager describes the details of this procedure.
Traditional Character Cell Connections
A terminal, telnet, or LAT connection runs under a user account. For OpenVMS and UNIX, the user must first log into the operating system. On Windows, Caché allows you to define user accounts that fill the same role.
Creating User Accounts for Windows
Create user accounts for Windows using the Caché Control Panel. Expand the Security folder and click User Accounts. Existing user accounts, including TRM:, are displayed in the right pane. Right click User Accounts and click New User from the shortcut menu to display the Add New User dialog box:
Creating a New User Account
Enter the name and password information. If UIC security is enabled, select a UIC for this user. Specify the namespace for the user process to use when it first starts. Entering information in the Routine box creates a captive account described in the following section.
Captive Accounts
To restrict what a user can do (and to make it easier for the user) a user can be placed directly into a particular Caché routine so they have no access to the Caché terminal prompt. On UNIX or OpenVMS accounts this is done by making a captive account at the OS level. For a Windows server, enter a value in the Routine field when creating a new user account.
Securing Local Terminal Access
The user account, TRM:, is used for terminal access from the local Caché console. You can add password protection. Unless there are concerns about the physical security and Windows level security of the Caché server, this is not usually done.
Double-click TRM: to open the Edit User dialog box. As well as specifying a password, you can select a different Namespace, but do not change any other values.
Changing Password for Local Terminal Users
Controlling Access to Globals
Access to databases and globals is controlled by the UIC of the user process. On UNIX and OpenVMS the UIC is taken from the OS level account. On Windows it is taken from the user account under which the user connected.
Each database can be given a UIC. This is done from the Caché Control Panel by right-clicking on the database name under the Local Databases folder and clicking Properties. Enter the UIC and click OK.
A process is determined to be in categories Owner, Group, World, or Network depending on the UICs of the process and of the database. Each global within the database can be given permissions: Read, Write, Delete, or None for each category of process. You can edit these global properties by right-clicking the global name and then choosing Properties from the shortcut menu.
By default, the system UIC (determined by the UIC of the CACHESYS database) is [0,0], which implies that no UIC checking is done before updating globals. Change this setting to enable checking. The system daemons must run under the system UIC. On Windows this seems to happen automatically, but something might have to be done on OpenVMS and UNIX.
As a by-product of enabling UIC security, users connected to Caché cannot switch to other namespaces.
ODBC/JDBC, Caché Direct, and CSP use server processes running under the system UIC. These connections are not affected by UIC-level protection.
Telnet Access
Telnet connections on UNIX and OpenVMS rely on normal telnet connection mechanisms; this must be controlled at the operating system level. If telnet access is not required, it should be disabled at the operating system level.
Caché for Windows accepts telnet connections directly. To prevent unauthorized use, it is possible to disable this:
  1. From the Caché Configuration Manager, click the Advanced tab.
  2. Expand the Startup properties.
  3. Expand Terminal and then Telnet.
  4. Ensure that the setting is Start Telnet = No.
If telnet is allowed, the user supplies a username and password and the process has the corresponding UIC.
Application Level Security
In addition to database security, an application developer is also responsible for maintaining security.
For example, at the most basic level a Web application that accesses the database is responsible for prompting the user for a username and password and using that to appropriately log the user into Caché.
More often, applications manage security in addition to database security, such as controlling which pages in a Web application a user is allowed to see, or rejecting access long before a user tries to access a table in the database.
One common function of applications is to record activity at a high level that is easily interpreted. While the database may record that a particular ODBC function was performed by a given user, the nature of a low level command is often obscure.
Recording Access to the System
Caché allows several mechanisms for recording information. All require specific action by the application designer or system administrator.
To record details of every user accessing your system from ODBC, InterSystems recommends implementing an SQL Login command to override the standard mechanism. A simple example can be provided by the WRC.
Callback functions in other parts of Caché can provide other audit trail facilities.
Supplying Usernames and Passwords
Caché Direct Users
To access an application using the object factory, the connection string can include a username and password. Visual Basic applications automatically prompt the user if no username is provided. Java applications are denied access if no username is provided. An application prompts the user for a username and password before connecting to Caché.
Note:
Prior to Caché 5.0, Visual Basic did not allow a username in the connection string; it always prompted the user.
SQL Users
ODBC applications can either provide a password in the definition of the DSN or in the ODBC connection string.
JDBC applications must provide the password in the connection string.
CSP applications can log the session into an SQL username using the $System.SQL.Login() method. The username is then held in the %session variable.