NonStop SQL Subsystem
NonStop SQL database is the HP NonStop server implementation of an ANSI-standard SQL DBMS supporting relational database management for applications, including ODBC applications.
Two Versions of NonStop SQL Database
NonStop SQL/MP Database
NonStop SQL/MP database is the HP NonStop server's implementation of the Structured Query Language (SQL). It is a distributed, relational database management system for on-line transaction processing and relational database management system (DBMS), based upon the industry ANSI SQL standard. It is ideally suited for database applications requiring easy access to the data, high performance, high availability, and the ability to scale up the database as the business grows. Conforming to industry-standard programming tools and protocols and accessible from popular PC software tools, NonStop SQL/MP database combines open access with the parallel, distributed processing environment made possible by the HP NonStop server's multiprocessor architecture. Parallel query capabilities and ease of management have made NonStop SQL/MP database the preferred database large business running on HP NonStop servers.
NonStop SQL/MX Database
HP's NonStop SQL/MX database is HP's next -generation relational database management system designed for business-critical applications. NonStop SQL/MX software brings the traditional NonStop SQL/MP fundamentals-high availability, scalability, reliability, and distributed database design and incorporated features that allowing creation of applications for open systems platforms.
NonStop SQL/MX database is a relational database management system that allows applications to use the NonStop SQL/MX query compiler and executor to access NonStop SQL/MP database objects. The NonStop SQL/MX query compiler and executor run in the HP NonStop Kernel Open System Services (OSS) environment. To allow ANSI-compliant applications to access NonStop SQL/MP database objects, the NonStop SQL/MX query compiler and executor provide basic logical name support.
For current versions of NonStop SQL/MX database, the query compiler and executor will operate on NonStop SQL/MP database objects only. NonStop SQL/MX database runs in the OSS environment, so OSS must be installed and running on the system. OSS is an open computing interface to the NonStop Kernel operating system.
The SQL subsystem components should be owned by SUPER.SUPER and Guardian secured "UUNU" or "OOAO", so that the components are accessible by local and remote users of SQL.
Two exceptions are $SYSTEM.SYSTEM.SQLMSG and $SYSTEM.SYSTEM. NLCPMSG which should be secured "NUNU" or "AOAO", granting READ access for reporting error messages.
| Caution | Safeguard software cannot secure individual SQL catalog or objects even though the object's name is a diskfile name. SQL tables and other objects can only be secured at the VOLUME or SUBVOLUME level. Safeguard can secure the SQL component files and processes, except for the System Catalog. See the Securing Application Chapter for more discussion on SQL objects |
NonStop SQL/MP Subsystem Components are:
SQLCAT
SQLCFE
SQLCI
SQLCI2
SQLCOMP
SQLESP
SQLEXPMG
SQLH
SQLMSG
SQLUTIL
NLCPCOMP
NLCPMSG
AUDSERV
SQLCAT
The SQL catalog manager process is the interface to reading and updating the SQL catalog structure. If remote access is required, the Catalog Manager communicates to a remote Catalog Manager process. The Catalog Manager is initiated by the SQL subsystem.
SQLCI / SQLCI2 / SQLUTIL
SQLCI and its components SQLCI2 and SQLUTIL are the conversational interface to NonStop SQL database. Commands are entered interactively or via macros to perform data access or to create, alter or purge descriptions. Access to SQLCI and its related components, must be closely controlled.
RISK Access to the conversational interface cannot be controlled, except at the base object Guardian access level. If a user has access to SQLCI and READ, WRITE access to a table, the user can effectively add, alter or delete one, many or all rows within the table. If a user has READ access to a table, all data within the table is accessible, regardless of its sensitivity.
AP-FILE-SQLCI-02 Application users should not have access to SQLCI. They should use the application's interfaces to insert, update and retrieve data from SQL databases.
SQL commands and access requirements are based on Guardian ownership, but have a revised concept of generalized ownership to objects. Userids that are considered to have generalized ownership of SQL objects are:
Local owner
Remote owner if granted PURGE access
Local SUPER.SUPER
Owner's local group manager
SQLCI Commands with Security Implications
This section describes only the SQLCI commands that pose security risks.
The three categories of SQL statements should be appropriately secured.
Data Definition Language (DDL) statements, issued by the database administrator.
Utility commands used to perform database maintenance and general utility functions, as needed.
Data Manipulation Language (DML) & Data Control Language (DCL) statements, generally issued by application users.
Security issues closely follow the use of three categories of SQL statements. These categories and the most frequent users of each category are as follows :
| Access Requirements for SQL Objects | ||
|---|---|---|
| Type | Command | Authority Required |
| Compile | SQLCOMP | READ and PURGE authority for the program file. READ and WRITE authority for the PROGRAMS, USAGES, and TRANSIDS table of the catalog in which the program will be registered. READ and WRITE authority for the USAGES and TRANSIDS catalog tables of any catalog that contains a description of a table or view that the program uses. |
| RUN program file | EXECUTE authority for the program file For dynamic recompilation, READ authority for any catalog with a description of a table or view used by the program | |
Commands "suitable" for Application Users (usually performed programmatically)
| Access Requirements for SQL Objects | ||
|---|---|---|
| Type | Command | Authority Required |
| DCL Statements | FREE RESOURCES | READ authority for affected objects. |
| App users | LOCK TABLE | READ authority for the table or view and all underlying tables of the view. |
| DML Statements | DELETE | READ and WRITE authority for the table or protection view being deleted or modified and, |
| App users | UPDATE | READ authority for tables, protection views, and underlying tables of shorthand views specified in subqueries of the statement. |
| SELECT | READ authority for tables, protection views, and underlying tables of shorthand views specified in the statement. | |
Commands "suitable" for Database Administration Group:
| Access Requirements for SQL Objects | ||
|---|---|---|
| Type | Command | Authority Required |
| DDL Statements DB admin | DDL commands in general | READ and WRITE authority for affected catalogs unless otherwise noted. |
| ALTER | Must be the:
| |
| To resecure program | READ and WRITE authority for the affected catalog and for the program file. | |
| To resecure catalog | Must be the:
| |
| COMMENT | Must be the:
| |
| CREATE CATALOG | WRITE authority for the SQL.CATALOGS table on the system that contains the catalog. | |
| CREATE COLLATION | READ and WRITE authority for the catalog in which the collation will be registered and,READ authority for the collation source file. | |
| CREATE CONSTRAINT | Must be the:
| |
| CREATE INDEX | Must be the:
| |
| CREATE TABLE | READ and WRITE authority for all affected catalogs. | |
| CREATE VIEW Shorthand | WRITE authority for the USAGES and TRANSIDS tables in catalogs that describe the underlying tables and views and WRITE authority for the VIEWS catalog table. | |
| CREATE VIEW Protection | Must be the:
| |
| DROP CONSTRAINT DROP INDEX | Must be the:
| |
| DROP PROGRAM | Purge authority for the object being dropped. | |
| UPDATE STATISTICS | Must be the:
| |
Commands "suitable" for Database Administration Group:
| Access Requirements for SQL Objects | ||
|---|---|---|
| Type | Command | Authority Required |
| Utility Commands | CLEANUP | Must be the:
|
| CONVERT | READ authority for the file to be converted and the DDL dictionary and the same authority as for: | |
| COPY | READ authority for the source file or object and WRITE authority for the target file or object and for objects, READ authority for the catalogs containing the object descriptions. | |
| DISPLAY USE OF | READ authority for the catalogs containing the object descriptions. | |
| DUP | READ authority for objects and files being duplicated; read authority for the catalogs containing the object descriptions and The same authority as for CREATE statements for the types of objects being duplicated and PURGE authority for target files and objects if purging is necessary. | |
| EDIT | READ and WRITE authority for the file to be edited. | |
| FILEINFO | READ authority for each object or file for which statistics are to be displayed. | |
| INVOKE | READ authority for the catalogs containing the object descriptions. | |
| LOAD | READ authority for the source file or object; WRITE authority for the target file or object and for objects, READ authority for the catalogs containing the object descriptions. If the target file is a table, then LOAD requires the authority to WRITE to the catalog in which the table is described. | |
| MODIFY [DICTIONARY] | Must be the:
For a MODIFY LABEL CHECKONLY request, READ authority for the SQL objects and object programs For a MODIFY CATALOG CHECKONLY request, READ authority for the catalogs. | |
| PURGE | Must be the:
| |
| PURGEDATA | WRITE authority for the files and for the tables and affected catalogs. | |
| SECURE | Must be the:
| |
| TEDIT | READ and WRITE authority for the file to be edited. | |
| UPGRADE CATALOG | Must be the:
| |
| UPGRADE SYSTEM CATALOG | Must be the:
| |
| VERIFY | READ authority for the catalogs containing the object descriptions. | |
Controlling SQLCI
SQLCI provides the interface to not only inquire into SQL objects, but to perform management functions. There is no internal security within SQLCI, except the security ownership requirements listed above to control commands within SQLCI for which a user has Guardian access.
RISK Without a third party Access Control Product, there is no way to control a user's use of unauthorized commands within SQLCI.
With a third party access control product:
3P-ACCESS-SQL-01 Use a third party access control product to allow the users responsible for using sensitive commands the ability to run SQLCI commands as SUPER.SUPER.
3P-ACCESS-SQL-02 Use a third party access control product to give the use of certain SQLCI commands to a limited group of users only.
Licensed SQLCI2
Normally, the SQL catalogs are managed by the SQL subsystem, meaning all entries into the SQL catalogs are performed via the subsystem, rather than by the user. However, SQLCI 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.
AP-FILE-SQLCI2-01 Inadvertent use of a licensed SQLCI2 can cause corruption of the SQL catalog structure. SQLCI2 should not be licensed on the SYSTEM.SYSTEM location. It should only be licensed, used and unlicensed if necessary by the SUPER.SUPER user.
AP-FILE-SQLCI2-02 Never LICENSE and PROGID SQLCI2 on the system. Use of this file could corrupt the catalogs.
SQLCOMP / NLCPCOMP
NonStop SQL/MP and SQL/MX compilers to verify and register SQL programs.
A compiler prepares SQL statements to be executed by a host language program and registers the program to the SQL catalog. Only programs processed by SQLCOMP can access SQL objects.
SQLCOMP is invoked explicitly or implicitly by SQL. Unlike other compilers, SQLCOMP is normally available on secure systems, as it is an integral piece of the SQL subsystem. Automatic ( implicit ) recompilation is invoked whenever an object is invalidated. (see SQL object invalidation .) Explicit SQL compilation is used to initially register SQL application programs in the SQL catalogs.
AP-FILE-SQLCOMP-01 SQLCOMP should be available for execution by the SQL subsystem on any system to perform SQL recompilations.
SQLCFE / SQLH
SQL components of the C compiler and C library extensions. These files enable SQL for C language programs. Other languages have libraries available for SQL extensions.
SQLESP / SQLESMG
Program and message file supporting native languages and SQL.
SQLMSG / NLCPMSG
SQL's message files for NonStop SQL/MP and SQL/MX databases. These files should be generally available.
SQLUTIL
SQLUTIL is initiated by SQLCI to perform certain SQL commands that are designated as utility operations, such as DUP, COPY, etc.
AUDSERV
AUDSERV is only used in an SQL environment. Please refer to the Gazette section on AUDSERV.
Securing SQL Components
BP-FILE-SQL-01 SQLCAT should be secured "UUNU".
BP-OPSYS-LICENSE-02 SQLCAT must be LICENSED.
BP-OPSYS-OWNER-02 SQLCAT should be owned by SUPER.SUPER.
BP-OPSYS-FILELOC-02 SQLCAT must reside in $SYSTEM.SYSTEM
BP-FILE-SQL-02 SQLCFE should be secured "UUNU".
BP-OPSYS-OWNER-02 SQLCFE should be owned by SUPER.SUPER.
BP-OPSYS-FILELOC-02 SQLCFE must reside in $SYSTEM.SYSTEM
BP-FILE-SQL-03 SQLCI should be secured "UUNU".
BP-OPSYS-OWNER-02 SQLCI should be owned by SUPER.SUPER.
BP-OPSYS-FILELOC-02 SQLCI must reside in $SYSTEM.SYSTEM
BP-FILE-SQL-04 SQLCI2 should be secured "UUNU".
BP-OPSYS-LICENSE-02 SQLCI2 should NOT be LICENSED.
BP-OPSYS-OWNER-02 SQLCI2 should be owned by SUPER.SUPER.
BP-OPSYS-FILELOC-02 SQLCI2 must reside in $SYSTEM.SYSTEM
BP-FILE-SQL-05 SQLCOMP should be secured "UUNU".
BP-OPSYS-LICENSE-02 SQLCOMP must be LICENSED.
BP-OPSYS-OWNER-02 SQLCOMP should be owned by SUPER.SUPER.
BP-OPSYS-FILELOC-02 SQLCOMP must reside in $SYSTEM.SYSTEM
BP-FILE-SQL-06 SQLESP should be secured "UUNU".
BP-OPSYS-OWNER-02 SQLESP should be owned by SUPER.SUPER.
BP-OPSYS-FILELOC-02 SQLESP must reside in $SYSTEM.SYSTEM
BP-FILE-SQL-07 SQLESPMG should be secured "UUNU".
BP-OPSYS-OWNER-02 SQLESPMG should be owned by SUPER.SUPER.
BP-OPSYS-FILELOC-02 SQLESPMG must reside in $SYSTEM.SYSTEM
BP-FILE-SQL-08 SQLH should be secured "NUNU".
BP-OPSYS-OWNER-02 SQLH should be owned by SUPER.SUPER.
BP-OPSYS-FILELOC-02 SQLH must reside in $SYSTEM.SYSTEM
BP-FILE-SQL-09 SQLMSG should be secured "NUNU".
BP-OPSYS-OWNER-02 SQLMSG should be owned by SUPER.SUPER.
BP-OPSYS-FILELOC-02 SQLMSG must reside in $SYSTEM.SYSTEM
BP-FILE-SQL-10 SQLUTIL should be secured "UUNU".
BP-OPSYS-LICENSE-02 SQLUTIL must be LICENSED.
BP-OPSYS-OWNER-02 SQLUTIL should be owned by SUPER.SUPER.
BP-OPSYS-FILELOC-02 SQLUTIL must reside in $SYSTEM.SYSTEM
BP-FILE-SQL-11 NLCPCOMP should be secured "UUNU".
BP-OPSYS-OWNER-02 NLCPCOMP should be owned by SUPER.SUPER.
BP-OPSYS-FILELOC-02 NLCPCOMP must reside in $SYSTEM.SYSTEM
BP-FILE-SQL-12 NLCPMSG should be secured "NUNU".
BP-OPSYS-OWNER-02 NLCPMSG should be owned by SUPER.SUPER.
BP-OPSYS-FILELOC-02 NLCPMSG must reside in $SYSTEM.SYSTEM
BP-FILE-SQL-13 The System Catalog should be secured "NUCU".
BP-OPSYS-OWNER-03 The System Catalog should be owned by SUPER.SUPER.
BP-OPSYS-FILELOC-03 The System Catalog resides in $<vol>.<svol>
If available, use Safeguard software or a third party object security product to grant access to SQL components object files to necessary personnel, and deny access to all other users.
BP-SAFE-SQL-01 Add a Safeguard Protection Record to grant appropriate access to the SQLCI object file.
BP-SAFE-SQL-02 to 03 Add a Safeguard Protection Record to grant appropriate access to the SQLCOMP/NLCPCOMP object files.
BP-SAFE-SQL-04 Add a Safeguard Protection Record to grant appropriate access to the SQL system catalog.
| Discovery Questions | Look here: | |
|---|---|---|
| FILE-POLICY | Is NonStop SQL/MP or SQL/MX database used for applications? | Policy |
| FILE-POLICY | Is SQL installed and active on the system for application or subsystem purposes? | Policy |
| FILE-POLICY | Where is the System Catalog? | SQLCI |
| OPSYS-OWNER-02 | Who owns the SQLCAT object file? | Fileinfo |
| OPSYS-OWNER-02 | Who owns the SQLCFE object file? | Fileinfo |
| OPSYS-OWNER-02 | Who owns the SQLCI object file? | Fileinfo |
| OPSYS-OWNER-02 | Who owns the SQLCI2 object file? | Fileinfo |
| OPSYS-OWNER-02 | Who owns the SQLCOMP object file? | Fileinfo |
| OPSYS-OWNER-02 | Who owns the SQLESP object file? | Fileinfo |
| OPSYS-OWNER-02 | Who owns the SQLESPMG object file? | Fileinfo |
| OPSYS-OWNER-02 | Who owns the SQLH object file? | Fileinfo |
| OPSYS-OWNER-02 | Who owns the SQLMSG object file? | Fileinfo |
| OPSYS-OWNER-02 | Who owns the SQLUTIL object file? | Fileinfo |
| OPSYS-OWNER-02 | Who owns the NLCPCOMP object file? | Fileinfo |
| OPSYS-OWNER-02 | Who owns the NLCPMSG object file? | Fileinfo |
| FILE-POLICY | Where is the SQL System Catalog | SQLCI |
| OPSYS-OWNER-03 | Who owns the SQL catalog? | Fileinfo |
| OPSYS-LICENSE-02 | Is the SQLCAT object file licensed? | Fileinfo |
| OPSYS-LICENSE-02 | Is the SQLCI2 object file licensed? | Fileinfo |
| OPSYS-LICENSE-02 | Is the SQLCOMP object file licensed? | Fileinfo |
| OPSYS-LICENSE-02 | Is the SQLUTIL object file licensed? | Fileinfo |
| FILE-POLICY | Who is responsible for managing the SQL system catalog and SQL environment? | Policy |
| FILE-POLICY | Who is allowed to create and manage SQL application catalogs ? | Policy |
| FILE-SQL-01 | Is the SQLCAT object file secured correctly? | Fileinfo |
| FILE-SQL-02 | Is the SQLCFE object file secured correctly? | Fileinfo |
| FILE-SQL-03 | Is the SQLCI object file correctly secured with the Guardian or Safeguard system? | Fileinfo Safecom |
| FILE-SQL-04 | Is the SQLCI2 object file secured correctly? | Fileinfo |
| FILE-SQL-05 | Is the SQLCOMP object file correctly secured with the Guardian or Safeguard system? | Fileinfo Safecom |
| FILE-SQL-06 | Is the SQLESP object file secured correctly? | Fileinfo |
| FILE-SQL-07 | Is the SQLESPMG object file secured correctly? | Fileinfo |
| FILE-SQL-08 | Is the SQLH file secured correctly? | Fileinfo |
| FILE-SQL-09 | Is the SQLMSG file secured correctly? | Fileinfo |
| FILE-SQL-10 | Is the SQLUTIL object file secured correctly? | Fileinfo |
| FILE-SQL-11 | Is the NLCPCOMP object file correctly secured with the Guardian or Safeguard system? | Fileinfo Safecom |
| FILE-SQL-12 | Is the NLCPMSG file secured correctly? | Fileinfo |
| FILE-SQL-13 | Is the SQL system catalog correctly secured with the Guardian or Safeguard system? | Fileinfo Safecom |
