SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-8
Securing Catalog Tables
If you specify a security string in the CREATE CATALOG statement, you must specify
the catalog name. This example specifies the location of a new catalog and the
security:
>> CREATE CATALOG \SYS1.$VOL1.MFGCAT SECURE "GGNO";
--- SQL operation complete.
When you create a catalog or alter the security string for a catalog, the catalog security
applies to all the catalog tables. If you do not specify a security string in the CREATE
CATALOG statement, SQL/MP assigns your current default security to the catalog and
all the catalog tables.
Access to Catalog Objects
Allowing access to the catalog does not automatically allow access to the objects
described in that catalog. Access to the catalog is required in addition to access to the
objects for DDL statements, DML statement compilations for SQLCI and dynamic SQL,
most utility commands, and SQL program compilations.
Altering Security
To make the USAGES, TRANSIDS, and PROGRAMS tables accessible for SQL
compilations of programs, you might need to change the security of each table in an
ALTER TABLE statement. During explicit SQL compilation, any dependencies that a
program has on tables or views described in a catalog are recorded in the catalog’s
USAGES table. To insert the dependency record into the USAGES table, the catalog
manager must start a TMF transaction, which is registered in the TRANSIDS table.
Write access to the PROGRAMS table is required so that the SQL compiler can
register programs in the table.
You can change the catalog security at creation time by specifying the SECURE
attribute in the CREATE CATALOG statement. You can also change the security of
these individual tables at any later time by using the ALTER CATALOG statement:
•
CATALOGS (system catalog only)
•
USAGES
•
TRANSIDS
•
PROGRAMS
If you use the SECURE attribute, you must specify a security string that gives the
owner of the catalog tables read access.
For a user to compile a program, the user needs read and write access to the
USAGES and TRANSIDS tables in any catalog containing descriptions of tables,
views, collations, partitions, and indexes that the program uses in addition to write
access to the PROGRAMS table of the catalog in which the program is registered.