Schema Level Privileges

Schema level privileges allow the schema owner to grant or revoke privileges on an entire schema to a user or a group of users.

The following statements are updated to support schema level changes:
NOTE:

The sections on GRANT and REVOKE statements later in this document include the complete syntax, semantics, and considerations for these statements.

CREATE SCHEMA changes

During the creation of the schema, the schema owner is given all DDL privileges and all DML privileges on the schema WITH GRANT OPTION. This enables the schema owner to grant those privileges to other users.

In the multi-tenant environment, the CREATE SCHEMA operation automatically grants relevant privileges to the privilege groups that are associated with the schema to represent the access levels (read, read-write, and full access including DDL).

GIVE SCHEMA changes

The GIVE SCHEMA operation is used to transfer the schema ownership from one user to another user. Privileges on the schema held by the owner with grantee type 'O' will be transferred to the new owner, this transfer will include DDL privileges.

Types of privileges

Schema level privileges support both SQL/MX Data Definition Language (DDL) and Data Manipulation Language (DML) privileges.

  • DDL

    The schema level DDL privileges allow the grantees to perform DDL operations on that schema, and on existing and future objects in that schema. DDL privileges are applicable only at the schema level. You cannot grant or revoke DDL privileges on individual objects.

  • DML

    The schema Level DML privileges allow the types of privileges that can be granted on individual objects to be granted on an entire schema; such privileges will pertain to existing and future objects in that schema. For example, if the SELECT privilege on a schema is granted to a user, that user will be able to select from objects in that schema. The schema owner holds no implicit DML privileges on those objects in the schema that are owned by other users.

Types of targets

The DDL privileges are applicable only at the schema level. These privileges cannot be granted or revoked on individual objects. DML privileges can be granted at two levels, the schema level and the object level.

Types of grantees

The types of grantee are owner, user, and public. A grantee can be a user or a privilege group.

DB_PUBLIC

A special authid DB_PUBLIC is introduced in SQL/MX Release 3.5. DB_PUBLIC is available for users and applications connected to user databases in multi-tenant environment. Outside the multi-tenant environment, DB_PUBLIC is the same as PUBLIC.

Types of user names

There are two types of user names, Guardian username and External Username. For more information, see User Management.

Authorization Requirements for Utilities

Utility

Authorization Requirements

CLEANUP

ALTER privilege on the schema containing the target object.

DUP

SELECT privilege on the source table or its schema. CREATE and ALTER privileges on the schema containing the target table.

FASTCOPY

SELECT privilege on the source table or its schema SELECT, INSERT, UPDATE, and DELETE privileges on the target table or its schema.

FIXUP

ALTER privilege on the schema containing the affected object.

IMPORT

INSERT privilege on the target table or its schema.

MODIFY

ALTER privilege on the schema containing the affected object.

POPULATE INDEX

ALTER privilege on the schema containing the affected object.

PURGEDATA

SELECT and DELETE on the target table or its schema.

VERIFY object

SELECT and EXECUTE privileges on the object or its schema.

VERIFY schema

ALTER privilege on the schema.