Considerations for GRANT

  • DML privileges for a schema target apply to all current and future objects in that schema, subject to the authorization requirements listed below. That means, a grant of a privilege on a schema gives the grantee corresponding access rights on all existing applicable objects in that schema and on all future applicable objects in that schema.

  • DDL privileges for a schema target enable the grantee to perform DDL and utility operations on objects in that schema. However, once an object is created its existence does not depend on the owner holding any DDL privilege on its schema.

  • Privileges granted on a schema cannot be revoked on individual objects in that schema, and conversely.

  • ALL_DDL is a combination of CREATE, ALTER, and DROP privileges. If ALL_DDL is granted, CREATE, ALTER, and DROP privileges can be revoked individually.

  • ALL_DML on an individual object will be resolved into one or more relevant privileges depending on the type of object:
    • SELECT, INSERT, UPDATE, DELETE, REFERENCES when the target is a table or a view.

    • EXECUTE when the target is a stored procedure.

    • USAGE when the target is a sequence generator.

  • ALL_DML on a schema will be resolved into the combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, USAGE, and EXECUTE privileges. The relevant privileges will be applied to objects in the schema, depending on the type of object.

  • ALL [PRIVILEGES] is equivalent to ALL_DML when the target is an individual object, and to the combination of ALL_DML and ALL_DDL when the target is a schema.

  • When granting to a privilege group, the grantee is not any individual user in that privilege group.

  • A privilege group cannot act as a grantor of a privilege.

  • The privilege group grantees cannot be used WITH GRANT OPTION.

  • A grant to a privilege group gives access rights on the target object to all the members of the privilege group including users that are members at GRANT time and users added as group members.

  • The users in a privilege group lose the access rights of the privilege group once they are removed from the privilege group, and when the privilege group is dropped.