Data Integrity and Security

SAS Security

SAS preserves the data security provided by your DBMS and operating system; SAS/ACCESS does not override the security of your DBMS. To secure DBMS data from accidental update or deletion, you can take steps on the SAS side of the interface such as the following:

specifying the SAS/ACCESS LIBNAME option DBPROMPT= to avoid saving connection information in your code
creating SQL views and protecting them from unauthorized access by applying passwords.

These and other approaches are discussed in detail in the following sections.

Assigning SAS Passwords

By using SAS passwords, you can protect SQL views, SAS data sets, and descriptor files from unauthorized access. The following table summarizes the levels of protection that SAS passwords provide. Note that you can assign multiple levels of protection.

Password Protection Levels and Their Effects
File Type	READ=	WRITE=	ALTER=
PROC SQL view of DBMS data	Protects the underlying data from being read or updated through the view; does not protect against replacement of the view	Protects the underlying data from being updated through the view; does not protect against replacement of the view	Protects the view from being modified, deleted, or replaced
Access descriptor	No effect on descriptor	No effect on descriptor	Protects the descriptor from being read or edited
View descriptor	Protects the underlying data from being read or updated through the view	Protects the underlying data from being updated through the view	Protects the descriptor from being read or edited

You can use the following methods to assign, change, or delete a SAS password:

the global SETPASSWORD command, which opens a dialog box
the DATASETS procedure's MODIFY statement.

The syntax for using PROC DATASETS to assign a password to an access descriptor, a view descriptor, or a SAS data file is as follows:

PROC DATASETS LIBRARY=libref MEMTYPE=member-type;
MODIFY member-name (password-level = password-modification);

RUN;

The password-level argument can have one or more of the following values: READ=, WRITE=, ALTER=, or PW=. PW= assigns read, write, and alter privileges to a descriptor or data file. The password-modification argument enables you to assign a new password or to change or delete an existing password. For example, this PROC DATASETS statement assigns the password MONEY with the ALTER level of protection to the access descriptor ADLIB.SALARIES:

proc datasets library=adlib memtype=access;
   modify salaries (alter=money);
run;

In this case, users are prompted for the password whenever they try to browse or update the access descriptor or try to create view descriptors that are based on ADLIB.SALARIES.

In the next example, the PROC DATASETS statement assigns the passwords MYPW and MYDEPT with READ and ALTER levels of protection to the view descriptor VLIB.JOBC204:

proc datasets library=vlib memtype=view;
   modify jobc204 (read=mypw alter=mydept);
run;

In this case, users are prompted for the SAS password when they try to read the DBMS data or try to browse or update the view descriptor VLIB.JOBC204. You need both levels to protect the data and descriptor from being read. However, a user could still update the data that VLIB.JOBC204 accesses--for example, by using a PROC SQL UPDATE. Assign a WRITE level of protection to prevent data updates.

Note: When you assign multiple levels of passwords, use a different password for each level to ensure that you grant only the access privileges that you intend. [cautionend]

To delete a password, put a slash after the password:

proc datasets library=vlib memtype=view;
   modify jobc204 (read=mypw/ alter=mydept/);
run;

Protecting Connection Information

In addition to directly controlling access to data, you can protect the data indirectly by protecting the connection information that SAS/ACCESS uses to reach the DBMS. Generally, this is achieved by not saving connection information in your code.

One way to protect connection information is by storing user name, password, and other connection options in a local environment variable. Access to the DBMS is denied unless the correct user and password information is stored in a local environment variable. See the documentation for your DBMS to determine whether this alternative is supported.

Another way to protect connection information is by requiring users to manually enter it at connection time. When you specify DBPROMPT=YES in a SAS/ACCESS LIBNAME statement, each user has to provide DBMS connection information in a dynamic, interactive manner. This is demonstrated in the following statement. The statement causes a dialog box to prompt the user to enter connection information, such as a user name and password:

libname myoralib oracle dbprompt=yes defer=no;

The dialog box that appears contains the DBMS connection options that are valid for the SAS/ACCESS engine that is being used; in this case, Oracle.

Using the DBPROMPT= option in the LIBNAME statement offers several advantages. DBMS account passwords are protected because they do not need to be stored in a SAS program or descriptor file. Also, when a password or user name changes, the SAS program does not need to be modified. Another advantage is that the same SAS program can be used by any valid user name and password combination that is specified during execution. You can also use connection options in this interactive manner when you want to run a program on a production server instead of testing a server without modifying your code. By using the prompt window, the new server name can be specified dynamically.

Note: The DBPROMPT= option is not available in SAS/ACCESS Interface to DB2 under z/OS. [cautionend]

Extracting DBMS Data to a SAS Data Set

If you are the owner of a DBMS table and do not want anyone else to read the data, you can extract the data (or a subset of the data) and not distribute information about either the access descriptor or view descriptor.

Note: You might need to take additional steps to restrict LIBNAME or Pass-Through access to the extracted data set. [cautionend]

If you extract data from a view that has a SAS password assigned to it, the new SAS data file is automatically assigned the same password. If a view does not have a password, you can assign a password to the extracted SAS data file by using the MODIFY statement in the DATASETS procedure. See the Base SAS Procedures Guide for more information.

Defining Views and Schemas

If you want to provide access to some but not all fields in a DBMS table, create a SAS view that prohibits access to the sensitive data by specifying that particular columns be dropped. Columns that are dropped from views do not affect the underlying DBMS table and can be reselected for later use.

Some SAS/ACCESS engines support LIBNAME options that restrict or qualify the scope, or schema, of the tables in the libref. For example, the DB2 engine supports the AUTHID= and LOCATION= options, and the Oracle engine supports the SCHEMA= and DBLINK= options. See the SAS/ACCESS documentation for your DBMS to determine which options are available to you.

