ACCESS Procedure in SAS and SYSTEM 2000

Types of Procedure Statements

In SAS/ACCESS, there are two categories of procedure statements: database-description statements and editing statements. The ACCESS procedure in SAS enables you to create and edit the descriptor files used by the SAS/ACCESS interface to SYSTEM 2000. Details about the ACCESS procedure statements are given in alphabetical order after the details about the PROC ACCESS statement.

Passwords for Descriptor Files

The SAS/ACCESS interface requires that access descriptors and view descriptors have a SYSTEM 2000 password to access the database. The password for an access descriptor determines the description of the database that is used to create view descriptors. The password for a view descriptor determines the data that you see, and your ability to subset and edit the data through the descriptor.
For the access descriptor, the password is specified in the DATABASE statement. For the view descriptor, the SYSTEM 2000 password is stored in the view descriptor by using the S2KPW statement, or the password can be submitted as a SAS data set option. Storing the SYSTEM 2000 password in a view descriptor, gives everyone who uses the view descriptor access to its data. Specifying a password as a data set option gives users access to the database passwords.
To protect your database passwords, store the SYSTEM 2000 password in the view descriptor, and assign one or more SAS passwords to control access to the descriptor file. You can also assign SAS passwords to control who can create view descriptors from an access descriptor. To access the descriptor files, specify the SAS password as a data set option. For example, to create a view descriptor, specify the access descriptor password in the PROC ACCESS statement after the ACCDESC= option, as follows:
  proc access dbms=s2k accdesc=mylib.employee (alter=reward);
     create vlib.customer.view;
     select all;
  run;
The following table summarizes the levels of protection that SAS passwords give and the effects on descriptor files.
Effects of SAS Password on Descriptor Files
Files
READ=
WRITE=
ALTER=
access descriptor
no effect
no effect
protects descriptor from being read or edited
view descriptor
protects DBMS data from being read or updated
protects DBMS data from being updated
protects descriptor from being read or edited
For detailed information about the levels of protection and the types of SAS passwords that you can use, see SAS 9.3 Statements: Reference. For more information about protecting access and view descriptors, see Data Security.

SAS Passwords

You can assign, change, or clear a password for an access descriptor, a view descriptor, or another SAS file in SAS by using the MODIFY statement in the DATASETS procedure. The following syntax for PROC DATASETS assigns a password to an access descriptor, a view descriptor, or a SAS data file:
PROC DATASETS LIBRARY= libref MEMTYPE= member-type;
MODIFY member-name (password-level = password-modification); RUN;
password-level can be one or more of the following values: READ=, WRITE=, ALTER=, or PW=. Use PW to assign Read, Write, and Alter privileges to a descriptor or a data file. password-modification enables you to assign a new password or to change or delete an existing password. For example, the following program assigns the password REWARD and specifies the level of protection to the access descriptor MyLib.Employe as Alter. After this program is executed, users are prompted for a password when they try to browse or edit the access descriptor or create view descriptors that are based on MyLib.Employe.
proc datasets library=mylib memtype=access;
   modify employee (alter=reward);
run;
In the next example, the program assigns the passwords MYPW and MYDEPT with Read and Alter levels of protection to the view descriptor Vlib.CustAcct. After this program is executed, users are prompted for the SAS password when they try to read the DBMS data, or try to browse or edit the view descriptor Vlib.CustAcct. In this instance, you need both Read and Alter levels to protect the data and the view descriptor from being read. However, a user could still update the data accessed by Vlib.CustAcct by using an UPDATE statement in PROC SQL. Assign the Write level of protection to prevent data updates.
proc datasets library=vlib memtype=view;
   modify custacct (read=mypw alter=mydept);
run;
To delete a password for an access descriptor or any SAS data set, put a slash after the password, as shown in the following example:
proc datasets library=vlib memtype=view;
   modify custacct (read=mypw/ alter=mydept/);
run;
In the following program, PROC DATASETS sets a password for Read and Alter levels to the view descriptor Vlib.CustInfo, and PROC PRINT tries to use the view descriptor with an invalid password and, then, a valid password.
/* Assign passwords */
proc datasets library=vlib memtype=view;
  modify custinfo (read=r2d2 alter=c3po);
run;

/* Invalid password given */
proc print data=vlib.custinfo (pw=r2dq);
  where ssn = '178-42-6534';
  title2 'Data for 178-42-6534';
run;

/* Valid password given */
proc print data=vlib.custinfo (pw=r2d2);
  where ssn = '178-42-6534';
  title2 'Data for 178-42-6534';
run;
For more examples of assigning, changing, deleting, and using SAS passwords, see SAS Statements: Reference.