SQL/MX 3.2 Management Manual (H06.25+, J06.14+)

CREATE SCHEMA mycat.myschema;
This example creates a schema that is owned by the user sql.gdavis and located on the volume
ZSDA142A:
CREATE SCHEMA mycat.myschema AUTHORIZATION "sql.gdavis" LOCATION
ZSDA142A;
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 the “Access Privileges for SQL/MX Database Objects” (page 78) and the SQL/MX
Reference Manual.
This example creates a schema and uses the LOCATION clause to specify the schema subvolume:
CREATE SCHEMA mycat.myschema LOCATION ZSDXMJH2;
For more information, 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 user who creates
the schema is the owner of that schema. 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.
For more information on the ownership of schemas and their objects, see the SQL/MX Reference
Manual.
For more information about using GRANT and REVOKE on SQL/MP objects, see the SQL/MP
Installation and Management Guide. For more 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 more information about assigning security for SQL/MP objects, see
the SQL/MP Reference Manual and the SQL/MP Installation and Management Guide.
Granting and Revoking Access Privileges to SQL/MX Database Objects
Use the GRANT statement to grant access privileges to one or more users for an SQL/MX table
or view. You identify users with authorization IDs, which are valid Guardian user names enclosed
in double quotes. You grant privileges to perform specific statements (SELECT, DELETE, INSERT,
UPDATE), to create referential constraints (REFERENCES), or to perform all privileges that apply to
the object type. Use the WITH GRANT OPTION to specify that users to which privileges are granted
are authorized to grant the same privileges to other authorization IDs.
To grant a privilege on an SQL/MX table or view, you must have that privilege and also have the
right to grant that privilege. That is, the privilege must be issued to you through the WITH GRANT
OPTION and not have been revoked.
Granting privileges on a view does not grant that privilege to the corresponding column of the
underlying table.
For more information, see the SQL/MX Reference Manual.
78 Creating an SQL/MX Database