Advantage Database Server: The Official Guide

While encrypting your database tables prevents someone from viewing the data in your table files directly, it does nothing to prevent those tables from being accessed by client applications designed to work with a data dictionary. If you want to control who can access your tables, you have to implement security for your data dictionary.

You can take a number of specific steps to ensure that your data is accessed only by authorized users. These include requiring users to log in prior to providing them with access to your database tables, as well as setting up the user names and passwords that users use to log in. These topics are covered in the following sections.

Controlling User Access

Unless you take specific steps, any user who can connect to your data dictionary can access the data dictionary’s database tables. Encrypting the tables does not help, since the data dictionary provides ADS with the password for the bound tables.

You can easily demonstrate this using the following steps:

1. Close the Advantage Database Manager.

2. Select Database | Open Database from the Advantage Data Architect main menu.

3. With the Alias radio button selected, and Alias set to DemoDD, check the Anonymous Login checkbox, and then click Open. The Advantage Data Architect responds by opening the data dictionary in the Advantage Database Manager. You can now view and edit the tables in this data dictionary if you like.

4. Close the Advantage Database Manager.

   Note

   So long as anonymous login is permitted by the data dictionary, a user can log in anonymously and view and edit data. However, a user logged in anonymously cannot change data dictionary definitions, such as table structures, index orders, and data dictionary properties.

If you want to control access to a data dictionary, you must configure the data dictionary to require a valid user name and password before access is granted. Use the following steps to restrict access to the data dictionary:

1. Open the Advantage Database Manager again, this time with the Anonymous Login checkbox unchecked. When prompted, log in using the data dictionary administrative user name ADSSYS and the password password.

2. Right-click the data dictionary node in the Advantage Database Manager tree view and select Properties. As you may recall from earlier in this chapter, the data dictionary node is the root node in the Advantage Database Manager tree view. In our example, DemoDD, the alias for DemoDictionary, is displayed as the root node. The Advantage Database Manager displays the data dictionary properties dialog box.

3. In the User Access section, check the Logins Required checkbox. When Logins Required is checked, ADS will not accept a connection to the data dictionary unless the user has provided a valid user name and password.

4. The User Access section also includes a Check User Rights checkbox. If this checkbox is not checked, a valid user name and password will provide the user with unlimited access to all objects in the data dictionary. If this checkbox is checked, the user will be limited by whatever user rights are expressly granted for their user name. For the time being, do not check the Check User Rights checkbox. Assigning user rights is discussed in the following section.

5. Click OK to save the new data dictionary properties.

   Note

   When the Check User Rights option is not checked, then any valid user can work with any of the tables, views, and stored procedures defined in the data dictionary. However, only the data dictionary administrator can change data dictionary definitions.

It is now impossible to log in anonymously. You can test this by closing the Advantage Database Manager, and then attempting an anonymous login, as demonstrated earlier in this section. This time you will get an ADS 7078 error. Because ADS could not authenticate you, it denied you access to the data dictionary.

Defining Users and Groups

An administrative user is created when you first create a data dictionary. The user name for the user is ADSSYS, and this user is specifically designed to manage the configuration of the data dictionary.

In most cases, you should not use this user name for regular data access. The ADSSYS connection consumes additional resources on your ADS server and increases the amount of network traffic between server and clients.

In addition, transaction processing is not allowed by ADS using the ADSSYS connection.

The only time that your client application should connect to a data dictionary using this administrative user account is when it needs to make runtime changes to your data dictionary. For example, you can build functionality into your client applications that permits users to be added to a data dictionary, passwords to be changed, access rights to be added or revoked, and so on. These capabilities are only available, however, when the connection to the data dictionary is made using the ADSSYS user name and associated password. As a result, your client application would likely offer these capabilities to select users, and establish an administrative connection only while administrative activities are being performed.

For all other, non-administrative access to a data dictionary, you should establish one or more “regular” users. (Regular user, as the term is used here, is any user other than ADSSYS.) In the simplest case, you create a single regular user and provide that user name and password (the password could even be blank) for all of your users. If there is going to be only one user, and that user is you, this approach is usually more than adequate.

