Problem Note 65904: SAS® Federation Server stops responding when you run queries against X_OBJECT_PRIVILEGES in SYSCAT and the queries run for hours
 |  |  |  |  |
The select * from "SYSCAT"."SYSCAT"."X_EFFECTIVE_OBJECT_PRIVILEGES" query runs for hours. In this scenario, SAS Federation Server stops responding, making it unavailable for use. You encounter this issue if you run code similar to the following:
proc sql noprint;
connect to FEDSVR(server="server_name" port=24141 dsn="ADMIN" );
create table X_EFFECTIVE_OBJECT_PRIVILEGES as
select * from connection to fedsvr
(select * from "SYSCAT"."SYSCAT"."X_EFFECTIVE_OBJECT_PRIVILEGES"
where
"SYSCAT"."SYSCAT"."X_EFFECTIVE_OBJECT_PRIVILEGES"."DATA_SERVICE" not in ('__SERVER__','SYSPROC','SQL_LOG')
"SYSCAT"."SYSCAT"."X_EFFECTIVE_OBJECT_PRIVILEGES"."PRIVILEGE_NAME" in ('SELECT')
order by
"SCHEMA_NAME","OBJECT_NAME" ) ;
disconnect from FEDSVR;
quit;
Restarting SAS Federation Server solves the issue for a while, but it fails again if you execute the same query.
The current workaround is to query OBJECT_PRIVILEGES to reduce the query time. However, this query does not provide the same level of information about objects that users have access to at a certain point in time. Therefore, reports generated using information from this query are not as complete.
Click the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | SAS Federation Server | Microsoft® Windows® for x64 | 4.2 | 9.4 TS1M6 | ||
| 64-bit Enabled AIX | 4.2 | 9.4 TS1M6 | ||||
| 64-bit Enabled Solaris | 4.2 | 9.4 TS1M6 | ||||
| HP-UX IPF | 4.2 | 9.4 TS1M6 | ||||
| Linux for x64 | 4.2 | 9.4 TS1M6 | ||||
| Solaris for x64 | 4.2 | 9.4 TS1M6 | ||||