DICTIONARY.PWDB and DICTIONARY.ACLS

Overview of Dictionaries

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

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

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 only get the result set 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:

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

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

       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 making 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 only get the result set for the users who are making a pass-through connection and not for all users.

Then, submit the following:

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

To find any ACL objects where "Jones" is the owner, submit the following:

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