|SAS/ACCESS Software for Relational Databases: Reference|
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.
Note: Currently, the SAS/ACCESS Interface to Oracle Rdb does not support the SAS/ACCESS
How the SAS System Connects to the DBMS
|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 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:
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;
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 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:
|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:
|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.
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.
To secure DBMS data from accidental update or deletion, you can follow these steps on the SAS System side of the interface:
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.
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.
Top of Page
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.