User's Guide

Part III. Using SQL Anywhere

Chapter 24. Managing 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 groups are simply a user ID with special properties attached, 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:

1. From ISQL, connect to the database as a user with DBA authority.
2. 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:

1. Connect to the database.
2. Click the Users and Groups folder for that database.
3. 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 users 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 SQL Anywhere 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

DBA must be enclosed in double quotes in this expression as it is a SQL Anywhere keyword.

If you are using ISQL, it is a good idea to put your permission grants into a command file for reference and so that it can be modified and run again if it is necessary to recreate the permissions.

Granting DBA and resource authority

DBA and RESOURCE authority are granted in exactly the same manner as each other.

To grant resource permissions to user ID M_Haneef:

1. Connect to the database as a user with DBA authority.
2. Type and execute the SQL statement:

        GRANT RESOURCE TO M_Haneef.
   

For DBA authority, the appropriate SQL statement is:

     GRANT DBA TO M_Haneef

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

SQL Anywhere provides 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 to alter the structure of a table or to create triggers on a table) and REFERENCES (permission to create indexes on a table, and to create foreign keys) 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.
• Granting ALL permissions 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:

1. Connect to the database as a user with DBA authority, or as the owner of sample_table.
2. 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:

1. Connect to the database as a user with DBA authority, or as the owner of sample_table.
2. Type and execute the 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.

Procedure permissions are discussed in "Granting permissions on procedures".

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".

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:

1. Connect to the database as a user with DBA authority, or as the owner of sample_table:
2. Type and execute the SQL statement:

        GRANT DELETE ON sample_table
        TO M_Haneef
        WITH GRANT OPTION
   

Granting user permissions on tables in Sybase Central

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

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

For more information, see the Sybase Central online Help.

Granting permissions on procedures

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:

1. Connect to the database as a user with DBA authority or as owner of my_procedure procedure.
2. 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 table in Sybase Central is as follows:

1. Connect to the database.
2. Click the Users and Groups folder, and locate the user you want to grant permissions to.
3. Right-click the user, and select Copy from the popup menu.
4. Locate the procedure you want to allow the user to execute, in the Stored Procedures folder.
5. 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 database engine in response to a user action; no permissions are required for triggers to be executed. When a trigger executes, it does have permissions associated with it that determine its ability to carry out actions.

Triggers execute 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

The ability to revoke permissions is useful in order to take away permissions that have been explicitly granted to a user, but which are no longer appropriate.

More than this, any user's permissions are a combination of those that have been granted and those that have been revoked. Revoking and granting permissions work together in managing 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