Elements of distributed relational database security

A distributed relational database administrator needs to protect the resources of the servers in the network without unnecessarily restricting access to data by clients in the network.

A client secures its objects and relational database to ensure only authorized users have access to distributed relational database programs. This is done using normal IBM i object authorization to identify users and to specify what each user (or group of users) is allowed to do with an object. Alternatively, authority to tables, views, and SQL packages can be granted or revoked using the SQL GRANT and REVOKE statements. Providing levels of authority to SQL objects on the client helps ensure that only authorized users have access to an SQL application that accesses data on another system.

The level of system security in effect on the server determines whether a request from a client is accepted and whether the remote user is authorized to objects on the server.

Here are some aspects of security planning for the IBM i environment in a distributed relational database network:

  • Object-related security to control user access to particular resources such as confidential tables, programs, and packages
  • Location security that verifies the identity of other systems in the network
  • User-related security to verify the identity and rights of users on the local system and remote systems
  • Physical security such as locked doors or secured buildings that surround the systems, modems, communication lines and terminals that can be configured in the line description and used in the route selection process

Location, user-related, and object-related security are only possible if the system security level is set at level 20 or above.

For Advanced Program-to-Program Communication (APPC) conversations, when the system is using level 10 security, the IBM i operating system connects to the network as a nonsecure system. The system does not validate the identity of a remote system during session establishment and does not require conversation security on incoming program start requests. For level 10, security information configured for the APPC remote location is ignored and is not used during session or conversation establishment. If a user profile does not exist on the system, one is created.

When the system is using security level 20 or above, the IBM i operating system connects to the network as a secure system. The system can then provide conversation-level security functions and, in the case of APPC, session-level security as well.

Having system security set at the same level across the systems in your network makes the task of security administration easier. A server controls whether the session and conversation can be established by specifying what is expected from the client to establish a session. For example, if the security level on the client is set at 10 and the security level on the server is above 10, the appropriate information might not be sent and the session might not be established without changing security elements on one of the systems.

Passwords for DRDA access

The most common method of authorizing a remote user for database access is by flowing a user ID and password at connection time.

With embedded SQL one method an application programmer can use is to code the USER/USING clause on the SQL CONNECT statement. An embedded SQL example:

EXEC SQL CONNECT TO :rdbname USER :userid USING :pwd

With dynamic interfaces typically these are passed in on the connect API. A CLI example:

SQLConnect(rdbname, userid, pwd)

For Distributed Relational Database Architecture™ (DRDA) access to remote relational databases, once a conversation is established, you do not need to enter a password again. If you end a connection with either a RELEASE, DISCONNECT, or CONNECT statement when running with the remote unit of work (RUW) connection management method, your conversation with the first server might or might not be dropped, depending on the kind of server you are connected to and your client job attributes (for the specific rules, see Controlling DDM conversations). If the conversation to the first server is not dropped, it remains unused while you are connected to the second server. If you connect again to the first server and the conversation is unused, the conversation becomes active again without you needing to enter your user ID and password. On this second use of the conversation, your password is also not validated again.

If the local connection attempts to start a three-part naming connection, it will use the user ID and password passed in on the local connection to start the new remote connections. There are some environments, such as host server, which are not able to retrieve the password from the local connection. Therefore server authentication entries should be used for propagating passwords.