SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Creating an SQL/MX Database
HP NonStop SQL/MX Installation and Management Guide—523723-004
7-9
Access Privileges for SQL/MX Database Objects
This example creates a schema that is owned by the user gdavis and located on the
volume $DATA08, and can only be performed by a super ID user:
CREATE SCHEMA mycat.myschema AUTHORIZATION “gdavis” LOCATION
$DATA08;
The authorization ID must be the current authorization ID unless it is a super ID user. A
super ID user can specify any currently valid authorization ID as the owner of the
schema. For more information, see Access Privileges for SQL/MX Database Objects
on page 7-9 and the SQL/MX Reference Manual.
This example creates a schema on the local node and uses the LOCATION clause to
specify the location of the metadata tables for the catalog:
CREATE SCHEMA mycat.myschema LOCATION $DATA08.ZSDADATA;
For more information about the CREATE SCHEMA statement, see the SQL/MX
Reference Manual.
Access Privileges for SQL/MX Database
Objects
In NonStop SQL/MX, Guardian and Safeguard security are not applicable. Security for
SQL/MX tables and views is implemented by the ANSI standard schema ownership
rules and by using the GRANT statement to grant access privileges to specified users.
The schema in which a table or view is located is the unit of object ownership. The
person who creates the schema is the owner of that schema and all objects associated
with it. NonStop SQL/MX grants these privileges to the person with the authorization ID
of the schema owner:
•
Create and drop tables, views, and other objects in the schema.
•
Perform SELECT, DELETE, INSERT, and UPDATE operations on tables and views
in the schema and create constraints (REFERENCES) on tables in the schema.
To create, alter, or drop a table or view within a given schema, you must either be the
super ID user or own the schema. To create a constraint on a table that refers to a
column in another table, you must have the REFERENCES privilege on that column in
the other table.
For information about using GRANT and REVOKE on SQL/MX objects, see the
SQL/MP Installation and Management Guide. For information about using the GRANT,
GRANT EXECUTE, REVOKE, and REVOKE EXECUTE statements to implement and
change security on SPJs, see the SQL/MX Guide to Stored Procedures in Java.
You cannot assign SQL/MX privileges to SQL/MP objects, and GRANT and REVOKE
do not support SQL/MP objects. For information about assigning security for SQL/MP
objects, see the SQL/MX Reference Manual and the SQL/MP Installation and
Management Guide.