If your client application is used by more than one user, you should consider having more than one non-administrative user. Having more than one user permits you to exert greater control over access to your database.

For example, imagine that you are writing a database application that will be used by several employees in your company. If one of those employees is subsequently terminated, you probably want to revoke their access to the database, particularly if the database contains sensitive information. If each employee has their own user name and password, you can revoke the former employee’s access by removing their associated user from the data dictionary.

Granted, if security is not an issue, you may conclude that the benefits of multiple passwords are not worth the additional administration. Nonetheless, if you do opt for multiple user names, you should also consider making use of groups. Groups are discussed later in this section.

Adding Users

Adding users from the Advantage Database Manager is quite simple, as shown in the following steps:

1. Open the DemoDictionary data dictionary in the Advantage Database Manager if it is not already open.

2. Right-click the USERS node in the Advantage Database Manager tree view and select Add User. The Advantage Database Manager displays the Create New User dialog box:

3. Set User Name to adsuser.

4. Set both Password and Verify Password to password, and then click Continue. The User Description dialog box is displayed:

5. Set User Description to Basic user name for data access. Click Continue to display the Set User Rights dialog box:

6. You use the Set User Rights dialog box to grant specific access privileges to the user. These privileges are only meaningful, however, if you have checked the Check User Rights checkbox of the data dictionary’s properties dialog box. Enabling user rights checking is discussed later in this section.

7. The Set User Rights dialog box is also where you enable Internet access for the user. If you want to grant this user access to the data dictionary and the database over the Internet, check the Enabled checkbox in the Internet Access area. This right is only meaningful if you have enabled the data dictionary and database for Internet Access from the data dictionary’s properties dialog box, and have also configured ADS to provide Internet access by specifying an Internet IP port through the Advantage Configuration Utility. Do not enable Internet access for this user at this time.

8. Click Finish to finalize the adsuser’s properties and to add this user to the USERS node in the Advantage Database Manager tree view.

Any number of end users can use the same user name and password at the same time, which is why you can get by with a single non-administrative user if you want. But if you want more than one user, you can repeat the preceding steps for each of the additional users that you want to add.

In addition to providing you with greater control over your application’s security, having more than one user permits you to leverage another feature of data dictionaries. This feature is user rights, and it is discussed in the following section.

Checking User Rights

User rights refer to access permissions associated with the tables, fields, views, stored procedures, and database links associated with a data dictionary. For some objects, such as stored procedures, rights refer to whether or not a connection for that particular user has the right to execute the stored procedure. For other objects, such as tables, these rights are more involved. For example, you can permit one user to view a particular table, but that is all. You might provide another user with complete access to that table, permitting them to view and edit the data.

The rights for tables can be extended down to the field level. For example, for the user who can only read a particular table, you can further define that there are one or more fields that this user is not even allowed to see. For example, you might let a user view the records of the employee table, but deny them the ability to view the Salary field.

There are two parts to controlling user rights. The first is to configure the data dictionary to check a user’s rights prior to providing access to a data dictionary’s objects, such as a table or a stored procedure. The second part involves explicitly granting rights to those resources. Use the following steps to configure the DemoDictionary data dictionary to check user rights. Granting rights is discussed later in this chapter.

1. Right-click the DemoDictionary (alias DemoDD) node in the Advantage Database Manager tree view and select Properties. The data dictionary’s properties dialog box, similar to that shown in Figure 4-4, is displayed. (The Logins Required and the Enabled checkboxes were enabled in previous steps.)

2. Check the Check User Rights checkbox and then click OK.

If you are considering limiting a user’s access to one or more resources, you should consider whether you want to employ groups.

Understanding Groups

A group is like a user template for access rights. You begin by creating a group and defining what access rights members of that group require. You can then assign group membership to individual users. By default, a user inherits the rights associated with the groups they belong to.

