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 17-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.
Use the grant command to grant object access permissions. The syntax is:
grant {all [privileges]| permission_list}
on { table_name [(column_list)]
| view_name[(column_list)]
| stored_procedure_name}
to {public | name_list | role_name}
[with grant option]
Use the revoke command to revoke object access permissions. The syntax is:
revoke [grant option for]
{all [privileges] | permission_list}
on { table_name [(column_list)]
| view_name [(column_list)]
| stored_procedure_name}
from {public | name_list | role_name}
[cascade]
all or all privileges specifies all permissions applicable to the specified object, except decrypt permission. All object owners can use all with an object name to grant or revoke permissions on their own objects. If you are granting or revoking permissions on a stored procedure, all is the same as execute.
insert, update statistics, delete
statistics, truncate table, and delete permissions
do not apply to columns, so you cannot include them in a permission
list (or use the keyword all) if you specify
a column list.
permission_list is the list of permissions that you are granting. If you name more than one permission, separate them with commas. Table 17-2 illustrates the access permissions that can be granted on each type of object:
Object |
permission_list can include |
|---|---|
Table or view |
select, insert, delete, update, references, truncate table, update statistics, decrypt, delete statistics references applies to tables but not views; the other permissions apply to both tables and views. update statistics, delete statistics, and truncate table apply to tables on, not views. |
Column |
select, update, references |
Stored procedure |
execute |
Encryption key |
select |
You can specify columns in the permission_list or the column_list, but not both.
on specifies the object for which the permission is being granted or revoked. You can grant or revoke permissions for only one table, view, encryption key, or stored procedure object at a time. You can grant or revoke permissions for more than one column at a time, but all the columns must be in the same table or view. You can grant or revoke permissions only on objects in your current database.
public refers to the group “public,” which includes all Adaptive Server users. public means slightly different things for grant and revoke:
For grant, public includes the object owner. Therefore, if you have revoked permissions from yourself on your object, and later you grant permissions to public, you regain the permissions along with the rest of “public.”
For revoke, public excludes the owner.
name_list includes:
Group names
User names
A combination of user and group names, each separated from the next by a comma
role_name is an Adaptive Server system-defined or user-defined role. You can create and define a hierarchy of user-defined roles and grant them privileges based on the specific role granted. System-defined roles include sa_role (system administrator), sso_role (system security officer), and oper_role (operator). You cannot create or modify system-defined roles.
with grant option in a grant statement allows the users specified in name_list to grant the specified object access permissions to other users. If a user has with grant option permission on an object, that permission is not revoked when permissions on the object are revoked from public or a group of which the user is a member.
grant option for revokes with grant option permissions, so that the users specified in name_list can no longer grant the specified permissions to other users. If those other users have granted permissions to other users, you must use the cascade option to revoke permissions from them as well. The user specified in name_list retains permission to access the object, but can no longer grant access to other users. grant option for applies only to object access permissions, not to object creation permissions.
The cascade option in a revoke statement removes the specified object access permissions from the user(s) specified in name_list, and also from any users they granted those permissions to.
You may grant and revoke permissions only on objects in the current database.
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 or until a system administrator revokes the access. That is, if a system administrator revokes access, the user is denied access even though other users have granted access.
Only a system security officer can grant or revoke permission to create encryption keys. The database owner can create triggers on any user table. Users can create triggers only on tables that they own.
Permission to issue the create trigger command is granted to users by default.
When the system security officer revokes permission for a user to create triggers, a revoke row is added in the sysprotects table for that user. To grant permission to that user to issue create trigger, issue two grant commands: the first command removes the revoke row from sysprotects; the second inserts a grant row. The system security officer must grant permission to create triggers. If permission to create triggers is revoked, the user cannot create triggers even on tables that the user owns. Revoking permission to create triggers from a user affects only the database where the revoke command was issued.
