CMS / Informix - Grant access to all DB tables

[dudecrush]

Hello - This is for all you CMS / Informix DB users out there...

I created a user account to run SQL queries against the CMS database for reporting. It was successful, but I was getting "No Select Permission" errors on the "haglog" table.

I fixed this by logging in as root and running the "dbaccess" command from the command line prompt. I ran the query "grant select on haglog to <useraccount>". Problem solved.

Next problem: Since it stands to reason there may be other tables where my account doesn't have select permissions, I'd like to grant the select permission to all tables for the user account, instead of a table-by-table basis.

So my quesion is: What SQL syntax can I use to grant select permission to all tables? I've tried the following:

grant select on database cms@cms_ol to <useraccount>
grant select on cms@cms_o1 to <useraccount>
grant select on database to <useraccount>
grant select on database cms to <useraccount>
grant select on all to <useraccount>

None has worked; I'm getting syntax errors. Poking through IBM's Informix DB query manual, the first one I tried should've worked but it didn't.

Any ideas?

 

[europe]

I would go the ODBC route....
What you are trying to do now can mess up you avaya support

Please let me know if the information that was provided is helpfull.
Edwin Plat
A.K.A. Europe

 

[dudecrush]

europe

It was precisely because I installed ODBC drivers that I ran into this problem. After install, I got -272 errors (No Select Permission) on the haglog table. Like I said, I solved the haglog problem, but I want to avoid any potential permission problems ahead of time.

At any rate, we don't have Avaya support; our support is through a 3rd party vendor who hasn't a clue as to how to make this change.

 

[Stinney]

You can use CMS Supervisor to make sure that the login has permissions to read the skills, VDNs, Dictionary, etc. This should resolve your issue.

- Stinney

Quoting only proves you know how to cut and paste.

 

[dudecrush]

To All,

I was able to solve this problem, but not through an magic SQL syntax. The more I looked on-line, that wasn't an easy thing to do.

Instead, I found a list of the CMS database table names from the Avaya CMS Open Database Connectivity guide. I then went through each table name and granted "Select" permission to my account to each and every single table name. It took me about 20 minutes, but then I was done.