DICTIONARY.PWDB and DICTIONARY.ACLS :: SAS(R) Scalable Performance Data Server 5.1: Administrator's Guide, Second Edition

Overview of Dictionaries

In addition to providing dictionary information for tables and columns, SPD Server provides information about the users in the password database and the ACL objects that are available. The following listing shows the column definitions for DICTIONARY.PWDB and DICTIONARY.ACLS:

DICTIONARY.PWDB {user char(8) Label = 'User'
  auth_lvl char(5) Label = 'Authorization Level'
  ip_addr char(16) Label = 'IP Address'
  defgrp char(8) Label = 'Default Group'
  othgrps char(40) Label = 'Other Groups'
  expire char(6) Label = 'Expire Period'
  mod_date char(32) Label = 'Password Last Modified'
  log_date char(32) Label = 'Last Login'
  timeout char(6) Label = 'Timeout Period'
  strikes char(6) Label = 'Failed Login Attempts'}

DICTIONARY.ACLS {owner char(8) Label = 'Owner'
  group char(8) Label = 'Group'
  defacs char(56) Label = 'Default Access'
  grpacs char(56) Label = 'Group Access'}

Example: Listing the Users in the Password Database Using SQL Pass-Through

To use SQL pass-through, you must first establish an SQL pass-through connection to SPD Server using ACLSPECIAL=YES.

proc sql;

  connect to sasspds     (dbq='tmp'      server=localhost.5400 
     user='admin'
     password='spds123' 
     ACLSPECIAL=YES);

Note: Without ACLSPECIAL=YES, you get the result set only for the users who are making a pass-through connection and not for all users.

To list all the users in the password database, submit the following command:

       select *
       from connection
       to sasspds
          (select *
           from dictionary.pwdb)

To select only the user name and last log in date, submit the following command:

       select *
       from connection
       to sasspds
          (select user, log_date
           from dictionary.pwdb);

Example: Listing ACL Objects Using SQL Pass-Through

To list all ACL objects for a user by using a pass-through connection, first establish an SQL pass-through connection to SPD Server using ACLSPECIAL=YES.

proc sql;

  connect to sasspds     (dbq='tmp'      server=localhost.5400 
     user='admin'
     password='spds123' 
     ACLSPECIAL=YES);

Note: Without ACLSPECIAL=YES, you get the result set only for the users who are making a pass-through connection and not for all users.

Then, submit the following command:

       select *
       from connection
       to sasspds
          (select *
           from dictionary.acls);

To find any ACL objects that specify Jones as the owner, submit the following command:

       select *
       from connection
       to sasspds
          (select *
           from dictionary.acls
           where owner = "Jones");