This example uses SAS/ACCESS Interface to Oracle:

libname myoralib oracle user=testuser password=testpass
        path='myoraserver' schema=testgroup;

proc datasets lib=myoralib;
run;

In this example the MYORALIB libref is associated with the Oracle schema named TESTGROUP. The DATASETS procedure lists only the tables and views that are accessible to the TESTGROUP schema. Any reference to a table that uses the libref MYORALIB is passed to the Oracle server as a qualified table name; for example, if the SAS program reads a table by specifying the SAS data set MYORALIB.TESTTABLE, the SAS/ACCESS engine passes the following query to the server:

select * from "testgroup.testtable"

Controlling DBMS Connections

Because the overhead of executing a connection to a DBMS server can be resource-intensive, SAS/ACCESS supports the CONNECTION= and DEFER= options to control when a DBMS connection is made, and how many connections are executed within the context of your SAS/ACCESS application. For most SAS/ACCESS engines, a connection to a DBMS begins one transaction, or work unit, and all statements issued in the connection execute within the context of the active transaction.

The CONNECTION= LIBNAME option enables you to specify how many connections are executed when the library is used and which operations on tables are shared within a connection. By default, the value is CONNECTION=SHAREDREAD, which means that a SAS/ACCESS engine executes a shared read DBMS connection when the library is assigned. Every time a table in the library is read, the read-only connection is used. However, if an application attempts to update data using the libref, a separate connection is issued, and the update occurs in the new connection. As a result, there is one connection for read-only transactions and a separate connection for each update transaction.

In the example below, the SAS/ACCESS engine issues a connection to the DBMS when the libref is assigned. The PRINT procedure reads the table by using the first connection. When the PROC SQL updates the table, the update is performed with a second connection to the DBMS.

libname myoralib oracle user=testuser password=testpass
        path='myoraserver';

proc print data=myoralib.mytable;
run;

proc sql;
   update myoralib.mytable set acctnum=123 
      where acctnum=456;
quit;

This example uses SAS/ACCESS Interface to DB2 under z/OS. The LIBNAME statement executes a connection by way of the DB2 Call Attach Facility to the DB2 DBMS server:

libname mydb2lib db2 authid=testuser;

If you want to assign more than one SAS libref to your DBMS server, and if you do not plan to update the DBMS tables, SAS/ACCESS enables you to optimize the way in which the engine performs connections. Your SAS librefs can share a single read-only connection to the DBMS if you use the CONNECTION=GLOBALREAD option. The following example shows you how to use the CONNECTION= option with the ACCESS= option to control your connection and to specify read-only data access.

libname mydblib1 db2 authid=testuser
   connection=globalread access=readonly;

If you do not want the connection to occur when the library is assigned, you can delay the connection to the DBMS by using the DEFER= option. When you specify DEFER=YES in the LIBNAME statement, the SAS/ACCESS engine connects to the DBMS the first time a DBMS object is referenced in a SAS program:

libname mydb2lib db2 authid=testuser defer=yes;

Note: If you use DEFER=YES to assign librefs to your DBMS tables and views in an AUTOEXEC program, the processing of the AUTOEXEC file is faster because the connections to the DBMS are not made every time SAS is invoked. [cautionend]

Locking, Transactions, and Currency Control

SAS/ACCESS provides options that enable you to control some of the row, page, or table locking operations that are performed by the DBMS and the SAS/ACCESS engine as your programs are executed. For example, by default, the SAS/ACCESS Oracle engine does not lock any data when it reads rows from Oracle tables. However, you can override this behavior by using the locking options that are supported in SAS/ACCESS Interface to Oracle.

To lock the data pages of a table while SAS is reading the data to prevent other processes from updating the table, use the READLOCK_TYPE= option, as shown in the following example:

libname myoralib oracle user=testuser pass=testpass
        path='myoraserver' readlock_type=table;

data work.mydata;
   set myoralib.mytable(where=(colnum > 123));
run;

In this example, the SAS/ACCESS Oracle engine obtains a TABLE SHARE lock on the table so that other processes cannot update the data while your SAS program reads it.

In the next example, Oracle acquires row-level locks on rows read for update in the tables in the libref.

libname myoralib oracle user=testuser password=testpass
   path='myoraserver' updatelock_type=row;

Note: Each SAS/ACCESS interface supports specific options; see the SAS/ACCESS documentation for your DBMS to determine which options it supports. [cautionend]

Customizing DBMS Connect and Disconnect Exits

To specify DBMS commands or stored procedures to run immediately after a DBMS connection or before a DBMS disconnect, use the DBCONINIT= and DBCONTERM= LIBNAME options. Here is an example:

libname myoralib oracle user=testuser password=testpass 
        path='myoraserver' dbconinit="EXEC MY_PROCEDURE";

proc sql;
   update myoralib.mytable set acctnum=123 
      where acctnum=567;
quit;

When the libref is assigned, the SAS/ACCESS engine connects to the DBMS and passes a command to the DBMS to execute the stored procedure MY_PROCEDURE. By default, a new connection to the DBMS is made for every table that is opened for updating. Therefore, MY_PROCEDURE is executed a second time after a connection is made to update the table MYTABLE.

To execute a DBMS command or stored procedure only after the first connection in a library assignment, you can use the DBLIBINIT= option. Similarly, the DBLIBTERM= option enables you to specify a command to run before the disconnection of only the first library connection, as in the following example:

libname myoralib oracle user=testuser password=testpass
        dblibinit="EXEC MY_INIT" dblibterm="EXEC MY_TERM";

Top of Page