Administration Getting Started

Step 2. Controlling Access to the Data

As a database administrator, you might need to control the type of access people have to data, or restrict their view of the data.

This section briefly describes two methods for controlling access to the data:

• Managing database authorities and privileges for database objects. An overview of database authorities, privileges on database objects, and system authorities is provided.
• Creating a view

About Authorities and Privileges

Database authorities involve actions on a database as a whole. When a database is created, some authorities are automatically granted to anyone who accesses the database. For example, CONNECT, CREATETAB, BINDADD and IMPLICIT_SCHEMA authorities are granted to all users. Database privileges involve actions on specific objects within the database. When a database is created, some privileges are automatically granted to anyone who accesses the database. For example, SELECT privilege is granted on catalog views and EXECUTE and BIND privilege on each successfully bound utility is granted to all users.

In "Welcome", we recommend that you use the user ID that was created following the instructions in the Quick Beginnings for your platform. That user ID has SYSADM authority. The Administration Guide describes all the system authority levels. In short they are:

• Administrative authority (SYSADM or DBADM): They give full privileges for a set of objects.

• System authority (SYSCTRL or SYSMAINT): They give full privileges for managing the system, but do not allow access to the data.

Together, privileges and authorities act to control access to an instance and its database objects. Users can access only those objects for which they have the appropriate authorization, that is, the required privilege or authority.

See the section on authorities, privileges, and authorizations, and the section on controlling database access in the Administration Guide for a more detailed discussion of authorities and privileges.

Granting and Revoking Authorities and Privileges

You can use the DB2 administration tools to grant and revoke privileges for users and groups for databases, table spaces, tables, views, and schemas. You do this as follows:

1. From the Control Center, click mouse button 2 on the database, table space, table, view, or schema for which you want to grant or revoke privileges, and select Authorities or Privileges from the pop-up menu. The Authorities window or Privileges window opens.

2. Select the User page to work with user authorities or privileges, or the Group page to work with group authorities or privileges.

3. Select one or more users from the User page, for example. (To add a user to the list, click on the Add User push button.)

4. Along the bottom of the window, select Yes, No, or Grant for each individual authority or privilege. (Grant is displayed only for objects for which it is a valid option.)

5. When you have finished, click on the Apply push button.

See the Administration Guide for the details of managing database authorities, privileges for database objects, and system authorities.

Creating a View to Restrict Access

A view is defined in "Views". You can define a view so that it includes some or all of the rows in a table. You can create a view, for example, for each individual department manager that allows each manager to look at the salary information for their employees. This is done by specifying the department number for each manager when you define each view.

To create a view:

1. Open the Create View window:

   1. From the Control Center, expand the object tree until you find the Views folder.

   2. Click mouse button 2 on the Views folder, and select Create from the pop-up menu. The Create View window opens.

2. Optional: Use the View schema list box to specify the schema for the view that you are creating.

3. In the View name field, type the lower-order (unqualified) identifier for the view that you are creating.

4. In the SQL statement box, edit the skeleton CREATE VIEW SQL statement to create your own. For example, the following statement creates a view of the non-managers in the STAFF table where salary and commission do not show through from the base table to the view:

   AS SELECT ID, NAME, DEPT, JOB, YEARS
        FROM STAFF
        WHERE JOB <> 'MGR'
   

   Note that the <> operator in the WHERE clause means not equal.

   See the SQL Reference for information on the CREATE VIEW statement.

5. Optional: Specify a Check option by clicking on either the Cascaded or Local radio button.

   A Check option specifies the constraint that conforms to the definition of the view. A row that does not conform to the definition of the view is a row that does not satisfy the search conditions of the view. The online help describes each of the options.

6. Optional: Type a comment in the Comment field.

7. Click on OK to begin processing the SQL statement. The view is created and listed in the list of views in the contents pane.

8. To see the rows of data for the view, click with mouse button 2 on the view and select Sample contents from the pop-up menu. The view is displayed.

[ DB2 List of Books | Search the DB2 Books ]