SAS/ACCESS Software for Relational Databases: Reference

Information for the Database Administrator

This section explains how SAS/ACCESS works so you can decide how to administer its use at your site.

How SAS/ACCESS Works

When you use the ACCESS procedure to create an access descriptor, the interface issues a SELECT statement to the data dictionary tables in your DBMS. The ACCESS procedure then issues the equivalent of a DESCRIBE statement to gather information about the columns in the specified table. The access descriptor's information about the table and its columns is then copied into the view descriptor when it is created. Therefore, it is not necessary for the SAS System to call the DBMS when it creates a view descriptor.

When you use the DBLOAD procedure to create a table, the procedure issues dynamic SQL statements to create the table and insert data from a SAS data file, DATA step view, PROC SQL view, or view descriptor into the DBMS table.

PROC SQL Pass-Through statements and the CONNECTION TO component are passed directly to the DBMS as soon as they are submitted. They are passed by using Pass-Through functions in the SAS/ACCESS interface view engine.

When you use the DATA step or any SAS procedure with a view descriptor, the SAS/ACCESS interface view engine issues SQL calls to the DBMS.

The following sections explain the calls that the SAS System makes to the DBMS when you use the ACCESS and DBLOAD procedures or the SQL Procedure Pass-Through Facility. The connection between the SAS System and the DBMS is illustrated in How the SAS System Connects to the DBMS.

Note: Currently, the SAS/ACCESS Interface to Informix does not support the ACCESS and DBLOAD procedures. [cautionend]

Note: Currently, the SAS/ACCESS Interface to Oracle Rdb does not support the SAS/ACCESS LIBNAME statement. [cautionend]

How the SAS System Connects to the DBMS

[IMAGE]

Using the ACCESS Procedure to Create an Access Descriptor

When you create an access descriptor, the SAS/ACCESS interface view engine requests the DBMS to execute an SQL SELECT statement dynamically by using DBMS-specific calling routines or interface software. The following steps are completed:

When you supply the connection information to PROC ACCESS, the SAS/ACCESS interface calls the DBMS to connect to the database.
The SAS System constructs a SELECT * FROM table-name statement and passes it to the DBMS to retrieve information about the table from the DBMS data dictionary. This SELECT statement is based on the information you supplied to PROC ACCESS. Using a SELECT statement enables the SAS System to determine whether the table exists and can be accessed.
The SAS/ACCESS interface calls the DBMS to get table description information, such as the column names, data types (including width, precision, and scale), and whether the columns accept null values.
The SAS System closes the connection with the DBMS.

Reading Data

When you use a view descriptor, DATA step, or procedure to read DBMS data, the SAS/ACCESS interface view engine requests the DBMS to execute an SQL SELECT statement. The interface view engine follows these steps:

Using the connection information provided during creation of the access descriptor, the SAS/ACCESS interface calls the DBMS to connect to the database.
The SAS System constructs a SELECT statement that is based on the information stored in the view descriptor (table name and selected columns and their characteristics) and passes this information to the DBMS.
The SAS System fetches the data from the DBMS table and passes it back to the SAS procedures as if it were observations in a SAS data set.
The SAS System closes the connection with the DBMS.

For example, if you execute the following SAS program using a view descriptor, the previous steps are executed once for the PRINT procedure, then a second time for the GCHART procedure. (The data used for the two procedures is not necessarily the same because the table might have been updated by another user between procedure executions.)

proc print data=vlib.allemp;
run;

proc gchart data=vlib.allemp;
   vbar jobcode;
run;

Updating Data

You use a view descriptor, DATA step, or procedure to update DBMS data in much the same way as when reading data. In addition, the following steps might occur:

