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), who can grant them to other users.
Table 11-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 |
The references permission refers to referential integrity constraints that you can specify in an alter table or create table command. The other permissions refer to SQL commands. Object access permissions default to System Administrators and the object’s owner, 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]
Notes on the keywords and parameters are as follows:
all or all privileges specifies all permissions applicable to the specified object. 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 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 11-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. references applies to tables but not views; the other permissions apply to both tables and views. |
Column |
select, update, references |
Stored procedure |
execute |
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, 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 only grant or revoke permissions 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 user(s) specified in name_list to grant the specified object access permission(s) 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 user(s) 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 only grant and revoke permissions 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 permissions to create triggers. The Database Owner can create triggers on any user table. Users can only create triggers 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. 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.
