Grant and revoke privileges in applications

One task related to data definition is performed repeatedly: granting and revoking privileges. Because privileges must be granted and revoked frequently, possibly by users who are not skilled in SQL, one strategy is to package the GRANT and REVOKE statements in programs to give them a simpler, more convenient user interface.

The GRANT and REVOKE statements are especially good candidates for dynamic SQL. Each statement takes the following parameters:

A list of one or more privileges
A table name
The name of a user

You probably need to supply at least some of these values based on program input (from the user, command-line parameters, or a file) but none can be supplied in the form of a host variable. The syntax of these statements does not allow host variables at any point.

An alternative is to assemble the parts of a statement into a character string and to prepare and execute the assembled statement. Program input can be incorporated into the prepared statement as characters.

The following function assembles a GRANT statement from parameters, and then prepares and executes it:

char priv_to_grant[100];
char table_name[20];
char user_id[20];

table_grant(priv_to_grant, table_name, user_id)
char *priv_to_grant;
char *table_name;
char *user_id;
{
   EXEC SQL BEGIN DECLARE SECTION;
   char grant_stmt[200];
   EXEC SQL END DECLARE SECTION;

   sprintf(grant_stmt, " GRANT %s ON %s TO %s",
      priv_to_grant, table_name, user_id);
   PREPARE the_grant FROM :grant_stmt;
   if(SQLCODE == 0)
      EXEC SQL EXECUTE the_grant;
   else
      printf("Sorry, got error # %d attempting %s", 
         SQLCODE, grant_stmt);

   EXEC SQL FREE the_grant;
}

The opening statement of the function that the following example shows specifies its name and its three parameters. The three parameters specify the privileges to grant, the name of the table on which to grant privileges, and the ID of the user to receive them.

table_grant(priv_to_grant, table_name, user_id)
char *priv_to_grant;
char *table_name;
char *user_id;

The function uses the statements in the following example to define a local variable, grant_stmt, which is used to assemble and hold the GRANT statement:

EXEC SQL BEGIN DECLARE SECTION;
   char grant_stmt[200];
EXEC SQL END DECLARE SECTION;

As the following example illustrates, the GRANT statement is created by concatenating the constant parts of the statement and the function parameters:

sprintf(grant_stmt, " GRANT %s ON %s TO %s",priv_to_grant, 
   table_name, user_id);

This statement concatenates the following six character strings:

'GRANT'
The parameter that specifies the privileges to be granted
'ON'
The parameter that specifies the table name
'TO'
The parameter that specifies the user

The result is a complete GRANT statement composed partly of program input. The PREPARE statement passes the assembled statement text to the database server for parsing.

If the database server returns an error code in SQLCODE following the PREPARE statement, the function displays an error message. If the database server approves the form of the statement, it sets a zero return code. This action does not guarantee that the statement is executed properly; it means only that the statement has correct syntax. It might refer to a nonexistent table or contain many other kinds of errors that can be detected only during execution. The following portion of the example checks that the_grant was prepared successfully before executing it:

if(SQLCODE == 0)
   EXEC SQL EXECUTE the_grant;
else
   printf("Sorry, got error # %d attempting %s", SQLCODE, grant_stmt);

If the preparation is successful, SQLCODE = = 0, the next step executes the prepared statement.