Chapter 7: Securing Applications
Understanding Application Development
Generally, applications and system tools that run on the HP NonStop server come from the following sources:
In-House application development
Third-party vendor applications
Legacy applications
In-House system tools
Third-party system tools
Regardless of the source, great care must be taken to properly secure an application, its database and its configuration files. First, we will discuss securing applications and later we will deal with system tools.
Applications are the company's business application(s). They are the reason for using the HP NonStop server. In essence, applications are the most important entity on the machine. Everything else that runs on the system supports the application.
In House Application Development
In-house application development is the most mysterious environment of an enterprise; mysterious to everyone other than developers and their managers. To most people, developers are geeks that wear cutoffs, sandals and beanies with propellers. You can hear them talking about the Source, Schemas, Buffers, Parsers and State Machines. How can any normal human communicate with such strange people?
Other than the strange language they speak, developers are no different than the rest of us. The exception is how they perceive computers. Computers are their universe and they take pride in inventing ways to make those electronic devices hum. To developers, there is nothing like a machine obeying their every command. To that extent they strive to learn every computer language on the planet.
Developers DO NOT like to be bogged down with computer security. They would be happiest if they all had access to the SUPER.SUPER password and could do anything they wanted.
But, the fact is that developers have to comply with the Corporate Security Policy and Standards.
The Security Administrator must establish a dialog with the development manager early on in the development cycle to ensure that the following areas comply with the security policy:
Source Control
Application Configuration Source Files
Object Release Control
Application Security Considerations
Source Control
Source Control is the management of a file's change history and the file's relationship to a larger grouping of related files known as a project. Source control is a fundamental part of a well-organized application software development organization.
Source Control is the generic term for tools that manage files (containing program source code) in a multi-developer environment. Most source control tools work something like a traditional public library, maintaining a central repository of files, programs, documents, configuration files or any other related file in a location accessible to all developers. In addition, source control tools include the ability to track the changes that developers make to source files, control simultaneous changes and revert to earlier versions if necessary.
Developers must only have access to submit and extract program source files from the Source Control Foundation.
RISK If access to the source code is not restricted via a source control product, it is possible that multiple developers could copy a file, each make changes and replace the file, which leaves the validity of the source code questionable.
AP-ADVICE-APPLICATION-01 The source control product and its database must be owned by the responsible job function ID and secured from unauthorized tampering.
Currently HP does not offer a source control product for the HP NonStop server, however there are several third party vendors that do.
3P-SOURCE-ADVICE-01 Use a third party source control product to secure the source code, configuration files, documentation and obey files that the application uses in the production environment.
Application Configuration Source Files
Application configuration source files must be treated the same as the application program source files. Developers must only have access to submit and extract configuration files from the Source Control facility.
Object Release Control
The Object Release Control facility must be owned by the responsible job function ID. Normally the object release utility is part of the source control product.
RISK At no time should a developer be allowed to submit objects into the release control database.
3P-SOURCE-ADVICE-03 Use a third party source control product to assist in performing and managing change control and versioning.
AP-ADVICE-APPLICATION-02 The Corporate Security Policy and Standards should mandate a periodic review of the system for each application:
The application manager should indicate which programs are being changed and why.
The security department should develop methods to verify that change control procedures are being used properly.
Procedures should be in place for a 'backout' to a prior version if a change causes problems for the application.
Procedures should be in place for verifying the application's security policy for files belonging to the application.
The Corporate Security Policy and Standards should determine the appropriate Job Function ID that will be responsible for performing the following tasks :
Moving the new object code into production.
Giving the files to the designated Application Owner ID.
Securing the files in accordance with the policy.
Application Security Considerations
Security must be considered at all levels of the application; its design, database, maintenance, communications, recovery, etc. This is not an easy task because the application utilizes many facets of the system hardware and system software subsystems.
The security department should work with the application managers to create a mechanism for performing application security reviews.
The Corporate Security Policy and Standards should mandate a periodic review to ensure that application security is in compliance with the policy. Procedures should be put in place to ensure that the reviews take place.
Third-Party Vendor Applications
Third-party vendor applications introduced to HP NonStop servers have the same issues as in-house applications. The difference is that the source code belongs to the third-party vendor.
The following sections described above apply to third-party vendor applications:
Object Release Control
Application Security Considerations
Legacy Applications
Legacy applications are those that are still in use, though many years have passed and the hardware and operating system have changed repeatedly. These applications are kept running in the hope that some day they will be replaced by new state of the art applications being developed in-house or purchased from a third-party vendor.
Legacy applications have a tendency to have a life of their own. Often, employees that were responsible for keeping them running have moved on to other jobs or to opportunities at other companies. Current developers are afraid to attempt updating the code for fear of breaking something else. It might even be the case that the source code for the application no longer exists.
The Security Administrator must inventory the legacy applications; paying close attention to the source code, configuration files, obey files, TACL macros and job streams used to execute the application; to be certain that the application's security conforms with the Corporate Security Policy and Standards.
The following sections described above apply to legacy applications:
Source Control
Application Configuration Source Files
Object Release Control
Application Security Considerations
In-House System Tools
In-house system tools can be obey files, TACL macros, or object programs used to manage databases, update SQL catalogs, partition physical files, add users, change passwords, etc. These tools use many of the HP NonStop server subsystems such as FUP, SCF, BACKUP and SQLCI.
Often the developers of these tools are no longer supporting them because they have moved on and usually there is no documentation available to understand what the tool was designed to accomplish.
The Security Administrator, with the knowledge that these tools are only present to support the application, must evaluate system tools. The Security Administrator must understand the exact function these tools perform in order to determine the appropriate security.
The following sections described above apply to in-house system tools:
Source Control
Application Configuration Source Files
Object Release Control
Application Security Considerations
Third-Party System Tools
The third-party system tools, like the in-house system tools, exist to support the application. The Security Administrator must evaluate each tool to determine which subsystems are being utilized and the appropriate security for the tool according to the security policy.
Usually, third-party tools come well documented and have easy to maintain common user interfaces.
The following sections described above apply to third-party system tools:
Object Release Control
Application Security Considerations
Securing the Production Applications
The production environment applications are the most important entities on the HP NonStop server. If any breach or stoppage occurs to any application, the enterprise will suffer a loss of revenue. Simply put, if the applications aren't running, no revenue is being generated. The entire enterprise suffers when this occurs.
At no time should improper security be the cause of any stoppage or breach. Strict adherence to the Corporate Security Policy and Standards must be enforced without exception. If there are exceptions to the security policy, they must be documented and approved by higher management.
If the Security Administrator is involved in the initial design of the in-house applications or the evaluation of third-party applications there will be fewer surprises at deployment time.
Determining the following will help the Security Administrator to analyze the production applications to see if they comply with the Corporate Security Policy:
| Discovery Issues: | Look Here: |
|---|---|
| Locate where the object files reside. | Fileinfo |
| Locate where the database files reside. | Fileinfo SQLCI |
| Determine who owns the object files. | Fileinfo |
| Determine who owns the database files. | Fileinfo SQLCI |
| Determine who can execute, add, modify and purge the object files. | Fileinfo Safecom |
| Determine who can read, write, create and purge the database files. | Fileinfo SQLCI Safecom |
| Determine which HP utilities are accessed by the application. | Code Review |
| Review the PORTCONF file to see what object files are being started by the LISTNER process, and how they are secured. | FUP |
| If any object files are logging on users, make sure some type of encryption is being used to transmit the passwords across the network. | Code Review |
| Review all Pathway and non-Pathway configuration files for ASSIGNs, DEFINEs and PARAMs; the object files and database files must match the physical location. | PATHCOM Edit |
| Review the Batch facility by checking what jobs are being started to operate on the production environment. | Batchcom |
| Review the security of application obey files or TACL macros. | Edit |
| Review application obey files or TACL macros for embedded passwords. | Edit |
| Locate the TMF audit trails. Since they contain the before and after images of data from the enterprise database, these files must be owned by the System Administrator. | TMFCOM |
Security must be involved in the design to ensure that the production application complies with the Corporate Security Policy and Standards. The following areas must be considered when new applications are designed or existing applications are reviewed.
Platforms
Pathway
Non-Pathway
Batch
Tools
DBMS
SQL Database
Enscribe Database
Communications
System Management
Platforms
Following are application platform descriptions to help the Security Administrator analyze a production environment to determine if the implementation complies with the Corporate Security Policy:
Pathway
Non-Pathway
Batch
Tools
The Pathway Subsystem
Pathway is an application development platform with a client server management system that performs much of the application run-time management.
Pathway is the platform for many HP NonStop server applications. It is often the pivotal production platform, therefore requiring a wide range of access throughout a company's enterprise.
Pathway is a client-server application model. The Pathway monitor provides the interface for the communication layer and the management layer between the client and server. A Pathway application has two major components :
Requestors A screen program or GUI client component that interacts directly with the terminal. The screen part of the application is written in SCOBOLX or in a GUI language.
Servers The user program running on the host system that interacts with the databases and performs user calculations and database manipulation, etc. The server part of the application can be written in any language supported by the HP NonStop server.
Terminal Interface
There are three basic ways to run a Pathway environment:
Dedicated Terminals: If a terminal is dedicated to Pathway TACL and terminal logons are not allowed. The terminal is always connected to the Pathway application. The terminals are setup via PATHCOM TERM commands.
Non-dedicated Terminals: The user runs Pathway by starting PATHCOM from a TACL prompt and issuing a RUN PROGRAM command to start the application. Non-dedicated terminals are not configured in Pathway.
PC GUI/Web: A connection is made to an HP NonStop server's TCP/IP interface on a particular port. Communications code must be listening to the port and send the GUI application messages to LINKMON. LINKMON provides the interface that sends and receives messages between the Pathway client and server programs.
RISK The applications should require logon and logoff mechanisms to ensure that the user at the terminal has a valid logon and is allowed to perform the requested function. The application should also have a timeout facility to pre-vent someone walking away from a terminal and leaving it open for someone else to walk up to and use.
Dedicated Terminals
RISK Dedicated terminals are always ON. Applications with dedicated terminals must have a user authentication method to control access, otherwise the application is at risk.
Non-Dedicated Terminals
RISK Non-dedicated terminals require access to a TACL and to PATHCOM. If Pathway security allows, the user can make changes using the PATHCOM interface.
RISK If a non-dedicated terminal is left logged on, the application is at risk unless the application times out an inactive session.
RISK If a non-dedicated terminal is left logged on, the application is at risk unless the terminal is timed out via either CMON or TACL configuration.
GUI Terminal Interface
RISK Accessing a Pathway application from a GUI does not enforce any authentication, unless software enforces it. A software mechanism is needed to ensure authorization of users using a GUI interface.
Servers
A Pathway server performs all calculations, disk I/O and communications outside the Pathway environment. Server programs can be written in a variety of application languages to perform many tasks. Servers can perform database I/O to any Enscribe, SQL, OSS or other type of data file on the system. They can also perform communication to devices and other systems.
Servers are started and stopped by the Pathway subsystem as needed. Configuration settings determine how many servers can be started.
The server processes run as the Pathway server owner, regardless of the user who is logged into the terminal or the owner of the Guardian file. It is the Pathway owner's userid that is used to determine the accessibility of system resources such as database objects, etc., unless the program object file is PROGID'd.
AP-ADVICE-APPLICATION-03 Do not PROGID Pathway servers, especially if the server object is owned by a SUPER Group member.
RISK Pathway does not interact with CMON when starting server processes for priority or CPU settings. CPU selection and priority is set for servers within Pathway.
RISK The server Owner and Security attributes are ignored by requests from a TCP. They are only valid when LINKMON is the requestor , as discussed separately.
RISK Pathways running with network security can allow remote access to the application, which should only be allowed when necessary.
RISK Because Pathway starts servers as the defined userid without requiring user authentication, a poorly secured Pathway interface will permit the malicious addition, removal or substitution of servers.
Configuring Pathways
With Pathway, developers can define the client-server environments using a configuration file. The configuration file contains statements that define the location of the object programs and the location of the database.
Before any Pathway application can be deployed, the Security Administrator should review the security related statements in the configuration file. The Pathway configuration file must be owned by the job function ID responsible for starting, modifying and stopping the Pathway application.
AP-ADVICE-APPLICATION-04 Pathway configuration files must be secured so that the job function ID can read the files but not change them.
AP-ADVICE-APPLICATION-05 Change control staff must be able to manage the Pathway configuration files.
Specifically, the Security Administrator must pay attention to the following statements in the Pathway configuration file:
Set Pathway Owner <Owner-Id>
The Owner-Id is the userid allowed to issue PATHCOM commands that directly alter the state of the PATHMON-controlled objects. The userid must be known to the system in which the Pathway is running. Use this attribute in conjunction with the SECURITY attribute. The value of ownerid is specified using the following syntax:
[\ node. \ system-number. \* .] group-name . user-name Set Pathway Security <security-attribute>
The Security-Attribute controls which users can issue PATHCOM commands that directly alter the state of the PATHMON-controlled objects. Before the START PATHWAY command is issued, the owner ID is the process-accessor ID of the
PATHMON process and the security attribute is "O". This security setting controls who can alter the Pathway configuration attributes. The security attribute is the same as the Guardian security attribute. The possible values are:
"A" ”All local users
"N" ”All network users
"G" ”Only local members of the owner's group
"C" ”Only network members of the owner's group
"O" ”Only the local owner.
"U" ”Only the remote or local owner
" - " ”Only local SUPER.SUPER
RISK If the owner of a Pathway system is SUPER.SUPER, allowing the security to default to "N" results in a security risk for the system. In this situation, set the security to "O" or "-", so that only persons logged on as local SUPER.SUPER can add, delete, or modify PATHMON-controlled objects.
Set Server Owner <Owner-Id>
The Owner-Id specifies the userid that is used to control access from a Pathsend process to a server class. (The TCPs ignore this server attribute.) The userid must be known to the system in which PATHCOM is running. Use this attribute in conjunction with the SERVER SECURITY attribute.
Set Server Security < security-attribute>
The Security-Attribute controls which users, in relation to the OWNER attribute, can access a server class from a Pathsend requester. (TCPs ignore this attribute.) The security attribute is the same as the Guardian file security attribute.
Communication Between Pathway Environments
Pathway systems can share Pathway server classes, meaning that a client process on Pathway A can send a request to a server within Pathway B.
The userid of the Pathway process controlling the server class has to have corresponding userids and remote passwords with the following systems:
The system where the Pathway process is running
The system where the server class is running
The system where the requesting process is running
The same operations take place whether the Pathway environments are running on the same or on different nodes. If the communication is remote, then the security requirements to allow remote access are:
The Pathway and program security must allow network access.
If the server is on the same system as the database, the databases do not need to be secured for network access.
Securing a Pathway Application
A Pathway application might be the primary mechanism to access the company's sensitive databases. Information is retrieved, added, updated and purged via the application programs.
To enforce separation of duties , database files should be created, configured and owned by the Application Database Owner. The Pathway application executing ID will retrieve, add and update information contained in the database, therefore the Pathway executing ID must have READ and WRITE access to the database.
AP-ADVICE-APPLICATION-06A If the Database owner and the Path - way/Application Executing ID are in the same administrative group, database files should be secured "GGOO" or, if remote access is appropriate, "CCOO". No userids assigned to actual individuals should be created in the administrative group.
 |
