
APAR= II09333
CA400WIN-CA400OPT-ODBC SECURITY ISSUES WITH CLIENT ACCESS/400
AND ODBC
                                          LAST UPDATE: 5/30/97
OVERVIEW
----------
The following information is not intended to be a comprehensive
guide to security issues on the AS/400 or with Client Access.
It's intent is to give an overview of security issues that
impact Client Access and ODBC users.

ODBC does not introduce any new security exposures.  The same
security issues have existed since Version 1 of OS/400.  What
ODBC did introduce was a plethora of desktop applications
that offer easy access to data on the AS/400 via a few mouse
clicks.

CONTENTS
---------
Security Concepts ... Overview of basic AS/400 security concepts
Client Access ........Security issues specific to Client Acces
ODBC .................Security issues specific to ODBC
Security Tools .......OS/400 Toolkit ordering information
Misc .................References for further assistance

OVERVIEW
--------

- OBJECT LEVEL SECURITY
  Object level security allows you to define who can use an
  object and how that object can be used. Object level security
  cannot be 'circumvented'. OS/400 checks authority every time
  the object is accessed. Here's an example of the object
  security on a file.

  Object secured by authorization list  . . . . . . . ORDENTRY

                            Object    ----------Object----------
   User        Group      Authority  Opr  Mgt  Exist  Alter  Ref
   ADMIN1                  *ALL        X    X     X      X     X
   *PUBLIC                 *USE        X

                Object    ---------------Data---------------
   User        Authority  Read  Add  Update  Delete  Execute
   ADMIN1      *ALL        X     X     X       X        X
   *PUBLIC     *USE        X                            X
   In this example an authorization list named ORDENTRY is used
   to ease administration.  User profile ADMIN1 has full access
   to the file, while *PUBLIC can read but not update the file.
   In this example, user profiles running the order entry
   application would be entered into the ORDENTRY authorization
   list with *CHANGE authority.  For further information
   reference the manual:  AS/400 Security - Reference.

- PROGRAM ADOPTED AUTHORITY
  The system administrator may want to limit the 'methods' by
  which a user profile can access data. In the example above,
  they might decide that the order entry user profile should
  have NO access to the files except when running the
  order entry application. OS/400 supports this concept using
  'program adopted authority'.  With the program adopted
  authority, the user profile running the application program
  'adopts' an authorization level attached to the program
  itself.  This technique was commonly used before R310 to
  ensure the integrity of data.

  Using the order entry example, it might be changed such that
  the order entry PROGRAM has *CHANGE authority to the files but
  the order entry user profiles have *NONE access to the files.
  The order entry user profiles have *USE authority to the
  PROGRAM.  When running the program the order entry profiles
  'adopt' the authority of the program, thereby gaining access
  to the file.  For further information reference the manual:
  AS/400 Security - Reference.

- REFERENTIAL INTEGRITY
  In R310, DB2/400 introduced data base support for referential
  integrity.  Prior to R310, many customers used 'program
  adopted authority to protect the integrity of their data base.
  Using our order entry example, we might want to ensure that
  no order detail entry could be made w/o matching an existing
  order header.  Using program adopted authority, every program
  that updates the order entry files must contain this check.
  Referential Integrity allows the database 'rules' to be coded
  directly in the database.  We no longer have to restrict file
  updates to certain programs to ensure integrity.
  For further information reference the manuals: AS/400 DB2/400
  Database Reference; Database 2/400 Advanced Database Functions
  (redbook).

- OTHER
  Some system administrators attempt to secure access to the
  data rather that the data itself. This is extremely risky as
  they have to know ALL the methods that data can be accessed
  by. Common techniques are:

  - Limit Capabilities - User Profile Parameter
    Useful for 'green screen' or 5250 emulation based
    applications, this method assumes that if you prevent users
    from typing in commands on a 5250 screen, they can only
    access data via the programs and menus that the system
    administrator provides them. THIS METHOD IS ONLY SECURE
    IF ALL 'BACKDOORS' ARE ACCOUNTED FOR. 'Backdoors' include
    TCPIP, APPC Comminications, Client Access and a multitude
    of OEM products. For further information reference manual:
    AS/400 Security - Reference.

- User Exit Programs
    A user exit program allows the system administrator to
    secure an IBM supplied host server program.  System
    administrators often overlook the DDM user exit which is
    documented in OS/400 Communications.  The DDM server is
    used by some Client Access functions such as the older
    Remote Command as well as many APPC programs.  See
    "CLIENT ACCESS - HOST SERVERS" below for an explanation
    of what a host server is.  For detailed information
    reference the manuals: OS/400 Server Concepts and
    Administration;   OS/400 Distributed Data Management.

  - AUDIT LOGS (monitoring security)
    OS/400 has several logs that can be used to monitor
    security.  QHST, the history log, contains messages that
    relate to security changes made to the system.  For
    detailed monitoring  of security related functions,
    QAUDJRN can be enabled.  Using the *SECURITY value the
    following functions are logged:
    o  Changes to object authority
    o  Create, change, delete, display, and restore
       operations of user profiles
    o  Changes to object ownership
    o  Changes to programs (CHGPGM) that will now adopt the
       owners profile
    o  Changes to system values and network attributes
    o  Changes to subsystem routing
    o  When the QSECOFR password is reset to the shipped
       value by DST
    o  When the DST security officer password is requested
       to be defaulted
    o  Changes to the auditing attribute of an object
    For further information reference the manual: AS/400
    Security - Reference.

