Home » Databases » UPDATED: HOWTO: Extract Permissions from SAP Sybase IQ

UPDATED: HOWTO: Extract Permissions from SAP Sybase IQ

Back in 2013 I posted a method of extracting the table/view permissions from SAP IQ. With later 15.4 patches and v16 the USER_NAME() function would fail on the grantee column of sys.systableperm. An easy way to extract the users is to simply join the grantee column with sys.sysuser. I broke up the case statement to we don’t have a leading comma in the grant statement: “grant , insert on “. Since we’re dealing with tiny tables, there shouldn’t be any undue harm in simply running each grant generation statement separately and union them (to make dbisql happy for those of you that don’t have multiple results enabled).


    SELECT 'GRANT SELECT ON ' + USER_NAME(st.creator) + '.' + st.table_name + ' TO ' + su.user_name + ';'
    FROM
        sys.systable st,
        sys.systableperm stp,
        sys.sysuser su
    WHERE stp.stable_id = st.table_id
        and su.user_id = stp.grantee
        and stp.selectauth = 'Y'
        and lower(su.user_name) not in ('dba', 'public', 'sys', 'dbo', 'diagnostics', 'rs_systabgroup', 'sa_debug', 'extenv_main', 'extenv_worker', 'sys_spatial_admin_role')
    
    UNION ALL 
    
    SELECT 'GRANT DELETE ON ' + USER_NAME(st.creator) + '.' + st.table_name + ' TO ' + su.user_name + ';'
    FROM
        sys.systable st,
        sys.systableperm stp,
        sys.sysuser su
    WHERE stp.stable_id = st.table_id
        and su.user_id = stp.grantee
        and stp.deleteauth = 'Y'
        and lower(su.user_name) not in ('dba', 'public', 'sys', 'dbo', 'diagnostics', 'rs_systabgroup', 'sa_debug', 'extenv_main', 'extenv_worker', 'sys_spatial_admin_role')
    
    UNION ALL 
    
    SELECT 'GRANT UPDATE ON ' + USER_NAME(st.creator) + '.' + st.table_name + ' TO ' + su.user_name + ';'
    FROM
        sys.systable st,
        sys.systableperm stp,
        sys.sysuser su
    WHERE stp.stable_id = st.table_id
        and su.user_id = stp.grantee
        and stp.updateauth = 'Y'
        and lower(su.user_name) not in ('dba', 'public', 'sys', 'dbo', 'diagnostics', 'rs_systabgroup', 'sa_debug', 'extenv_main', 'extenv_worker', 'sys_spatial_admin_role')
    
    UNION ALL
    
    SELECT 'GRANT ALTER ON ' + USER_NAME(st.creator) + '.' + st.table_name + ' TO ' + su.user_name + ';'
    FROM
        sys.systable st,
        sys.systableperm stp,
        sys.sysuser su
    WHERE stp.stable_id = st.table_id
        and su.user_id = stp.grantee
        and stp.alterauth = 'Y'
        and lower(su.user_name) not in ('dba', 'public', 'sys', 'dbo', 'diagnostics', 'rs_systabgroup', 'sa_debug', 'extenv_main', 'extenv_worker', 'sys_spatial_admin_role')
    
    UNION ALL 
    
    SELECT 'GRANT REFERENCE ON ' + USER_NAME(st.creator) + '.' + st.table_name + ' TO ' + su.user_name + ';'
    FROM
        sys.systable st,
        sys.systableperm stp,
        sys.sysuser su
    WHERE stp.stable_id = st.table_id
        and su.user_id = stp.grantee
        and stp.referenceauth = 'Y'
        and lower(su.user_name) not in ('dba', 'public', 'sys', 'dbo', 'diagnostics', 'rs_systabgroup', 'sa_debug', 'extenv_main', 'extenv_worker', 'sys_spatial_admin_role')
    
    UNION ALL
    
    SELECT 'GRANT INSERT ON ' + USER_NAME(st.creator) + '.' + st.table_name + ' TO ' + su.user_name + ';'
    FROM
        sys.systable st,
        sys.systableperm stp,
        sys.sysuser su
    WHERE stp.stable_id = st.table_id
        and su.user_id = stp.grantee
        and stp.insertauth = 'Y'
        and lower(su.user_name) not in ('dba', 'public', 'sys', 'dbo', 'diagnostics', 'rs_systabgroup', 'sa_debug', 'extenv_main', 'extenv_worker', 'sys_spatial_admin_role')
Share Button

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

Facebook login by WP-FB-AutoConnect