UPDATED: HOWTO: Extract Permissions from SAP Sybase IQ using the LIST() function

Back in August I provided an updated method to extract the permissions for tables in a SAP IQ server. This provided SAP_IQquite a few GRANT statements to execute. If you have thousands of tables and/or thousands of users, the created a large file that can take a while to execute. We can reduce the number of GRANT statements by combining the SELECT, DELETE, UPDATE, ALTER, REFERENCE and INSERT grants into a single GRANT statement with the LIST() function.

The LIST() function joins a value from a set of rows with a specified delimiter. Think of this as the equivalent of the Perl JOIN() command.

Take for example the following strings:

VALUE1
VALUE2 
VALUE3

In Perl if you wanted a single string with a comma as the delimiter you would write the following:

my $STRING = join(', ', 'VALUE1', 'VALUE2', 'VALUE3');
print "$STRING\n";
VALUE1, VALUE2, VALUE3

In IQ we can do the equivalent with the LIST() function:

CREATE TABLE #test (column_a CHAR(6));
INSERT INTO #test VALUES ('VALUE1');
INSERT INTO #test VALUES ('VALUE2');
INSERT INTO #test VALUES ('VALUE3');

SELECT LIST(column_a, ', ') FROM #test;
VALUE1, VALUE2, VALUE3

The extract permissions for IQ tables code as updated to use the LIST() function:

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

Produces the following SQL output:

GRANT SELECT ON MYUSER.my_table TO otheruser_i;
GRANT DELETE, UPDATE, INSERT, SELECT ON MYUSER.my_table TO otheruser_u;
GRANT DELETE, UPDATE, INSERT, SELECT ON MYUSER.my_table TO otheruser_e;
Share Button

SAP Sybase IQ and ODBC (Linux/UNIX)

Connecting to SAP SAP_IQSybase’s IQ server shouldn’t be scary but the documentation leaves a lot to be desired. Log on to your favorite Linux or Unix box and make sure you source the IQ.sh file wherever you installed the software.

Let’s use the following hypothetical server:

IQ Server:  MyIQboxOfDoom
Port:  5000
Hostname: myiqboxofdoom

If you want a single odbc.ini file for all users, you’ll need to create the file and store it somewhere. Make sure you set the ODBCINI environment variable somewhere (NOT the IQ.sh file as it will but overwritten anytime you patch IQ) to the file. For example: export ODBCINI=/opt/databases/odbc.ini

If you want each user to have their own odbc.ini file, they will need to create the file named as ${HOME}/.odbc.ini The format will be the same as odbc.ini
odbc.ini / .odbc.ini contents:

[MyIQboxOfDoom]
CommLinks=tcpip(ip=myiqboxofdoom;port=5000;DOBROADCAST=NONE;VERIFY=NO)
AutoStop=no

Connecting is a simple matter of:

dbisql -c "dsn=MyIQboxOfDoom;uid=myuser;pwd=mypass" -nogui

Of course you could embed the user in the odbc.ini file like so:

[MyIQboxOfDoom]
CommLinks=tcpip(ip=myiqboxofdoom;port=5000;DOBROADCAST=NONE;VERIFY=NO)
AutoStop=no
uid=myuser
Share Button