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

For more information, see the SQL/MX Reference Manual. For more information about application
programs, see “Managing Database Applications” (page 209).
Authorization Requirements for Dropping Database Objects
Table 13 (page 167) describes the authorization requirement for each drop operation.
Table 13 Authorization Requirements for Dropping Database Objects
Authorization RequirementsOperation
Any user can drop any empty catalog visible on the local node. (All metadata tables
for the catalog must be accessible at the time DROP CATALOG executes.) No user
“Dropping Catalogs” (page 167)
can drop a non empty catalog, however, even if the catalog contains only empty
schemas.
To drop a column with a sequence of SQL/MX statements, you must own its schema
or be the super ID.
“Dropping Columns from an
SQL/MX Table” (page 168)
To drop a constraint with the ALTER TABLE DROP CONSTRAINT statement, you must
own its schema or be the super ID. You must also have access to all partitions of the
“Dropping Constraints”
(page 168)
table itself. (ALTER TABLE works only on user-created tables. You cannot use it to
modify a metadata table even if you are the owner of the metadata tables or a super
ID.)
To drop an index with the DROP INDEX statement, either you must be the super ID,
or you must own the schema that contains the index, have ALL privileges on the table,
and have access to all partitions of the index and the underlying table.
“Dropping SQL/MX Indexes”
(page 169)
To drop a partition with the MODIFY utility, you must be the owner of its schema or
the super ID.
nl
“Dropping Partitions for
SQL/MX Tables and Indexes”
(page 170)
To drop only the data in a partition with the PURGEDATA utility, either you must be
the super ID, or you must own the schema and have ALL privileges on the table
(DELETE, INSERT, SELECT, and UPDATE).
To drop a schema with the DROP SCHEMA statement, you must own the schema or
be the super ID. You must also have remote passwords for both the node where the
“Dropping Schemas” (page 171)
catalog of the schema has been registered, and for any nodes to which the schema
has been replicated.
To drop an SQL/MP alias with the DROP SQLMP ALIAS statement, you must be the
owner of the schema in which the alias resides or be the super ID.
“Dropping SQL/MP Aliases”
(page 171)
To drop an SPJ with the DROP PROCEDURE statement, you must own the SPJ or be
the super ID. For more information, see the SQL/MX Guide to Stored Procedures in
Java.
“Dropping SPJs” (page 172)
To drop a table with the DROP TABLE statement, you must own its schema or be the
super ID.
“Dropping SQL/MX Tables”
(page 172)
To drop a trigger with the DROP TRIGGER statement, you must own its schema or be
the super ID.
“Dropping Triggers” (page 174)
To drop a view with the DROP VIEW statement, you must own the schema that contains
the view or be the super ID.
“Dropping Views” (page 174)
Dropping Catalogs
To drop a catalog, use the DROP CATALOG statement.
DROP CATALOG automatically removes the SQL/MX user metadata tables, system metadata
tables, and definition schema tables associated with the catalog and all entries in the system
catalog associated with the catalog. All metadata tables for the catalog must be accessible at the
time DROP CATALOG executes.
Before you can drop a catalog, it must be empty. Any user can drop any empty catalog visible on
the local node. No user can drop a nonempty catalog, even if the catalog contains only empty
schemas.
Dropping Objects From an SQL/MX Database 167