Dynamically generate GRANT SQL from syscat.tabauth table

Question

I have a database (on DB2 9.7) A in which suppose I have tables X,Y,Z...n Now I have created same tables X,Y,Z...n in database B. I want to provide same GRANTs to users in database B as it was in database A. So based on SYSCAT.TABAUTH I am trying to generate GRANT SQLs. I have written the following query for it:

db2 "select 'GRANT '||
case INSERTAUTH
    WHEN 'Y' THEN 'INSERT,'
    WHEN 'N' THEN ' '
    END||
case ALTERAUTH
    WHEN 'Y' THEN 'ALTER,'
    WHEN 'N' THEN ' '
    END||
case DELETEAUTH
    WHEN 'Y' THEN 'DELETE,'
    WHEN 'N' THEN ' '
    END||
case SELECTAUTH
    WHEN 'Y' THEN 'SELECT,'
    WHEN 'N' THEN ' '
    END||
case UPDATEAUTH
    WHEN 'Y' THEN 'UPDATE,'
    WHEN 'N' THEN ' '
    END||
' ON '||TABSCHEMA||'.'||TABNAME||' TO '||GRANTEE from SYSCAT.TABAUTH 
where INSERTAUTH='Y' OR  ALTERAUTH='Y' OR DELETEAUTH='Y' OR SELECTAUTH='Y' OR UPDATEAUTH='Y'"

However, the problem I am facing is of the additional ',' at end. Suppose a user has only Insert auth, the above query will generate GRANT sql as:

GRANT INSERT, ON SCHEMA.TABLE TO GRANTEENAME
or if user has insert and select grants then:
GRANT INSERT,SELECT, ON SCHEMA.TABLE TO GRANTEENAME

How can I solve this? Please help..

bhamby · Answer 1 · 2013-01-18T14:10:22.237

You can always push the dynamic part into a sub-query, and then use a combination of LENGTH() and SUBSTR() to trim off the extra comma. Here is your SQL I modified a bit:

SELECT
'GRANT' ||
SUBSTR(T.AUTHSTRING, 1 LENGTH(T.AUTHSTRING) -1) 
|| T.TABNAME
FROM(
select
    case INSERTAUTH
        WHEN 'Y' THEN 'INSERT,'
        WHEN 'N' THEN ' '
    END
    ||
    case ALTERAUTH
        WHEN 'Y' THEN 'ALTER,'
        WHEN 'N' THEN ' '
    END
    ||
    case DELETEAUTH
        WHEN 'Y' THEN 'DELETE,'
        WHEN 'N' THEN ' '
    END
    ||
    case SELECTAUTH
        WHEN 'Y' THEN 'SELECT,'
        WHEN 'N' THEN ' '
    END
    ||
    case UPDATEAUTH
        WHEN 'Y' THEN 'UPDATE,'
        WHEN 'N' THEN ' '
    END
    AS AUTHSTRING,

    ' ON ' || RTRIM(TABSCHEMA) || '.' || RTRIM(TABNAME)||' TO ' || RTRIM(GRANTEE) AS TABNAME
from SYSCAT.TABAUTH
where INSERTAUTH='Y'
   OR  ALTERAUTH='Y'
   OR DELETEAUTH='Y'
   OR SELECTAUTH='Y'
   OR UPDATEAUTH='Y'
) AS T

I tested this, and it worked on LUW 9.7 and z/OS 9.1.

Hi Bhamby, Thanks for your answer but I made some changes to your query and it worked for fine me then. Please find updated query in my answer. — Saurabh Agrawal, Jan 18 '13 at 07:25

score 0 · Answer 2 · answered Jan 18 '13 at 07:34

This is what I finally made and it works fine:

db2 "SELECT
'GRANT ' ||
SUBSTR(T.AUTHSTRING, 1 ,LENGTH(T.AUTHSTRING) - 1) 
|| T.TABNAME
FROM(
select
    case INSERTAUTH
        WHEN 'Y' THEN 'INSERT,'
        WHEN 'N' THEN ''
    END
    ||
    case ALTERAUTH
        WHEN 'Y' THEN 'ALTER,'
        WHEN 'N' THEN ''
    END
    ||
    case DELETEAUTH
        WHEN 'Y' THEN 'DELETE,'
        WHEN 'N' THEN ''
    END
    ||
    case SELECTAUTH
        WHEN 'Y' THEN 'SELECT,'
        WHEN 'N' THEN ''
    END
    ||
    case UPDATEAUTH
        WHEN 'Y' THEN 'UPDATE,'
        WHEN 'N' THEN ''
    END
    AS AUTHSTRING,
    ' ON ' ||TRIM(TABSCHEMA)||'.'||TRIM(TABNAME)||' TO ' ||GRANTEE AS TABNAME
from SYSCAT.TABAUTH
where INSERTAUTH='Y'
   OR  ALTERAUTH='Y'
   OR DELETEAUTH='Y'
   OR SELECTAUTH='Y'
   OR UPDATEAUTH='Y'
) AS T"

Dynamically generate GRANT SQL from syscat.tabauth table

2 Answers2