Granting permissions on tables

ASA Database Administration Guide
Managing User IDs and Permissions
Managing individual user IDs and permissions

Granting permissions on tables

You can assign a set of permissions on individual tables and grant users combinations of these permissions to define their access to a table.

You can use either Sybase Central or Interactive SQL to set permissions. In Interactive SQL, you can use the following SQL statements to grant permissions on tables.

The ALTER permission allows a user to alter the structure of a table or to create triggers on a table. The REFERENCES permission allows a user to create indexes on a table, and to create foreign keys. These permissions grant the authority to modify the database schema, and so will not be assigned to most users. These permissions do not apply to views.
The DELETE, INSERT, and UPDATE permissions grant the authority to modify the data in a table. Of these, the UPDATE permission may be restricted to a set of columns in the table or view.
The SELECT permission grants authority to look at data in a table, but does not give permission to alter it.
ALL permission grants all the above permissions.

To grant permissions on tables or columns (Sybase Central)

Connect to the database.
Open the Tables folder for that database.
Right-click a table and choose Properties from the popup menu.
On the Permissions tab of the Table property sheet, configure the permissions for the table:
- Click Grant to select users or groups to which to grant full permissions.
- Click in the fields beside the user or group to set specific permissions. Permissions are indicated by a check mark, and grant options are indicated by a check mark with two '+' signs.
- Select a user and click the button beside References, Select, or Update to set that type of permission on individual columns.
- Select a user or group in the list and click Revoke to revoke all permissions.

Tips

Legend for the columns on the Permissions page: A=Alter, D=Delete, I=Insert, R=Reference, S=Select, U=Update.

You can also assign permissions from the user/group property sheet. To assign permissions to many users and groups at once, use the table's property sheet. To assign permissions to many tables at once, use the user's property sheet.

To grant permissions on tables or columns (SQL)

Connect to the database with DBA authority or as the owner of the table.
Execute a GRANT statement to assign the permission.

For more information, see GRANT statement.

Example 1

All table permissions are granted in a very similar fashion. You can grant permission to M_Haneef to delete rows from the table named sample_table as follows:

Connect to the database as a user with DBA authority, or as the owner of sample_table.
Type and execute the following SQL statement:
```
GRANT DELETE
ON sample_table
TO M_Haneef
```

Example 2

You can grant permission to M_Haneef to update the column_1 and column_2 columns only in the table named sample_table as follows:

Connect to the database as a user with DBA authority, or as the owner of sample_table.

Type and execute the following SQL statement:

GRANT UPDATE (column_1, column_2)
ON sample_table
TO M_Haneef

Table view permissions are limited in that they apply to all the data in a table (except for the UPDATE permission which may be restricted). You can fine-tune user permissions by creating procedures that carry out actions on tables, and then granting users the permission to execute the procedure.