Pathway/Application Executing ID = 200,255; BASE24.MGR Database Owner = 200,1; BASE24.DBASE
| |
In Example 1, there should be no individual userids in group 200.
AP-ADVICE-APPLICATION-06B If the Pathway/Application Executing ID is not in the same group as the Database Owner, then it is strongly recommended that Safeguard software be used to grant granular READ and WRITE access to the Path- way/Application Executing ID.
| |
Pathway/Application Executing ID = 200,255; BASE24.MGR Database Owner = 100,254; DATABASE.OWNER
| |
In Example 2, there should be no individual userids in group 200. If the Policy allows, individuals may be given individual userids in the DATABASE administrative group.
AP-SAFE-PATHWAY-02 If Safeguard software is installed, Protection Records should be created to control access to data files for READ only for inquiry, and READ and WRITE for file updates. Access can be granted to a single user if desired.
3P-OBJSEC-ADVICE-01 Safeguard Protection Records do not apply to SQL tables, only to the subvolume or volume that contain the tables. To adequately secure SQL databases, a third party object security module might be required.
The following discovery questions apply to each Pathway environment.
| Discovery Questions | Look Here: |
|---|---|
| Who is allowed to start, stop and modify this Pathway? | Policy |
| Does the organization have a change control policy? | Policy |
| Who owns the Pathway environment? | Pathway info |
| Who owns the POBJCOD and POBJDIR files? | Fileinfo |
| Who owns the PATHCTL file? | Fileinfo |
| What is the internal Pathway security vector? | Pathway info |
| Is the internal owner the same as the running owner? | Pathway info Status |
| Is the Pathway owner explicitly set? | Pathway conf file |
| Is there a Safeguard Protection Record to control access to the Pathway application? | Safecom |
| Who are the Program entity owners ? | Pathway info |
| Are the Program entities secured correctly? | Pathway info |
| Who are the server entity owners? | Pathway info |
| Are the server entities secured correctly? | Pathway info |
| What is the server object files security? | Fileinfo |
| Are any servers PROGID'd? | Fileinfo |
| Are any servers LICENSED? | Fileinfo |
| Who owns the Pathway configuration files? | Fileinfo |
| Are the Pathway configuration files secured correctly? | Fileinfo |
| Are remote clients used for this Pathway? | Policy |
| What method is used for remote client access? | Policy |
| What method of user authorization is used for the incoming request? Is it documented? | Policy |
| Who owns the database files? | Fileinfo |
| Are the database files secured correctly? | Fileinfo |
| Is there a Safeguard Protection Record to control access to the database subvolumes ? | Safeguard |
Binder
The Binder utility must be secured to only those users allowed to create, modify or delete program objects on the production environment. It is a simple task for any user that has gained unauthorized access to an HP NonStop server to use the Binder utility to alter any program object. The altered object is undetectable by Pathway, Safeguard or Guardian security. See Gazette section on BINDER for more information.
AP-ADVICE-APPLICATION-07 Ideally, only system support staff, application support staff and change control staff should be allowed to use BIND on production systems. The person running BIND needs:
READ access to the object file
CREATE and WRITE access to the target file's subvolume
READ access to any libraries that will be bound into the target object.
AP-ADVICE-APPLICATION-08 BIND can be tightly secured if the development system is separate from the production system. Applications can be bound on the development system and moved to the production system. In this scenario, BIND need not be present on the production system.
RISK To protect proprietary code from the risk of theft from reverse engineering, the DUMP and LMAP commands should also be restricted.
Non-Pathway Applications
While Pathway is the preferred platform to manage enterprise applications, HP Non- Stop servers allow for Requestor-Server programs to execute independent of Pathway. Requestor programs will accept input from terminals, intelligent devices or other processes running on the server or networked server. That input is formulated into data messages then sent to a server program for processing. The server programs perform I/O operations to any database, device or file on the server or network.
The disadvantage of a non-Pathway application is that they are more difficult to code and manage. The programs are not fault tolerant unless special code is added to make them so. Non-Pathway applications perform many of the same features pro- grammatically that are performed by the Pathway monitor.
Securing a non-Pathway application depends on how well the Security administrator understands the purpose and basic functions of the application. The Security Administrator must work with the Application Owner to understand where the object programs and database reside in order to secure those locations. The Security Administrator must develop an application checklist to analyze if the non-Pathway applications comply with the corporate Security Policy.
Batch
The HP NonStop server has a Batch facility capable of scheduling jobs to start at predetermined times. The Batch facility (NetBatch) initiates programs or TACL macros. Generally these programs are functions that are written to perform tasks on the system at predetermined times or under predetermined conditions.
Unlike the applications discussed above, batch jobs do not require human intervention to be started.
RISK Unauthorized jobs in the batch facility can get started without obvious detection.
Batch Jobs are sometimes used to reorganize large data files during off-hours so as not to impact the production environment during critical hours. Depending on the enterprise, these Batch Jobs will perform special functions to support the main enterprise applications.
AP-ADVICE-APPLICATION-09 The Corporate Security Policy and Standards should mandate a periodic review of the jobs in the Batch facility and make a determination of their use.
AP-ADVICE-APPLICATION-10 Batch jobs should not have embedded passwords.
Tools
Tools come in many different forms and can be developed in-house or obtained from a third party vendor.
Obey files for a utility, such as FUP or SQLCI
TACL macros
Compiled TACL macros
Compiled programs
Pathway Applications
Non-Pathway Applications
Tools should only exist to support the enterprise application and should not interfere with the production environment. These tools are sometimes overlooked by the Security Administrator because they are less easily identified.
AP-ADVICE-APPLICATION-11 All tools must be documented and secured according to the Corporate Security Policy.
Database Management Systems (DBMS)
The following are DBMS subsystem descriptions to help the Security Administrator analyze a production environment to determine if the implementation complies with the Corporate Security Policy:
Enscribe software
NonStop SQL software
Enscribe Databases
An Enscribe database is defined with the Data Definition Language (DDL) facility. DDL enables users to define fields in Enscribe files and translate those object definitions into source code for HP NonStop server languages, such as COBOL, C, TAL and ENFORM.
The Enscribe record manager is integrated into the Guardian file system and provides high level access to records in a database. Many legacy applications running on an HP NonStop server utilize the Enscribe record manager system. Enscribe database security can be managed by the Safeguard Security system and/or Guardian security.
Securing Enscribe Databases With the Guardian System
Enscribe database files can be secured with the Guardian security system. Files will have a designated owner, specified by the groupid and userid. A security vector specifies whether users have READ, WRITE, EXECUTE and PURGE access. The vector controls how other users can access the file. The Guardian system is the fundamental security method to secure any file on the HP NonStop server.
RISK If Safeguard software is the main security method and not all files are properly secured, the security check defaults to the Guardian system.
AP-ADVICE-APPLICATION-12 Regardless of whether or not the application files are secured with Safeguard, the Guardian security vectors should always be set to provide basic security for the application.
RISK If backup tapes are not physically secured, application data is not secure.
RISK NonStop TMF audit trails contain sensitive data images. NonStop TMF audit trails must be secured at the same level as the data it protects.
Securing Enscribe Databases With Safeguard
Safeguard software can further secure Enscribe objects at the volume, subvolume and individual diskfile level. With the Safeguard software users can be granted access to objects via Protection Records. The Protection Records can allow or deny READ, WRITE, EXECUTE, CREATE, OWNER and PURGE access to a file for all users or specified users. Sometimes Protection Records can contain allow and deny access to specific users on the same object. Please refer to Securing Diskfiles with Safeguard Subsystem in Part Five for more information.
AP-ADVICE-APPLICATION-13 If Safeguard software is available, sensitive files should have Protection Records to restrict unauthorized access.
RISK If Safeguard software is the main security method and not all files are properly secured, the security check defaults to the Guardian environment.
AP-ADVICE-APPLICATION-14 Regardless of whether or not the application files are secured with Safeguard software, the Guardian security vectors should always be set to provide basic security for the application.
NonStop SQL Databases
Structured Query Language (SQL) is a language used for creating, modifying and accessing relational databases. NonStop SQL is a powerful relational database manager fully integrated with the Guardian operating system and taking full advantage of the distributed processing made possible by the HP NonStop server hardware architecture.
NonStop SQL is thoroughly integrated into the HP NonStop server architecture. It is called an active data dictionary because the NonStop SQL Optimizer queries the application catalogs for statistics for the best access path to the required data at the time the request is made. The Optimizer also updates the access path statistics in the application catalogs for future use.
Catalogs are sets of SQL tables that contain descriptions of the SQL relational objects. Catalog tables can only be modified within SQL, not with FUP. Each Catalog contains:
Table names and descriptions
The column names, data types and sizes in each table
Descriptions of files containing the tables
The names of all the programs that use the tables
Access path statistics
ODBC support tables
There can be one or more catalogs per volume, but only one per subvolume and each catalog must have the same name as the subvolume where it resides. For example, the catalog on $DATA23.APP1 will be named $DATA23.APP1. Each catalog can describe objects in its subvolume or in any other subvolume on any disk.
SQL database objects are created via the SQL Conversational Interface (SQLCI) or programmatically using embedded SQL statements or the Dynamic SQL API facility. Once the database is created, users can access the data from tables or views via the same three methods.
RISK Access to the SQLCI utility provides access to all SQLCI commands, both informational and destructive.
RISK Depending on how the relational database was designed and the amount of data being queried, SQLCI can start sort processes that may slow down critical production cycles on the HP NonStop server.
Securing SQL Databases With the Guardian System
NonStop SQL objects such as Catalogs, Tables, Indexes and Views are secured using SQLCI DDL create and alter statements. The ownerid and security vectors are held in the SQL catalog. These can only be altered using SQLCI or programmatically with embedded SQL statements. FUP cannot be used to change the security of any SQL object.
RISK Change to file security made with SQLCI or programmatic interfaces are immediately applied to the Guardian security vector and may cause a disruption of service if incorrect.
RISK Safeguard software cannot secure individual SQL catalogs or objects even though the object's name is a disk file name. SQL tables and other objects can only be secured at the VOLUME or SUBVOLUME level with Safeguard.
Securing SQL Databases With the Safeguard Subsystem
Safeguard has limited ability to secure NonStop SQL catalogs and databases.
For SQL catalogs and objects, Safeguard can:
Restrict access to volumes and subvolumes containing SQL tables, views, indexes, collations, and SQL programs stored in Guardian files
Protect an entire catalog by protecting the subvolume that contains the catalog.
| Note | Every table that requires different access rules must reside in a different subvolume. |
For a partitioned table or index, each volume containing a partition of the object should have an identical Protection Record.
Restrict the creation of tables, views, indexes and catalogs on volumes and sub- volumes for which it maintains a Protection Record.
Restrict the creation of SQL processes and the execution and purging of SQL program files.
| Caution | Safeguard software cannot protect individual SQL object names, even though names of SQL tables, views, indexes, collations, and programs are disk file names. |
Securing Catalogs
The catalog tables compose the data dictionary, a vital part of an application's integrity. The security of a catalog should protect the data dictionary information from unauthorized removal or alteration.
In order to compile a program, users need READ and WRITE access to the USAGES and TRANSIDS tables in any catalog containing descriptions of tables, views, collations, partitions, and indexes the program uses as well as write access to the PROGRAMS table of the catalog in which the program is registered.
RISK When a catalog's security string is altered, the catalog security applies to all of the catalog tables.
Securing the System Catalog
Each node where SQL is used must have at least one catalog, the System Catalog. The System Catalog contains information about all the catalogs on the system. The system catalog is like any other catalog, with the following exceptions:
The system catalog is created during the installation of NonStop SQL database.
The system catalog contains the SQL CATALOGS table in which all catalogs on the node are registered. The system catalog can be used as a catalog directory only or as a general-purpose catalog.
The system catalog must be protected against removal. The PURGE authority must be granted only to the Database Administrator.
RISK Users must have READ and WRITE access to the System Catalog to create or delete application catalogs.
RISK Users who need to query the CATALOGS table must have READ access.
AP-ADVICE-APPLICATION-15 The system catalog should not be used as an application catalog. Doing so minimizes the ability to protect the system catalog from corruption or removal.
SQL Objects
NonStop SQL database uses defined SQL Objects to relate to the information stored in the catalog.
The physical SQL objects are:
Tables
Indexes
Views
Programs
Tables
A base table is a physical table, stored in a file. It is made up of rows and columns . Each row is a data record. Each column contains a particular field of data. Data is entered into the table a row at a time. Each row (record) contains a value for each column or null value.
Securing a Base Table
Base tables are the foundation of the database, and base table security ultimately defines much of the security for views, indexes, and DML statements.
The following users have the authority to perform DDL operations on existing tables as the equivalent of the owner:
local owner of an object
remote owner with purge authority
local SUPER.SUPER
local owners group manager
RISK Anyone with authority to purge a table can drop the table.
RISK When the security string of a base table is altered, SQL will alter the security of the dependent indexes. The security of dependent protection views might also be altered if the new security of the table violates the system-enforced relationship between these objects, as explained later in "Securing Protection Views."
Indexes
An index is an alternate key to a table. An alternate key is an alternate way to access the data in the table. A NonStop SQL index is stored in a key- sequenced file and includes columns for the primary key and the alternate key.
Securing an INDEX
Indexes must be secured to match its base table to ensure accessibility.
RISK Purging a base table will automatically purge associated indexes.
SQL Views
Controlled access to base tables can be granted using NonStop SQL views. A view is a logical table derived from one or more base tables. A view can include a projection of columns and a selection of rows from the table or tables. Projected columns and selected rows for a view can come directly from the base tables or indirectly through other views. Views do not store data physically on the disk, but have a disk file label.
There are two types of views:
Protection Views “ users can view and enter data
Shorthand Views “ users can only view data
Securing Protection Views
A Protection View is a logical view of a table, providing security restrictions so that only certain information can be presented to a user by masking rows and columns of the single underlying table from displays or updates. Protection views are updateable.
Protection views are most commonly used to secure columns or rows of sensitive data from the user. For instance, assume that an employee file contains the name, address, phone number and salary of each employee. A Protection view can eliminate the 'SALARY' column from the view so that the risk of accidental display is minimized.
Protection views can be used to insert, update or delete records.
Securing Shorthand Views
A Shorthand View is a pre- formed query that accesses data to complete the query. Data may be selected from one or more tables or other views and presented to the user as a logical single table. Shorthand views are not updateable.
Shorthand views assist in securing the data by:
The owner of a shorthand view does need not be the owner of any underlying tables or views. Tables and views underlying a shorthand view can have different owners.
Anyone with authority to READ all the underlying tables also has authority to READ a shorthand view, but only authorized users can PURGE a shorthand view.
SQL Programs
SQL programs are like any other language program except that they must be SQLCOMPiled to be registered in an SQL catalog and optimized for access.
SQLCOMP / NLCPCOMP
The SQLCOMP or (NLCPCOMP for native mode languages) SQL compilers optimize the SQL statements in the object code file and link the statement to a specific access path determination, then register the program to the SQL catalog. Only programs processed by SQLCOMP can access SQL objects.
Unlike other compilers, SQLCOMP is normally available on secure systems, as an integral piece of the SQL subsystem.
SQLCOMP is invoked explicitly or implicitly (automatic) by SQL.
Explicit Compilation: is used to register SQL application programs to the SQL catalogs. It is demand driven.
Automatic ( implicit ) Recompilation: is invoked whenever an object is invalidated (see SQL invalidation ). When an invalidating event occurs it invalidates the stored optimized access path. While a program is executing, if it discovers an invalid path, the SQL subsystem initiates SQLCOMP for the statement to obtain a new path for access.
AP-FILE-SQLCOMP-01 SQLCOMP should be available for execution by the SQL subsystem on any system to perform SQL recompilations.
AP-FILE-SQLCOMP-02 Automatic SQLCOMPs are valid for the active session of the object's run unit. Once the program has stopped, if it restarts the recompilations will need to be performed again. Only an explicit SQLCOMP can re-store the new paths. This has performance implications, as recompiling invalid statements incurs overhead.
SQL Invalidation
A number of SQL and system events can cause invalidation.
Non-DDL events:
Copying the program file. If you copy a program file by using the FUP DUPLICATE command, the original file is unaffected, but the new file is invalid.
Binding the program file. If you explicitly bind a program file by using the Binder program, the original file is unaffected, but the resulting target file is invalid.
Restoring a program file. If you restore a program file (or an underlying table of a protection or shorthand view used by the program) by using the RESTORE program without specifying the SQLCOMPILE ON option, the restored program is invalid. If the SQLCOMPILE ON option is used, an explicit SQL recompile is performed.
Running the Accelerator for the program file. If you run the accelerator to optimize the object code (TNS/R systems only), the program file becomes invalid.
DDL events:
Adding a constraint to a table used by the program.
Adding a column to a table used by the program.
Adding an index to a table used by the program, or to an underlying table of a protection or shorthand view used by the program, unless you specify the NO INVALIDATE option in the CREATE INDEX statement.
Adding a partition to a table or index used by the program.
Changing a collation: dropping and then re-creating the collation, renaming a collation, or changing a DEFINE that points to a collation.
Executing the UPDATE STATISTICS statement unless you specify the NORECOMPILE option.
Dropping a table or view.
Dropping a partition of a table or index.
Dropping an index or constraint on a table.
Restoring a table, including an underlying table of a protection or shorthand view, using the RESTORE program.
Changing the PARTITION ARRAY type associated with the base table.
| Note | For commands that have options to circumvent the invalidation, the changes may not be utilized by the program until the recompilation is performed. For instance, if a new index is added with the NO INVALIDATE option, the index will not be used by the program until after the subsequent explicit recompilation. Similarly, if the program is compiled with the CHECK INOPERABLE PLANS option and the table and any associated protection views have the similarity check enabled, the invalidation can be circumvented. |
Unexpected events:
A node or volume that contains one or more objects involved in the SQL statement is inaccessible.
An event occurs, such as a CPU failure during SQLCOMP or SQL access.
Licensed SQLCI2
Normally, the SQL catalogs are managed by the SQL subsystem, meaning all entries into the SQL catalogs are performed via the subsystem as the commands to add, alter or delete objects is requested. For instance, if the user adds an index, part of the functionality to add the index is to add the appropriate catalog entries. The SQL subsystem makes the appropriate changes to the catalogs, keeping them properly updated.
Under normal conditions a user does not perform direct manipulation to the catalogs. However, SQLCI2 includes a diagnostic tool that allows SUPER.SUPER to perform direct data manipulation on the SQL Catalogs. This tool has been provided for disaster recovery of corrupted catalogs. To facilitate the diagnostic tool, the SQLCI2 object file must be licensed and used by SUPER.SUPER. Using SQLCI2 in this manner makes direct changes to the SQL catalog without affecting the corresponding SQL object.
RISK Inadvertent use of a licensed SQLCI2 can cause corruption of the SQL catalog structure.
AP-ADVICE-SQLCI2-01 Never leave SQLCI2 licensed on your $SYSTEM.SYSTEM subvolume. If SUPER.SUPER uses this version it will automatically be in the diagnostic mode and could cause corruption.
