1

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)

til
  • 832
  • 11
  • 27

1 Answers1

1

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

til
  • 832
  • 11
  • 27
  • 1
    Your query doesn't cover permissions granted to PUBLIC. It also doesn't cover permissions granted via roles. And, for a final twist of agony, it doesn't cover column-level permissions, which are relevant for SELECT and UPDATE. OTOH, I don't think you need worry too much about the column-level permissions in this query. And you simply need to be aware of the issue with roles. But ignoring PUBLIC permissions seriously underestimates who can select from the tables. – Jonathan Leffler Aug 25 '22 at 18:58
  • @jonathan-leffler Thanks for the info! Do you have a query / method to access all the options? I'll happily accept that answer! – til Aug 29 '22 at 08:00