Groups are especially valuable when you have multiple users and want to easily administer their rights. For example, you can create one group and explicitly grant that group the rights that all group members will need to the data dictionary’s resources. You can then make each of your users a member of that group.

If at some later date you need to change the rights of all users in the particular group, you simply update the group’s rights. The members of that group automatically inherit those changes (unless you have overridden this behavior for a particular user; overriding inherited rights is discussed later in this chapter). By comparison, if you do not use groups, and explicitly set rights for each individual user, any changes that you make to user privileges will need to be applied to each user’s rights separately.

The benefits of using groups increase when you have two or more types of users of your database. For example, some applications have three levels of users: those who can only view data, those who can view and add data, and those who have full access (including delete privileges).

To accommodate this scenario, you begin by creating three groups, with each group being granted the rights associated with the type of user who will be a member. Then, for each user you add to the data dictionary, you assign them to whichever group they belong.

Actually, you can get pretty fancy about how you use groups. For example, a given user can belong to two or more groups. That user’s rights are the sum of the rights conveyed to the groups to which they belong.

Using Access Rights and Groups

As you probably concluded from the preceding discussions concerning users, groups, and access rights, how you set up the security for your data dictionary depends largely on your application’s needs. In the case of the data dictionary created in this chapter, a simple security model will be employed. You will create two groups, one with readonly access to the data dictionary’s tables, and one with complete access. You will then assign adsuser to the group that grants complete access.

Use the following steps to configure security for this data dictionary:

1. Right-click the GROUPS node in the Advantage Database Manager tree view and select Add Group. The Group dialog box, shown in Figure 4-6, is displayed.

   Figure 4-6: The Group dialog box allows you to define access rights for a group as well as to display existing properties for the group.

2. Set Name to ReadOnly and Description to Sample group with readonly permissions. Click Table Rights to view the Table Permissions dialog box.

3. Click the Set All Read button to check the Read checkbox associated with every table in the data dictionary. Leave the Insert, Update, and Delete checkboxes unchecked:

   The settings on the Table Permissions dialog box apply to entire tables. If you want to configure field-level permissions, select a table name from the Table Permissions dialog box, and click the Field Permissions button. For example, if you select the ITEMS table in the Table Permissions dialog box and click Field Permissions, you will see the Field Permissions dialog box. This dialog box permits you to set the Read, Insert, and Update permissions for the fields in the ITEMS table:

   As long as no checkboxes are checked on the Field Permissions dialog box, the group members can have any access to the table’s fields granted by the table’s rights. If at least one checkbox is checked, that group’s members will have only those permissions explicitly granted by the field-level permissions. Click OK to close the Field Permissions dialog box and return to the Table Permissions dialog box.

   Note

   This field-level permissions behavior differs from that of the SQL GRANT statement, which is described in Chapter 11.

4. Click OK to close the Table Permissions dialog box and return to the Group dialog box. From here you can add other permissions, but in our example, there are currently no other objects for which permissions can be granted. You can also click the Group Members button in order to add a user to this group, but we are not going to add a user to the ReadOnly group at this time. Click Create to create the group.

5. Right-click the GROUPS node in the Advantage Database Manager tree view and select Add Group once more.

6. Set Name to ALL and Description to Sample group with rights to everything.

7. Click the Table Rights button to display the Table Permissions dialog box. Click the Set All Read, Set All Insert, Set All Update, and Set All Delete buttons to enable all rights to the tables of the data dictionary. Click OK when done to return to the Group dialog box.

8. Click Create to create this group.

Once you create a group, you add users to the group. The following steps add the adsuser to the ALL group:

1. If the USERS node is not already expanded, click the + sign next to it to expand it.

2. Click the adsuser user node to display this user’s properties, as shown in Figure 4-7.

   Figure 4-7: The User dialog box displays the properties for the user selected in the Advantage Database Manager tree view.

3. Click the Group Membership button from the User dialog box. The User Group List displays all defined groups. Check the ALL group to add adsuser to the ALL group. Click Save, and then click Close to close the User Group List dialog box.

