Assigning views of a synonym table to individual users

To enable users to have synonyms unique to their needs and still keep table maintenance at an acceptable level, consider creating several views of one synonym table, and assigning the views to individual users or groups of users. There are three types of views you can create.

Synonyms for public or private use

If you have few synonyms that are used by individuals, consider creating and assigning a view that flags each synonym for either public use (by all users) or private use (by individual users):

1. Add an AUTHID column to the synonym table when you create the table. A null value in the AUTHID column indicates a public synonym; a user ID in the AUTHID column indicates a private synonym. You can have many entries for the same synonym, each assigned to a different user.
2. Use a query similar to that in Figure 145 to create a view on the synonym table. This query allows a user (indicated by userid in the figure) to use all public synonyms in the table and any synonyms assigned privately to his or her SQL authorization ID.
   Figure 145. Creating a view that controls individual and public use of synonyms

   CREATE VIEW SYNVIEW (VERB,OBJECT,SYNONYM__DEFINITION)
     AS SELECT VERB, OBJECT, SYNONYM__DEFINITION
        FROM COMMAND__SYNONYMS
        WHERE AUTHID='userid' OR AUTHID IS NULL

Synonyms for public or group use

If you support a large group of end users, consider creating and assigning a view that flags certain synonyms to be used by certain groups of users.

The synonym table used to create the view contains a single row for each synonym that belongs to a user group, and a single row for each public synonym. AUTHID is either null or has a value that uniquely identifies the user group.

1. Add an AUTHID column to the synonym table if it does not have one.
2. Use a query similar to the one in Figure 146 to create the view on the synonym table. The example in the figure shows a view created for a group of users that have a common user ID, DEPTD02. All users in the DEPTD02 group can use all public synonyms in the table and any synonyms assigned specifically to the group.
   Figure 146. Creating a view that controls group and public use of synonyms

   CREATE VIEW GROUPVIEW (VERB,OBJECT,SYNONYM__DEFINITION)
     AS SELECT VERB, OBJECT, SYNONYM__DEFINITION
        FROM COMMAND__SYNONYMS
        WHERE AUTHID='DEPTD02' OR AUTHID IS NULL

Synonyms paired with an authorization table

Consider creating a separate table that holds in one column SQL authorization IDs and in the other column the values of a key. If the keyed value for a particular SQL authorization ID matches a keyed value in a row of the command synonym table, the synonym described in that row is available to the user.

Use a query similar to the one in Figure 147 to implement this method of maintaining command synonyms. The query creates a view called KEYVIEW on the table COMMAND__SYNONYMS, incorporating in the view only the synonyms that have keyed matches between COMMAND__SYNONYMS and the auxiliary table, KEYTABLE.

CREATE VIEW KEYVIEW (VERB,OBJECT,SYNONYM__DEFINITION)
  AS SELECT VERB, OBJECT, SYNONYM__DEFINITION
     FROM COMMAND__SYNONYMS
     WHERE AUTHID IS NULL OR AUTHID IN
        (SELECT KEYS FROM KEYTABLE WHERE USER=userid)