Informix, grant select on all tables

Question

With Informix I can grant select on a table like;

grant select on 'dba'.mytable to someuser as dba;

How can I perform this on all tables in the database?

Answer 1

There isn't a simple way to do that (in either the SQL standard or in Informix), but it usually isn't necessary since that specific privilege is normally granted by default. However, I imagine that in fact you want to do more general operations too, and want to know what options there are.

Depending on what you are aiming to do and how you have your system set up, you can achieve your goals in various ways.

Brute force - direct answer to question

 SELECT 'GRANT SELECT ON "' || TRIM(owner) || '".' || TRIM(tabname) ||
        ' TO someuser AS dba;'
     FROM "informix".systables
     WHERE tabid >= 100;

Take the output from this and feed it into a program. I use SQLCMD for the job, not least because it doesn't generate unwanted output:

sqlcmd -d dbname -e "
 SELECT 'GRANT SELECT ON \"' || TRIM(owner) || '\".' || TRIM(tabname) ||
        ' TO someuser AS dba;'
     FROM "informix".systables
     WHERE tabid >= 100;" |
sqlcmd -d dbname -f -

The extra backslashes are needed to work around the shell. The '-f -' options aren't strictly necessary, but make it explicit that the second SQLCMD will read from standard input. If you use DB-Access, you have to worry about the verbiage it generates - which is the reason I wrote SQLCMD in the first place.

Clearly, this solution can fairly readily be scripted and generalized - you just have to be careful with your use of quotes.

Using roles

Another option is to create a role that has all the permissions that you want, and then grant that role to users as required. The downside to this is that your software needs to set the role so that people connecting have that privilege. One possibility is to set the select-only role as the default role for each user.

Using Server Studio Java Edition

SSJE has privilege management tools to simplify this task.

Using Open Admin Too

OAT has tools to help with privilege management.