Changing user Privileges on a Database Informix

Question

How can I change a user privileges on a database, on all tables from consult to select, delete, update, insert, in Informix.

I tried to use something like this

dbaccess DATABASE exec for i in (select table_name from user_tables) loop execute immediate 'grant select,insert,update,delete on '||i.table_name||' to USER'; end loop

But it didn't work.

Answer 1

DB-Access isn't my favourite tool, but it can do the job with some support from a shell script. You end up invoking it twice, once to generate the list of table names and once to process the permissions.

Given the file so-6952-3871.sql containing:

unload to '/dev/stdout'
    select tabname
       from "informix".systables
      where tabid >= 100
        and tabtype = 'T';

the shell script so-6952-3871.sh does the job, revoking existing privileges and granting new ones:

#!/bin/sh
#
# @(#)$Id$
#
# Grant select, insert, delete, update permission on all user tables in a database to a named user.

: "${DBACCESS:=dbaccess}"

if [ $# = 0 ]
then
    echo "$0: you must specify the database and at least one user" >&2
    echo "Usage: $0 database user [...]" >&2
fi
dbase="$1"
shift
if [ $# = 0 ]
then
    echo "$0: must specify at least one user" >&2
    echo "Usage: $0 database user [...]" >&2
    exit 1
fi

$DBACCESS $dbase so-6952-3871.sql |
sed -n '/^\([a-zA-Z0-9_]*\)|$/ s//\1/p' |
while read tabname
do
    #echo "Table: $tabname" >&2
    for user in "$@"
    do
        echo "revoke all on $tabname from $user;"
        echo "grant select, insert, delete, update on $tabname to $user;"
    done
done |
$DBACCESS $dbase -

I've chosen to use the "informix".systables system catalog table since I don't have a table called user_tables in my database. You can refine the selection criteria (for example, to omit the time series tables) as necessary.

Provided you have sufficient permissions to grant and revoke permissions in the database (e.g. as the DBA or as user informix), this should work OK.

Rather than use DB-Access, I'd use my SQLCMD (available from the IIUG Software Archive), which I wrote to behave consistently in shell scripting contexts whereas DB-Access doesn't. It dates back to 1986 (before there was dbaccess; in those days, you used isql instead — DB-Access was carved out of isql in an evening). It bears no relation to Microsoft's johnny-come-lately program of the same name — except for the name and having the same general purpose (manipulate SQL databases). With SQLCMD, there'd be no need for the sed line to ignore noise output from DB-Access on standard output.