Schema privileges and authorities

Schema privileges are in the object privilege category.

Object privileges are shown in Figure 1.

Figure 1. Object Privileges
Diagram showing categories of object privileges.

Schema privileges and authorities involve actions on schemas in a database. A user, group, role, or PUBLIC can be granted any of the following privileges:

  • CREATEIN allows the user to create objects within the schema.
  • ALTERIN allows the user to alter objects within the schema.
  • DROPIN allows the user to drop objects from within the schema.
  • SELECTIN allows the user to execute select query on tables within the schema
  • UPDATEIN allows the user to update a table within the schema
  • INSERTIN allows the user to insert data into a table within the schema
  • DELETEIN allows the user to delete rows from tables within the schema
  • EXECUTEIN allows the user to execute user-defined functions, methods, procedures, packages, or modules defined in the schema.

Schema authorities

Schema LOAD authority:

The schema LOAD authority gives a user the right to use the LOAD utility on all existing and future tables defined in this schema as long as they have either DATAACCESS on the database, DATAACCESS on this schema, or the relevant table-level privileges. It also gives the user the ability to use RUNSTATS on objects in this schema.

The relevant table-level privileges are:
  • INSERT privilege on the table for LOAD with mode INSERT, TERMINATE (to terminate a previous LOAD INSERT), or RESTART (to restart a previous LOAD INSERT)
  • INSERT and DELETE privilege on the table for LOAD with mode REPLACE, TERMINATE (to terminate a previous LOAD REPLACE), or RESTART (to restart a previous LOAD REPLACE)
  • INSERT privilege on the exception table, if such a table is used as part of LOAD

SCHEMAADM authority:

The SCHEMAADM authority provides administrative authority over a single schema.

This schema administrator implicitly possesses the following for this schema:
  • CREATEIN privilege
  • ALTERIN privilege
  • DROPIN privilege
  • schema LOAD authority
  • The ability to reorganize indexes/tables in this schema
  • The ability to use RUNSTATS on objects in this schema
  • The BINDADD authority for packages whose names are qualified by this schema

The SCHEMAADM authority can be granted only by a user with schema ACCESSCTRL, ACCESSCTRL, or SECADM authority. The SCHEMAADM authority cannot be granted to PUBLIC.

Schema ACCESSCTRL authority:

The schema ACCESSCTRL authority provides administrative authority to issue the following statements for this schema:
  • GRANT (and REVOKE) SCHEMAADM authority
  • GRANT (and REVOKE) schema DATACCESS authority
  • GRANT (and REVOKE) schema LOAD authority
  • GRANT (and REVOKE) CREATEIN privilege
  • GRANT (and REVOKE) ALTERIN privilege
  • GRANT (and REVOKE) DROPIN privilege
  • GRANT (and REVOKE) SELECTIN privilege
  • GRANT (and REVOKE) INSERTIN privilege
  • GRANT (and REVOKE) UPDATEIN privilege
  • GRANT (and REVOKE) DELETEIN privilege
  • GRANT (and REVOKE) EXECUTEIN privilege
  • GRANT (and REVOKE) READ, WRITE privileges on global variables defined in this schema
  • GRANT (and REVOKE) CONTROL privilege on indexes defined in this schema
  • GRANT (and REVOKE) EXECUTE privilege on modules defined in this schema
  • GRANT (and REVOKE) BIND,CONTROL,EXECUTE privileges on packages defined in this schema
  • GRANT (and REVOKE) EXECUTE privilege on routines defined in this schema
  • GRANT (and REVOKE) USAGE, ALTER privileges on sequences defined in this schema
  • GRANT (and REVOKE) ALTER, CONTROL, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE privileges for table, views, or nicknames defined in this schema
  • GRANT (and REVOKE) USAGE privilege on XSR objects defined in this schema

Schema ACCESSCTRL authority can only be granted by a user with ACCESSCTRL or SECADM authority. The schema ACCESSCTRL authority cannot be granted to PUBLIC.

Schema DATAACCESS authority:

The schema DATAACCESS authority level provides the following privileges and authorities within the schema:
  • LOAD authority
  • SELECTIN privilege
  • INSERTIN privilege
  • UPDATEIN privilege
  • DELETEIN privilege
  • EXECUTE privilege on packages defined in this schema
  • EXECUTE privilege on modules defined in this schema
  • EXECUTE privilege on routines defined in this schema
  • READ, WRITE (except variables which are read-only) privilege on all global variables defined in this schema
  • USAGE privilege on all XSR objects defined in this schema
  • USAGE privilege on all sequences defined in this schema

Schema DATAACCESS can be granted only by a user who holds schema ACCESSCTRL, ACCESSCTRL, or SECADM authority. The schema DATAACCESS authority cannot be granted to PUBLIC.

The owner of the schema has CREATEIN, ALTERIN, DROPIN, and SCHEMADM privileges. The owner also gets the privilege to grant CREATEIN, ALTERIN, and DROPIN to others. The objects that are manipulated within the schema object include: tables, views, indexes, packages, data types, functions, triggers, procedures, and aliases.