Checking Query Security via SQL

Query security always seems to be an issue: "I can't see the query!"

Here are a couple of SQL statements to review the setup. The last one tells you why a user can't see a query.

-- List of records available to query
SELECT * FROM PSQRYACCLSTRECS
WHERE CLASSID = 'ROSO_PERMISSION_LIST_NAME'
ORDER BY RECNAME;

-- List of Query Tree Access Groups
SELECT * FROM PS_SCRTY_ACC_GRP
WHERE CLASSID = 'ROSO_PERMISSION_LIST_NAME';

-- Records without security
SELECT * FROM PSQRYRECORD
WHERE QRYNAME = 'ROSO_QUERY_NAME'
AND RECNAME NOT IN (
SELECT DISTINCT RECNAME FROM PSQRYACCLSTRECS
WHERE CLASSID = 'ROSO_PERMISSION_LIST_NAME');



Post a Comment:
Comments are closed for this entry.