Object access permissions regulate the use of certain commands that access certain database objects. For example, you must explicitly be granted permission to use the select command on the authors table. Object access permissions are granted and revoked by the object owner (and system administrators or system security officers), who can grant them to other users.
Table 6-1 lists the types of object access permissions and the objects to which they apply.
Permission |
Object |
|---|---|
select |
Table, view, column |
update |
Table, view, column |
insert |
Table, view |
delete |
Table, view |
references |
Table, column |
execute |
Stored procedure |
truncate table |
Table |
delete statistics |
Table |
update statistics |
Table |
decrypt |
Table, view, column |
select |
Encryption key |
The references permission refers to referential integrity constraints that you can specify in an alter table or create table command. The decrypt permission refers to the permission required to decrypt an encrypted column. An encryption key’s select permission refers to the permissions required to use encryption keys in create table, alter table or select into command to encrypt columns. The other permissions refer to SQL commands. Object access permissions default to the object’s owner, or system administrators or system security officers for decrypt on an encrypted column and select on an encryption key, and can be granted to other users.
If several users grant access to an object to a particular user, the user’s access remains until access is revoked by all those who granted access. If a system administrator revokes access, the user is denied access, even though other users have granted access.
Use the grant command to grant object access permissions. See the Reference Manual: Commands.
You can grant select, update and delete permission using a where clause that can restrict access on a row by row basis based on the condition in the where clause. See “Granting Predicated Privileges”.
