Granting and revoking permissions  Concrete identification

Chapter 17: Managing User Permissions

Object access permissions

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 17-1 lists the types of object access permissions and the objects to which they apply.

Table 17-1: 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

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]
grant [truncate table | update statistics | delete statistics] on table_name to {user_name | role_name}

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]
revoke [truncate table | update statistics | delete statistics] on table_name from {user_name | role_name}

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 permissions to create triggers. 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. 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.





Copyright © 2005. Sybase Inc. All rights reserved. Concrete identification

View this book as PDF