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, from the SAS side of the interface, you can take steps like these.

These and other approaches are discussed in detail in subsequent sections.

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.

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

Here is the syntax for using PROC DATASETS to assign a password to an access descriptor, a view descriptor, or a SAS data file.

The password-level argument can have one or more of these 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.

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.

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

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

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, you can achieve this 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. (See DBPROMPT=.) The following statement shows this: It 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.

The DBPROMPT= option is not available in SAS/ACCESS Interface to DB2 under z/OS.

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.

You might need to take additional steps to restrict LIBNAME or pass-through access to the extracted data set.

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. For more information, see the Base SAS Procedures Guide.

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 this query to the server.

select * from "testgroup.testtable"

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;

To assign more than one SAS libref to your DBMS server when you do not plan to update the DBMS tables, SAS/ACCESS lets you optimize how the engine makes connections. Your SAS librefs can share a single read-only connection to the DBMS if you use the CONNECTION=GLOBALREAD option. This example shows 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;

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 SAS/ACCESS Interface to Oracle supports.

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 this example:

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

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

Here 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 this 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;

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

To specify DBMS commands or stored procedures to run immediately after a DBMS connection or before a DBMS disconnect, use the DBCONINIT= and DBCONTERM= 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.

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