Managing individual user IDs and permissions

User's Guide
PART 4. Database Administration and Advanced Use
CHAPTER 21. Managing User IDs and Permissions

Managing individual user IDs and permissions

This section describes how to create new users and grant permissions to them. For most databases, the bulk of permission management should be carried out using groups, rather than by assigning permissions to individual users one at a time. However, as a group is simply a user ID with special properties, you should read and understand this section before moving on to the discussion of managing groups.

Creating new users

A new user is added to a database by the DBA using the GRANT CONNECT statement. For example:

To add a new user to a database, with user ID M_Haneef and password welcome:

From Interactive SQL, connect to the database as a user with DBA authority.

Issue the SQL statement:

GRANT CONNECT TO M_Haneef
IDENTIFIED BY welcome

Only the DBA has the authority to add new users to a database.

Initial permissions for new users

By default, new users are not assigned any permissions beyond connecting to the database and viewing the system tables. In order to access tables in the database, they need to be assigned permissions.

The DBA can set the permissions granted automatically to new users by assigning permissions to the special PUBLIC user group, as discussed in Special groups.

Creating users in Sybase Central

To create a user in Sybase Central:

Connect to the database.
Click the Users and Groups folder for that database.
Double-click Add User. A Wizard is displayed, which leads you through the process.

For more information, see the Sybase Central online Help.

Changing a password

Changing a user's password

You can change your password, or that of another user if you have DBA authority, using the GRANT statement. For example, the following command changes the password for user ID M_Haneef to new_password:

GRANT CONNECT TO M_Haneef
IDENTIFIED BY new_password

Changing the DBA password

The default password for the DBA user ID for all databases is SQL. You should change this password to prevent unauthorized access to your database. The following command changes the password for user ID DBA to new_password:

GRANT CONNECT TO DBA
IDENTIFIED BY new_password

Granting DBA and RESOURCE authority

DBA and RESOURCE authority are granted in the same manner.

To grant RESOURCE permissions to a user ID:

Connect to the database as a user with DBA authority.
Type and execute the SQL statement:
```
GRANT RESOURCE TO userid
```

For DBA authority, the appropriate SQL statement is:

GRANT DBA TO userid

Notes

Only the DBA may grant DBA or RESOURCE authority to database users.
DBA authority is very powerful, granting the ability to carry out any action on the database and access to all the information in the database. It is generally inadvisable to grant DBA authority to more than a very few people.
You should consider giving users with DBA authority two user IDs, one with DBA authority and one without, so that they connect as DBA only when necessary.
RESOURCE authority allows the user to create new database objects, such as tables, views, indexes, procedures, or triggers.

Granting permissions on tables and views

You can assign a set of permissions on individual tables and views. Users can be granted combinations of these permissions to define their access to a table or view.

Combinations of permissions

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 or view. 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 or view, but does not give permission to alter it.
ALL permission grants all the above permissions.

Example 1

All table and view 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 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

One limitation of table and view permissions is that they apply to all the data in a table or view (except for the UPDATE permission which may be restricted). Finer tuning of user permissions can be accomplished by creating procedures that carry out actions on tables, and then granting users the permission to execute the procedure.

Granting user permissions on tables in Sybase Central

One way to grant a user permissions on a table in Sybase Central is as follows:

Connect to the database.
Double-click the Tables folder for that database, to display the tables in the left panel.
Click the Users and Groups folder, and locate the user you want to grant permissions to.
Drag the user to the table for which you want to grant permissions.

For more information, see the Sybase Central online Help.

Granting users the right to grant permissions

Each of the table and view permissions described in Granting permissions on tables and views can be assigned WITH GRANT OPTION. This option gives the right to pass on the permission to other users. This feature is discussed in the context of groups in section Permissions of groups.

Example

You can grant permission to M_Haneef to delete rows from the table named sample_table, and the right to pass on this permission to other users, as follows:

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

Type and execute the SQL statement:

GRANT DELETE ON sample_table
TO M_Haneef
WITH GRANT OPTION

Granting permissions on procedures

There is only one permission that may be granted on a procedure, and that is the EXECUTE permission to execute (or CALL) the procedure.

Permission to execute stored procedures may be granted by the DBA or by the owner of the procedure (the user ID that created the procedure).

The method for granting permissions to execute a procedure is similar to that for granting permissions on tables and views, discussed in Granting permissions on tables and views.

Example

You can grant M_Haneef permission to execute a procedure named my_procedure, as follows:

Connect to the database as a user with DBA authority or as owner of my_procedure procedure.

Execute the SQL statement:

GRANT EXECUTE
ON my_procedure
TO M_Haneef

Execution permissions of procedures

Procedures execute with the permissions of their owner. Any procedure that updates information on a table will execute successfully only if the owner of the procedure has UPDATE permissions on the table.

As long as the procedure owner does have the proper permissions, the procedure will execute successfully when called by any user assigned permission to execute it, whether or not they have permissions on the underlying table. You can use procedures to allow users to carry out well-defined activities on a table, without having any general permissions on the table.

Granting user permissions on procedures in Sybase Central

One way to grant a user permissions on a procedure in Sybase Central is as follows:

Connect to the database.
Click the Users and Groups folder, and locate the user you want to grant permissions to.
Right-click the user, and select Copy from the popup menu.
Locate the procedure you want to allow the user to execute, in the Stored Procedures folder.
Click the procedure, and choose Edit->Paste from the main menu to grant permissions.

For more information, see the Sybase Central online Help.

Execution permissions of triggers

Triggers are executed by the server in response to a user action; no permissions are required for triggers to be executed. When a trigger executes, it does so with the permissions of the creator of the table with which they are associated.

For more information on trigger permissions, see Trigger execution permissions.

Revoking user permissions

Any user's permissions are a combination of those that have been granted and those that have been revoked. By revoking and granting permissions, you can manage the pattern of user permissions on a database.

The REVOKE statement is the exact converse of the GRANT statement. To disallow M_Haneef from executing my_procedure, the command is:

REVOKE EXECUTE 
ON my_procedure 
FROM M_Haneef

This command must be issued by the DBA or by the owner of the procedure.

Permission to delete rows from sample_table may be revoked by issuing the command:

REVOKE DELETE 
ON sample_table 
FROM M_Haneef