No select permission

Hi all, Just a quick and simple/complex question - When a user gets "No select permission" (err -272), Is there any way (using SQL) to find out which table user doesn't have select permission ? Using onaudit, I can get this but would be great if this can be achived by quering sysmaster tables. TIA.

Hari Gupta wrote: > Just a quick and simple/complex question - When a user gets "No select > permission" (err -272), Is there any way (using SQL) to find out which > table user doesn't have select permission ? Using onaudit, I can get > this but would be great if this can be achived by quering sysmaster > tables. You don't need sysmaster - you need the current database's system catalog... You need to find the tables which that user can select from, and then generate the list of tables which isn't in the list. A user can select from a table if: 1. the corresponding systabauth entry for the given user includes 's' or 'S' in column 1, or 2. the corresponding systabauth entry for the user's current role includes 's' or 'S' in column 1, or 3. the corresponding systabauth entry for the pseudo-user PUBLIC includes 's' or 'S' in column 1. Good, clean fun: SELECT DISTINCT a.tabid FROM 'informix'.systabauth a, 'informix'.systables t WHERE t.tabid = a.tabid AND t.tabtype = 'T' AND a.tabauth[1] IN ('s', 'S') -- might be too tricky! --AND a.tabauth MATCHES "[sS]*" -- should work regardless AND (a.grantee = USER OR a.grantee = 'public' OR USER IN (SELECT r.grantee FROM 'informix'.sysroleauth r WHERE r.rolename = a.grantee ) ) The distinct isn't 100% necessary. I think the role determination is correct, but I'm not completely sure of that. You then generate the list of tables where the tabid is not in list above. Call that mouthful '<select-A>' and you can write: SELECT DISTINCT 1t.owner, t1.tabname, t1.tabid FROM 'informix'.systables t1 WHERE t1.tabtype = 'T' AND t1.tabid NOT IN (<select-A>) This generates the list of true (base) tables for which the current user does not have SELECT permission on the table through the PUBLIC permissions, the user's explicit permissions, or the permissions of one of the roles which the user is permitted to set as the current role. That hasn't been past a server - I reserve the right to have silly mistakes in it. Residual problem - what about synonyms (public and private), views, and so on? Answer - the query above does not address these issues. You can modify the tabtype queries to permit other types. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/