Dictionary-Related SQL Statements

SQL statements used to grant and revoke rights to data dictionary objects are described in this section.

Creating a Data Dictionary

You create a data dictionary using the CREATE DATABASE statement. At a minimum, you must supply the name of the data dictionary you are creating. For example, the following statement creates a data dictionary named NEW.ADD:

CREATE DATABASE "NEW.ADD"

This statement created the data dictionary in the directory associated with the connection over which this query is executed. If you are connected to a data dictionary, this will be the data dictionary’s default directory. If you are using a free table connection, it will be the directory associated with those free tables.

To specify where the data dictionary is created, qualify the data dictionary name with a valid path. For example, if you want to create NEW.ADD on the DATASERV computer in the directory named AppData in the C$ share, your CREATE DATABASE statement might look something like the following:

CREATE DATABASE "\\DATASERV\C$\AppData\NEW.ADD"

CREATE DATABASE supports three options when creating a new database. You can assign a password to the ADSSYS user name, you can attach a description to the data dictionary, and you can choose to encrypt the data dictionary. The following statement demonstrates the use of these three options:

CREATE DATABASE "\\DATASERV\C$\AppData\NEW.ADD"   PASSWORD 'password'   DESCRIPTION 'A data dictionary created using SQL'   ENCRYPT True

There is no SQL DROP statement for destroying a data dictionary. If you need to destroy a data dictionary, you need to use your client application development environment’s capabilities to delete the data dictionary’s .ADD, .AI, and .AM files. However, if there are tables bound to that data dictionary that you want to keep, be sure to release those tables prior to deleting the data dictionary’s files.

Note

You do not need an administrative connection to create a data dictionary.

Granting Rights

If you configure your data dictionary to check user rights, any new objects that you add to your data dictionary must have rights granted to the groups and users who need to work with those objects. With Advantage SQL, you grant rights to objects using the SQL GRANT statement. You must be connected to your data dictionary through the administrative account in order to grant rights.

You follow the GRANT keyword with the type of rights you want to convey. The type of rights is followed by the ON keyword, which you follow with the object to which you are granting the rights. You complete the GRANT statement using the TO keyword followed by the name of the user or group to whom you are granting the rights.

Note

All rights can be granted to either users or groups, with the exception of INHERIT. INHERIT can only be granted to a user.

For example, if you have a table named TEMP and you want to convey SELECT and INSERT rights to the ALL group, but not UPDATE and DELETE rights, you could execute the following SQL script:

GRANT INSERT ON TEMP TO [ALL]; GRANT SELECT ON TEMP TO [ALL]

Note

The group named ALL is enclosed in delimiters since ALL is an Advantage SQL reserved keyword.

Similarly, if you have a table named DEMO1 in your data dictionary, and you want to grant all rights to the adsuser user, use the following statement:

GRANT ALL ON DEMO1 TO adsuser

Granting rights to a table explicitly grants those same rights to every field in the table as well.

Rights can be granted for tables, table columns, stored procedures, views, and data dictionary links. Which rights can be granted depends on the object to which rights are being conveyed. Table 11-10 lists the type of rights and the objects to which those rights can be granted.

Table 11-10: Object Rights

Type	Applies To
ALL	Any object
ACCESS	Data Dictionary Link
DELETE	Table or View
EXECUTE	Stored Procedure
INHERIT	Table, Stored Procedure, View, or Link (users only)
INSERT	Table, Table Column, or View
SELECT	Table, Table Column, or View
UPDATE	Table, Table Column, or View

As you can see in Table 11-10, you can grant INSERT, SELECT, and UPDATE rights to individual columns of a table. When you do so, the syntax of the GRANT statement is slightly different. You follow the INSERT, SELECT, or UPDATE keyword with the name of the column to which you are granting rights in parentheses. For example, the following statement grants SELECT rights to the Full Name column in the DEMO1 table (although this field already has SELECT rights since ALL rights were granted to DEMO1 in the previous statement):

GRANT SELECT("Full Name") ON DEMO1 TO adsuser

As you learned in Chapter 4, when no field-level rights have been explicitly granted, your groups’ and users’ rights to the individual fields of a table are the same as the rights conveyed to the table itself. This situation changes once you explicitly grant rights to one or more fields. Specifically, if you grant explicit access rights to one or more fields of a table, users and groups can access only those fields of the table for which rights have been explicitly granted. As you learned earlier in this section, when you execute a GRANT SQL statement for a table, those same rights are explicitly granted to every field in that table. By comparison, granting rights to a table in the Advantage Data Architect has no effect whatsoever on individual field-level rights.

Tip

As mentioned in Chapter 4, if you have one or more fields in a table that you do not want users or groups to have access to, you can provide access to a view that does not include those fields you want to protect.

Revoking Rights

The syntax of the REVOKE SQL statement is nearly identical to the GRANT statement. The only differences are that you use the REVOKE keyword in place of GRANT, and FROM in place of TO. All other aspects of this statement are identical, including the use and applicability of the rights and object to which they apply that appear in Table 11-10.

For example, to revoke the adsuser’s SELECT rights from the Full Name field of the DEMO1 table, you can use the following statement:

REVOKE SELECT("Full Name") ON DEMO1 FROM adsuser

Note

If the SELECT rights to the Full Name field was the only field-level rights granted to the DEMO1 table, revoking those rights would restore to adsuser the access rights granted to the DEMO1 table, based on adsuser’s table-level access rights to DEMO1, including those inherited from the ALL group to which adsuser belongs. Field-level rights only apply when rights have been explicitly granted to one or more of the table’s fields.

To remove access rights to the DEMO1 table from the adsuser user altogether, you use the following statement:

REVOKE ALL ON DEMO1 FROM adsuser