SAS Security

Securing Data

SAS preserves the data security provided by your DBMS and operating system. However, 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.

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

These and other approaches are discussed in detail in subsequent 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.

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

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

Here is the syntax for using PROC DATASETS to assign a password to a SAS data set.

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 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 data in Adlib.Salaries:

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

In this case, users are prompted for the password whenever they try to browse or update the data or try to create new data sets 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 data in Mylib.Jobc204:

proc datasets library=mylib;
   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 data in Vlib.Jobc204. A user could still update the data in Vlib.Jobc204. For example, a user could update the data by using a PROC SQL UPDATE statement. (To prevent user updates, 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=mylib;
   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, 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.

For Hadoop, another way to protect connection information between Hive and SAS/ACCESS Interface to Hadoop is to use Kerberos authentication. When you use Kerberos, there are no sensitive user names and passwords to protect. For more information, see Hadoop Configuration.

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. The statement below opens 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 the preceding LIBNAME statement, the connection options are for 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, you can specify the new server name dynamically.

See the section DBPROMPT= Data Set Option for more information about which engines support the DBPROMPT= LIBNAME option.

Extracting DBMS Data to a SAS Data Set

If you extract data from a SAS view with an assigned SAS password, the new SAS data set is automatically assigned to the same password. If a view does not have a password, you can assign a password to the extracted SAS data set by using the MODIFY statement in the DATASETS procedure. For more information, see the Base SAS Procedures Guide.

Specifying 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. To restrict access to some columns, specify that those 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. 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=myusr1 password=mypwd1
        path='mysrv1' schema=testgroup;

proc datasets lib=mysrv1;
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 MYORALIB libref 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 MYSRV1.TESTTABLE, the SAS/ACCESS engine passes this query to the server.

select * from "testgroup.testtable"

Controlling DBMS Connections

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. For many DBMSs, the default 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 PROC SQL queries the table, the query uses a second connection to the DBMS.

libname myhadlib hadoop server=hadoopsvr user=myusr1 password=mypwd1;

proc print data=myhadlib.mytable;
run;

proc sql;
   select date1 from myhadlib.mytable;
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=myusr1;

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=myusr1
   connection=globalread access=readonly;

If you do not want a connection to occur when a 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 myhadlib hadoop server=hadoopsvr user=user1 pass=mypwd1 defer=yes;

Locking, Transactions, and Currency Control

SAS/ACCESS provides options so that you can control some of the row, page, or table locking operations that the DBMS and SAS/ACCESS engine perform 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=myusr1 pass=mypwd1
        path='mysrv1' 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=myusr1 password=mypwd1
   path='mysrv1' updatelock_type=row;

Each SAS/ACCESS interface supports specific options. See the DBMS-specific reference section for your SAS/ACCESS interface to determine which options it supports.

Customizing a DBMS Connection

To specify DBMS commands or stored procedures to run immediately after a DBMS connection, use the DBCONINIT= LIBNAME option. Here is an example.

libname myoralib oracle user=myusr1 password=mypwd1
        path='mysrv1' dbconinit="EXEC MY_PROCEDURE";

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

When a libref is assigned, the SAS/ACCESS engine connects to the DBMS and passes a command that you specify 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, the command is executed an additional 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, you can use the DBLIBTERM= LIBNAME option to specify a command to run before the disconnection of only the first library connection. Here is an example.

libname myoralib oracle user=myusr1 password=mypwd1
        dblibinit="EXEC MY_INIT" dblibterm="EXEC MY_TERM";