Using the connection information provided during creation of the access descriptor, the SAS/ACCESS interface calls the DBMS to connect to the database.
When rows are added to a table, the SAS System constructs an SQL INSERT statement and passes it to the DBMS. When you reference a view descriptor, you can use the ADD command in FSEDIT and FSVIEW, the APPEND procedure, or an INSERT statement in PROC SQL to add data to a DBMS table. (Or, you can use the SQL Procedure Pass-Through Facility's EXECUTE statement to add, delete, or modify DBMS data directly. Literal values must be used when inserting data by using the Pass-Through Facility.)
When rows are deleted from a DBMS table, the SAS System constructs an SQL DELETE statement and passes it to the DBMS. (When you reference a view descriptor, you can use the DELETE command in FSEDIT and FSVIEW or a DELETE statement in PROC SQL to delete rows from a DBMS table.)
When data in the rows is modified, the SAS System constructs an SQL UPDATE statement and passes it to the DBMS. (When you reference a view descriptor, you can use FSEDIT, the MODIFY command in FSVIEW, or an INSERT statement in PROC SQL to update data in a DBMS table. You can also reference a view descriptor in the DATA step's UPDATE, MODIFY, and REPLACE statements.)
The SAS System closes the connection with the DBMS.

Using the DBLOAD Procedure to Create DBMS Tables

You create DBMS tables from SAS data sets by using the DBLOAD procedure. The SAS/ACCESS interface view engine follows these steps:

When you supply the connection information to PROC DBLOAD, the SAS/ACCESS interface calls the DBMS to connect to the database.
The SAS System uses information that is provided by the DBLOAD procedure to construct a SELECT * FROM table-name statement, and it passes the information to the DBMS to determine if the table already exists. PROC DBLOAD continues only if a table with that name does not exist, unless you use the DBLOAD APPEND option.
The SAS System uses information that is provided by the DBLOAD procedure to construct an SQL CREATE TABLE statement and passes it to the DBMS.
The SAS System constructs an SQL INSERT statement for the current observation and passes it to the DBMS. New INSERT statements are constructed and then executed repeatedly until all of the observations from the input SAS data set are passed to the DBMS. Some DBMSs have a bulkcopy capability that allows a group of observations to be inserted at once. See your DBMS documentation to determine if your DBMS has this capability.
Additional nonquery SQL statements specified in the DBLOAD procedure are executed as submitted by the user. The DBMS returns an error message if a statement does not execute successfully.
The SAS System closes the connection with the DBMS.

Using the SQL Procedure Pass-Through Facility to Read and Update DBMS Data

To read and update data with the SQL Procedure Pass-Through Facility, the SAS/ACCESS interface view engine passes SQL statements directly to the DBMS for processing. Here are the steps:

You pass a PROC SQL CONNECT statement to the SAS/ACCESS interface view engine to establish a connection with the specified database.
You use a CONNECTION TO component in a PROC SQL SELECT statement to read data from a DBMS table or view. The SELECT statement (that is, PROC SQL query) can be stored as a PROC SQL view.
You use a PROC SQL EXECUTE statement to pass any dynamic, non-query SQL statements (such as INSERT, DELETE, and UPDATE) to a database. INSERT statements must contain literal values.
In the EXECUTE statement and CONNECTION TO component, all statements are passed to the DBMS exactly as you have typed and submitted them.
You terminate the connection with the DISCONNECT statement.

Ensuring Data Security

The SAS System preserves the data security provided by your DBMS and the operating system. The DBA controls who has privileges to access or update DBMS objects. The DBA also controls who can create objects, and creators of the objects control who can access the objects. A user cannot use DBMS facilities through the SAS/ACCESS LIBNAME statement, the SAS/ACCESS interface view engine, the ACCESS procedure, or the DBLOAD procedure, unless the user has the appropriate DBMS privileges or authority on those objects. For example, only users who have object privileges for a DBMS table can create an access descriptor on that table.

To secure data from accidental update or deletion, you can take precautionary measures in both your DBMS and the SAS System.

On the DBMS, give users only the privileges they must have. Privileges are granted on whole tables or views. A user must explicitly be granted privileges on the DBMS tables or views underlying a view to use that view.

You can grant privileges on the DBMS side by using the SQL Procedure Pass-Through Facility to submit an SQL GRANT statement, or issue a GRANT statement from the DBLOAD procedure SQL statement.

Using Triggers to Enhance Security

If your DBMS supports triggers, you can use them to enforce security authorizations or business-specific security considerations. When and how triggers are executed is determined by when the SQL statement is executed and how often the trigger executes. Triggers can be executed before an SQL statement is executed, after an ORACLE SQL statement is executed, or can be executed for each row of an SQL statement. Also, triggers can be defined for DELETE, INSERT, and UPDATE statement execution.

Enabling triggers can provide more specific security for delete, insert, and update operations. The SAS/ACCESS interface view engine abides by all constraints and actions that are specified by a trigger. For more information, see your DBMS documentation.

SAS System Security

To secure DBMS data from accidental update or deletion, you can follow these steps on the SAS System side of the interface:

Create access descriptors yourself and drop columns from the display that contain sensitive data by using the DROP statement.
Give users read-only access to the SAS data library in which you store the access descriptors.
Create all view descriptors yourself and control their use.
Assign SAS System passwords to access descriptors and view descriptors. For more information, see ACCESS Procedure Syntax .

Note: On CA-OpenIngres, databases are created as public databases, unless the -p flag is used with the CREATEDB command to create a private database. When creating access descriptors, the interface view engine issues PREPARE and DESCRIBE statements to gather information about the table. The DESCRIBE statement uses CA-OpenIngres system catalogs to retrieve the information, and CA-OpenIngres does not check permissions. Therefore, users can create access descriptors and view descriptors on tables on which they have no privileges. This is not caused by any security circumvention by the SAS System; this is the way CA-OpenIngres operates. However, users cannot use the view descriptors to view or extract the data. [cautionend]

In the SAS/ACCESS LIBNAME statement, you can specify the DBPROMPT= option to defer providing connection information until connection time. When you use DBPROMPT=, you do not need to save connection information in your code.

SAS provides the ability to create SQL views that can be protected from unauthorized access by applying passwords. Also, you can use the DBMS security provided by the DBMS to restrict table access by user ID. See your DBMS documentation for more details.

When you create an access descriptor, the connection information that you provide is stored in the access descriptor and in any view descriptors based on that access descriptor. The password is stored in an encrypted form. When these descriptors are accessed, the connection information that was stored is also used to access the DBMS table or view. To ensure data security, you might want to change the protection on the descriptors to prevent others from seeing the connection information stored in the descriptors.

An alternative is to leave the user name, password, and other connection arguments blank when you create descriptors. In this case, access to the DBMS is denied unless the correct user and password information is stored in a local environment variable. See your DBMS chapter to determine if this alternative is supported.

You can also use the ACCESS procedure to create access descriptors in which you specify that particular columns be dropped from the descriptor. Columns that are dropped from an access descriptor do not affect the DBMS table and can be reselected for later use.

Note: SAS/ACCESS does not override your DBMS's security. [cautionend]

Chapter Contents
Previous
Next
Top of Page