Informix SQL - List all fields & tables where a user has authorized access to

user_5011027 0
Viewed 18

There are Answers how to retrieve a full list of Tables

My problem is more specific, as I want to retrieve a list of tables a specific user is able to perform a certain operation on (eg Select)

informix
1 Answers

With information from Informix Doc - SYSTABAUTH I am using the following query

SELECT TAB.TABNAME 
FROM 
  SYSTABLES TAB, 
  (
    SELECT TABID 
    FROM SYSTABAUTH 
    WHERE 
      GRANTEE = CURRENT_USER AND 
      UPPER(TABAUTH) LIKE 'S%'
  ) AUTH 
WHERE TAB.TABID = AUTH.TABID

By upper-ing the case I cover both options:

  • lowercase: grantee is able to perform action
  • uppercase: grantee is able to grant action to others

The query checks specifically for select privileges, the Informix Docs specify further privileges as well as columnar privileges

user_50110270
Related