Authorization, privileges and object ownership
Users (identified by an authorization ID) can successfully execute SQL statements only if they have the authority to perform the specified function. To create a table, a user must be authorized to create tables; to alter a table, a user must be authorized to alter the table; and so forth.
There are two forms of authorization:
- administrative authority
- The
person or persons holding administrative authority are charged
with the task of controlling the database manager and are responsible
for the safety and integrity of the data. Those with administrative
authority implicitly have all privileges on all objects and control
who will have access to the database manager and the extent of this
access.
The security officer and all users with *ALLOBJ authority have administrative authority.
- privileges
- Privileges are
those activities that a user is allowed to perform. Authorized users
can create objects, have access to objects they own, and can pass
on privileges on their own objects to other users by using
the GRANT statement.
Privileges may be granted to specific users or to PUBLIC. PUBLIC specifies that a privilege is granted to a set of users (authorization IDs). The set consists of those users (including future users) that do not have privately granted privileges on the table or view. This affects private grants. For example, if SELECT has been granted to PUBLIC, and UPDATE is then granted to HERNANDZ, this private grant prevents HERNANDZ from having the SELECT privilege.
The REVOKE statement can be used to REVOKE previously granted privileges. A revoke of a privilege from an authorization ID revokes the privilege granted by all authorization IDs. Revoking a privilege from an authorization ID will not revoke that same privilege from any other authorization IDs that were granted the privilege by that authorization ID.
When an object is created, the authorization ID of the statement must have the privilege to create objects in the implicitly or explicitly specified schema. The authorization ID of a statement has the privilege to create objects in the schema if:
- it is the owner of the schema, or
it has *OBJOPR, *EXECUTE, and *ADD to the schema.
When an object is created, one authorization ID is assigned ownership of the object. Ownership gives the user complete control over the object, including the privilege to drop the object. The privileges on the object can be granted by the owner, and can be revoked from the owner. In this case, the owner may temporarily be unable to perform an operation that requires that privilege. Because he is the owner, however, he is always allowed to grant the privilege back to himself.
When an object is created:
- If SQL names were specified, the owner of the object is the user profile with the same name as the schema into which the object is created, if a user profile with that name exists. Otherwise, the owner of the object is the user profile or group user profile of the job executing the statement.
- If system names were specified, the owner of the object is the user profile or group user profile of the job executing the statement.
Authority granted to *PUBLIC on SQL objects depends on the naming convention that is used at the time of object creation. If *SYS naming convention is used, *PUBLIC acquires the create authority (CRTAUT) of the library into which the object was created. If *SQL naming convention is used, *PUBLIC acquires *EXCLUDE authority.
In the Authorization sections of this book, it is assumed that the owner of an object has not had any privileges revoked from that object since it was initially created. If the object is a view, it is also assumed that the owner of the view has not had the system authority *READ revoked from any of the tables or views that this view is directly or indirectly dependent on. The owner has system authority *READ for all tables and views referenced in the view definition, and if a view is referenced, all tables and views referenced in its definition, and so forth. For more information about authority and privileges, see Security Reference.