User's Guide

Part III. Using SQL Anywhere

Chapter 24. Managing User IDs and Permissions

For databases that require a high level of security, defining permissions directly on tables has limitations. Any permission granted to a user on a table applies to the whole table. There are many cases when users' permissions need to be shaped more precisely than on a table-by-table basis. For example:

• It is not desirable to give access to personal or sensitive information stored in an employee table to users who need access to other parts of the table.
• You may wish to give sales representatives update permissions on a table containing descriptions of their sales calls, but limit such permissions to their own calls.

In these cases, you can use views and stored procedures to tailor permissions to suit the needs of your organization. This section describes some of the uses of views and procedures for permission management.

For information on how to create views, see "Working with views".

Using views for tailored security

Views are computed tables that contain a selection of rows and columns from base tables. Views are useful for security when it is appropriate to give a user access to just one portion of a table. The portion can be defined in terms of rows or in terms of columns. For example, you may wish to disallow a group of users from seeing the salary column of an employee table, or you may wish to limit a user to see only the rows of a table that they have created.

Example

The Sales manager needs access to information in the database concerning employees in the department. However, there is no reason for the manager to have access to information about employees in other departments.

This example describes how to create a user ID for the sales manager, create views that provides the information she needs, and grants the appropriate permissions to the sales manager user ID.

The first example gives the sales manager permission to see the employees in the Sales department by defining a view on the employee table.

1. Create the new user ID using the GRANT command, from a user ID with DBA authority. Enter the following:

        CONNECT "dba" IDENTIFIED by sql ;
        GRANT CONNECT TO SalesManager IDENTIFIED BY sales
   

   (You must enclose DBA in quotation marks because it is a SQL keyword, just like SELECT and FROM.)

2. Define a view which only looks at sales employees as follows:

        CREATE VIEW emp_sales AS
        SELECT emp_id, emp_fname, emp_lname
        FROM "dba".employee
        WHERE dept_id = 200
   

   The table should be identified as "dba".employee, with the owner of the table explicitly identified, for the SalesManager user ID to be able to use the view.

3. Give SalesManager permission to look at the view: GRANT SELECT ON emp_sales TO SalesManager Exactly the same command is used to grant permission on a view as to grant permission on a table.

Example 2

The next example creates a view which allows the Sales Manager to look at a summary of sales orders. This view requires information from more than one table for its definition:

1. Create the view.

        CREATE VIEW order_summary AS
        SELECT order_date, region, sales_rep, company_name
        FROM "dba".sales_order
            KEY JOIN "dba".customer
   

2. Grant permission for the Sales Manager to examine this view.

        GRANT SELECT
        ON order_summary
        TO SalesManager
   

3. To check that the process has worked properly, connect to the SalesManager user ID and look at the views you have created:

        CONNECT SalesManager IDENTIFIED BY sales ;
        SELECT * FROM "dba".emp_sales ;
        SELECT * FROM "dba".order_summary ;
   

No permissions have been granted to the Sales Manager to look at the underlying tables. The following commands produce permission errors.

     SELECT * FROM "dba".employee ;
     SELECT * FROM "dba".sales_order

Update permissions on views

The example shows how to use views to tailor SELECT permissions. INSERT, DELETE, and UPDATE permissions can also be granted on views.

For information on allowing data modification on views, see "Using views".

Using procedures for tailored security

While views restrict access on the basis of data, procedures restrict the actions a user may take. As described in "Granting permissions on procedures", a user may have EXECUTE permission on a procedure without having any permissions on the table or tables on which the procedure acts.

Strict security

For strict security, you can disallow all access to the underlying tables, and grant permissions to users or groups of users to execute certain stored procedures. With this approach, the manner in which data in the database can be modified is strictly defined.

Stored procedures Stored procedures are not supported by the SQL Anywhere Desktop Runtime system.