Database Object Access

By using database roles and linking database users to them, you have control over who can and cannot access the database. After getting access to the database, users can access all data stored there: the records (rows) stored in the tables, stored procedures, views, and so on.You might feel the need to prevent specific users from altering certain tables, executing certain stored procedures, or even certain table columns from being shown. All these items, called database objects, can be protected from users. The best way to do this is through roles.

There are two ways to set these access rights. Both work because they provide a different view on the permissions that are stored in one place in the database, a system table.The first view is taking the role as a starting point. In this case, you are shown all available database objects with the corresponding permissions. Depending on the number of objects that are defined in the database, this list can be quite overwhelming.To get a better understanding of what you are presented with, you can open the permission list of the BizTalk_dba role in the InterchangeBTM database:

1. Open SQL Server Enterprise Manager.

2. Expand until all databases are shown.

3. Expand InterchangeBTM.

4. Create a New Database Role called BizTalk_dba, and make sa_InterchangeBTM a member of this role.

5. Reopen BizTalk_dba by right-clicking it and selecting Properties.

6. The Database Role Properties dialog appears. Press Permissions____

7. The current Database Role Properties dialog will now appear with a Permissions tab (Figure 8.24).

Figure 8.24 Setting Object Permissions in the Database Role Properties Window

Figure 8.24 Setting Object Permissions in the Database Role Properties Window

8. Notice that even you "'asked" for the permissions of BizTalk_dba, you are able to select any role available within the current database.

9. Below the Database role is a list selection.The option List all objects is the default, since at this point selecting List only objects with permissions for this role will give you an empty list.

10. The Object list is sorted alphabetically, and the Object column shows an icon indicating the type of object and its name.The next column shows the owner of the object.

11. The last six columns represent the possible permissions, with the valid permissions showing a check box in the cell. The meanings of the permissions are SQL-statement related. Let's look at them:

■ SELECT is the role allowed to perform a SELECT-FROM-

WHERE SQL statement on the table; hence, allowed to read records (rows) from the table.

■ INSERT is the role allowed to perform an INSERT-INTO-WITH SQL statement on the table; hence, allowed to add records to the table.

■ UPDATE is the role allowed to perform an UPDATE-SET SQL statement on the table; hence, allowed to modify records in the table.

■ DELETE is the role allowed to perform a DELETE-FROM-WHERE SQL statement on the table; hence, allowed to remove records from the table.

■ EXEC is the role allowed to perform an EXECUTE statement on the stored procedure; hence, allowed to run the stored procedure.

■ DRI is the role allowed to execute Declarative Referential Integrity constraints on the table. DRI constraints enable you to ensure consistency between tables, effectuated by equal columns with the same values.

For example, you have two tables, "Vendor" and "Products." Each Vendor record is identified with a Vendor_Id. Every record in the Products table is identified by a Product_Id and a Vendor_Id showing who is selling this product. Every Vendor_Id used in the Products table must correspond with one and only one Vendor_Id in the Vendors table. This is called referential integrity. A DRI constraint can be that before a DELETE on a vendor record is effectuated, it is first checked if there are still product records with this Vendor_Id. If so, the Vendor record cannot be deleted.

The ability to clamp down the access to the database is very powerful, but by its extensiveness also very prone to errors, resulting in runtime errors due to a shortage of permissions. Nevertheless, an additional level of permissions gives you even more possibilities. At the bottom of the Permissions tab of the Database Role Properties windows is a Columns... button.This button becomes active if the object you select is a table. Do the following:

1. Select an object that is a database table; for example, the first object adm_Group.

2. Press Columns. , and a Column Permissions dialog appears (Figure 8.25).

3. The dialog shows the role name (although it is called User name) and object name. Below it is the option to limit the listed columns.

Figure 8.25 Setting Permissions in the Column Permission Window

DIICTH^MMM

J

l:

LiiMJranul'fdng

i:

L S m»

u

upnw

::

1 i^ùb^wvhé^;^

n

4. The list box lists all fields (columns) in the table. In each column, you can choose to allow or deny the following options:

■ SELECT, meaning this column can or cannot be used in a SELECT-FROM-WHERE statement. If you deny SELECT access, the column will not show in a selection.

■ UPDATE, meaning the value of this column can or cannot be changed in an UPDATE-SET statement. If you deny UPDATE access, the column value will not be changed, even if the UPDATE tries to do so.

The second way to set permissions is through the view of the object.This shows the object and all roles associated to the database, and from here you can set the same permissions. Let's see how this works:

1. Open SQL Server Enterprise Manager.

2. Expand until all databases are shown.

3. Expand InterchangeBTM.

4. Select Tables.

5. Select a table in the right-hand list box; for this example, select adm_Group.

6. Right-click it, select Properties, and the Table Properties dialog appears (Figure 8.26).

Was this article helpful?

0 0

Post a comment