- JOURNALS
  Journaling is often used with Client Server applications
  to provide commitment control.  The journals will contain
  detailed information on every update made to a file being
  journaled.  The journal information can be formatted and
  queried to sift for specific information such as what user
  profiles updated the file, what records were updated, type
  of update, etc.  OS/400 also allows user defined journal
  entries.  Used with a user exit program or trigger, this
  offers a relatively low overhead method of maintaining user
  defined audits. For further information reference the manuals:
  OS/400 Backup and Recovery - Advanced;  OS/400 Security -
  Advanced.

CLIENT ACCESS CONCEPTS
 ---------------------
- HOST SERVER
  Each client application on a PC is a program that            s
  communicates with a corresponding server on the as400.  This
  server processes a request and returns data. OS/400 has
  several servers included in the base operating system.
  These are documented in the manual SC41-3740 OS/400 Server
  Concepts and Administration.  Examples of host server
  programs are: File Server - used by shared folders;
  Database Server - used by ODBC; Data Queue Server- used
  by the data queue api's and windows network driver.
  A system administrator must watch for changes or additions
  to these servers.

  Any communications program can act as a server.  The system e
  administrator must also be aware of ANY OEM server or user
  written server that may reside on their system.  Any of
  these can be used to access as/400 data.

- SERVER PROGRAM SECURITY
  IBM server programs authenticate the user id and password sent
  by the client on the program 'allocate' or connect. By default
  this is the userid and password of the client access 'router'
  but many servers, including ODBC,
  allow you to override this and pass a different
  userid and password.  After verifying the security information
  the server program uses the authority of this userid when
  attempting to access data.  For further information reference
  the manual: APPC Communications Programming.

- USER EXIT PROGRAMS
  Each server program has an associated user exit.  A user
  exit  program allows an administrator to control what
  activities a client is allowed to do for each of the specific
  servers.    Whenever a request is sent to the server, the
  server first checks if a user exit program is registered.  If
  so, it will pass to the user exit a block of data with the
  request information.  The user exit program can process this
  data and return a flag allowing or preventing the server
  from processing the request.
³ User exit programs can have a significant impact on
³ performance.

- COMMON BACKDOORS
  Several servers offer methods to submit AS/400 commands via
  the client. Restricting command line usage does not block
  this. Remote Command uses either the DDM server or the Remote
  Command and Distributed Program Call Server depending on the
  client. Both remote sql and ODBC support a method of
  executing AS/400 commands and programs by a call to QCMDEXC.

ODBC
----
- Restricting Program access to the database
  System administrators often need to limit access to particular
  files to a certain program or set of programs.  A 'Green
  Screen' programmer would implement this using program adopted
  authority. A similar method can be used with ODBC.

  Stored procedures allow ODBC programmers to implement program
  adopted authority.  The programmer may not want users to be
  able to manipulate database files using desktop applications
  such as Microsoft Access ot Lotus 123. Instead, the programmer
  may want to limit database updates to only their application.
  To implement this, user access to the database must be
  restricted with object level security or user exit programs.
  The application must be written to send data requests to the
  stored procedure and have the stored procedure update the
  database.
  Another advantage to this approach is that it 'hides' the
  details of your code from others.  Anyone can use ODBC spy
  to monitor sql calls and reverse engineer an application
  or database.  Stored procedures hide many of the details
  from a 'spy'.

  For further information on stored procedures reference the
  manuals:  DB2/400 Advanced Database Topics (redbook); Client
  Access ODBC User's Guide; AS/400 Client/Server Performance
  using the Windows 3.1 Client (redbook).

- Restricting CPU utilization by user
  ODBC has greatly eased accessing AS/400 data.  One negative
  impact has been that users may accidentally create very CPU
  intensive queries without realizing it.  ODBC runs at an
  interactive job priority and this can severely impact
  system performance.  The AS/400 supports a 'Query Governor'.
  The Query Governor can be invoked via ODBC either willingly
  (i.e. by the PC application) in a stored procedure call
³ or (R312 or later of Client Access) by the ODBC apis via
³ the query timeout parameter.  It can also be
³ 'forced' on the ODBC job via a user exit program.
  The time limit is specified on the QRYTIMLMT parameter of
  the CHGQRYA CL command.
  For further information reference the manuals:
    DB2/400 Advanced Database Topics (redbook);
    AS/400 Client/Server Performance using the Windows 3.1
    Client (redbook);
    OS/400 Server Concepts and Administration.


*** Please see II09334 for the continuation of this text! ***
