Teradata RDBMS Database Administration - NCR

Privilege Description
Implicit Implicit privileges are bestowed by the system to owners. An owner of a user or database is any user who owns the space from which that user or database was created. An owner can grant privileges and the WITH GRANT OPTION on the owned entity. Ownership privileges cannot be refused or revoked.
Automatic Automatic privileges are bestowed by the system to creators The creator of an entity is the user who submits a statement or executes a macro containing a statement that creates a database, user, role, profile, table, view, macro, join index, stored procedure, or other entity. When the CREATE statement completes successfully, the appropriate rows are inserted in the DBC.AccessRights table. An automatic privilege can be dropped via the DROP statement. It can also be removed via the REVOKE statement.
Explicit Explicit privileges are privileges that are explicitly granted to a user by the database or security administrator, or by another user with the appropriate privileges. You can use privileges to control access to most database entities, including DATABASE, USER, TABLE, JOIN INDEX, HASH INDEX, VIEW, MACRO, STORED PROCEDURE, and TRIGGER. The GRANT statement adds new rows to the DBC.AccessRights table. The REVOKE statement removes them. (For the special circumstance of granting rights to and revoking rights from PUBLIC, see "Granting Access Rights to PUBLIC", below.) You can use the REVOKE statement to remove only the WITH GRANT OPTION from a granted privilege, leaving the access right but not the privilege to grant that right to others. Explicit privileges are also removed when a DROP statement is executed on the entity or a DROP/DELETE DATABASE/USER statement causes the entity to be dropped.

6 - 10 Teradata RDBMS Database Administration

Chapter 6: Controlling Access

Controlling Inherited and Group Privileges

Granting Privileges

The following table summarizes the privileges, authority, and requirements for access by owners, creators, and users.

Recipient

Privilege Acquisition and Type

Owners Owners have the implicit privilege to grant privileges on their owned objects. (This is not true for stored procedures. For complete details, see "Stored Procedures" in Teradata RDBMS SQL Reference, Volume 6.)

Users A new user must be explicitly granted the CREATE PROFILE, CREATE

ROLE, CREATE PROCEDURE, CREATE DATABASE, and CREATE USER privileges, even on his or her default database.

For example, before Jones can create a new database in Jones, the DBA or the creator of Jones must submit a GRANT statement to explicitly grant to Jones the CREATE DATABASE privilege.

• To grant the CREATE privilege on objects to other users, the creator must have been granted CREATE privilege ... WITH GRANT OPTION. (Also see "Creating a Database or User" on page 2-2.)

• To grant a role to another user, a user must have been granted the role with the WITH ADMIN OPTION. (Also see "Implementing Roles and Profiles" on page 5-12.)

• To grant the EXECUTE PROCEDURE privilege to other users, the creator must have been granted the CREATE PROCEDURE privilege with the WITH GRANT OPTION. (Also see "Limiting Data Access with Stored Procedures" on page 6-44.)

Creators • The creator of a database or user is automatically granted all privileges on that database or user space.

Creators acquire privileges on created objects automatically. For example, the creator of a table is automatically granted INSERT, SELECT, UPDATE, DELETE, and DROP TABLE on that table.

The creator of a user needs to submit an explicit GRANT CREATE statement to grant to that new user the right to create databases, users, procedures, roles, and/or profiles in his or her own space.

The creator of a table or view needs to submit an explicit GRANT statement to grant to other users the SELECT or any other privilege on that table or view.

The creator has the EXECUTE privilege to statements in the body of the created macro or stored procedure. Checks for access rights to the objects targeted by those statements are performed against:

The executing user, for a macro

The owning user, for a stored procedure

The following list summarizes rights you can explicitly grant to another user.

Note: You can specify a user as the name of a user, the name of a role, or the keyword PUBLIC, meaning everyone. If a role name is used, then all users who are role members gain the specified privilege or privileges.