SQL help

[bhakhra]

hi, basically I am trying to find out if there are any tables for schema sunny that doesnt have insert and update grants for testuser, Can anyone help me with this? how can I join these two ? thanks Sunny

select tabname from syscat.tables where tabschema = 'SUNNY' and type = 'T'

select tabname from syscat.tabauth where grantee = 'TESTUSER' AND INSERTAUTH = 'N' AND UPDATEAUTH = 'N'

[sakumar9]

Basically you want to join these two queries. You can try following code.

WITH ABC AS 
 (SELECT tabname 
  FROM syscat.tables
  WHERE tabschema = 'SUNNY' and type = 'T')
SELECT syscat.tabauth.tabname 
  FROM ABC, syscat.tabauth 
  WHERE grantee = 'TESTUSER' AND 
               INSERTAUTH = 'N' AND 
               UPDATEAUTH = 'N' AND
               ABC.tabname = syscat.tabauth.tabname;

Let me know if your issue is resolved.

Regards
-- Sanjay