4. Click the Table Rights button on the User dialog box to view the user’s Table Rights dialog box, as shown in Figure 4-8. Notice that the last column of checkboxes is labeled “Inherit,” and each of the checkboxes in this column is checked by default. When Inherit is checked, the rights of this user for the associated table are inherited from the sum of the groups the user is a member of.

   Figure 4-8: Use the Table Rights dialog box to customize a user’s rights as well as to view effective permissions.

   If you want to override this behavior, assigning table rights explicitly rather than using the inherited rights, uncheck the Inherit checkbox for each table whose rights you want to define explicitly. Note that you can view the effective rights by clicking the Effective Permissions tab of this dialog box. The Effective Permissions page is a readonly view of the current permissions, taking into account group membership and inherited rights.

5. Do not make any changes to this user’s Table Rights dialog box. Click Cancel once to close the Table Rights dialog box. Click Close to close the User dialog box.

Changing Objects and Permissions

Creating users, groups, and enabling user rights checking is often one of the final steps that you will take in the testing and deployment of a database application. The creation of users and groups, and the enabling of rights checking were performed in this chapter since these issues are closely related to the creation and control of a data dictionary. The problem is that you have not completed the construction of this data dictionary. In later chapters of this book, you will add new objects, such as stored procedures and views, to this data dictionary.

Each time you add a new object (stored procedure, view, or link), you will also need to update group and/or user access rights in order to permit access to the added object. By comparison, if you had waited to enable access rights until after all of the data dictionary’s objects were created, you would have had to configure access rights only once.

To update group or user access rights for a newly added object, you display the Advantage Database Manager and select the desired group or user in the tree view. If you select a group, you will see a Group dialog box similar to the one shown in Figure 4-6. If you select a user, you will see a User dialog box similar to the one shown in Figure 4-7. The Procedure Rights, View Rights, and Link Rights options on these dialog boxes allow you to define group or user access rights for stored procedures, views, and links, respectively.

Setting rights for stored procedures, views, and links is discussed later in this book when these objects are discussed.

How Field-Level Permissions Are Enforced

We did not assign field-level permissions to any of the tables in this data dictionary. In fact, some developers avoid using field-level permissions altogether, and use alternative mechanisms to restrict access to certain fields. Consider the example given earlier in this chapter in which a user can view the EMPLOYEE table, but is not permitted to view the Salary field of the EMPLOYEE table. Instead of using field-level permissions, the user could be denied read access to the EMPLOYEE table, but be granted read access to a view that includes all fields other than the Salary field from the EMPLOYEE table. In the end, the results are similar. That user cannot access the Salary field.

But what happens when you do employ field permissions to limit a user’s access to a field? Specifically, what would happen if you try to show a user a record that contains a field whose field-level permissions prohibit the field from being read?

The answer depends on the permissions level associated with the table to which the field belongs. You can view and change a table’s permission level by right- clicking the node associated with the table under the TABLES node, and selecting Properties from the displayed menu. Figure 4-9 shows the table properties dialog box for the EMPLOYEE table.

Figure 4-9: The table properties dialog box for the EMPLOYEE table

There are three possible values for the Permission Level table property. These are Level 1, Level 2, and Level 3, where Level 1 is the least restrictive and Level 3 is the most.

If you set a table’s Permission Level property to Level 1, reading a field that does not have read permissions does not cause an error, it simply returns a null value. If you try to display that data to a user who does not have read permissions, the field appears empty. However, you can use the field in a SQL WHERE clause, a filter, or a scope, which means that a user could possibly infer the value of the field based on the results of one of these operations.

The Level 2 Permission Level also returns a null value if you attempt to read a field that does not have read permissions. However, attempting to use that field in a SQL WHERE clause, a scope, or a filter will generate an error. This is the default permission level.

The Level 3 Permission Level is very restrictive. Tables with this permission level can only be accessed using SQL. Furthermore, any field without read rights cannot appear in a WHERE, HAVING, or ORDER BY clause. Otherwise, the SQL statement will produce an error.