how to connect to UCCX 11.x Informix db as dbadmin

[Sedat Duztas]

Hello everyone,

I need to login to UCCX 11.6 Informix database as dbadmin rights to create a new procedure for a custom report. As far as I know, uccxhruser account which is used for CUIC reporting has readonly rights in db_cra database. When I login to Informix with OpenAdmin Tool, I can see informix and uccxuser account and both have dbadmin rights but we do not have password of these accounts. Do you have any idea how to connect to db_cra as dbadmin ?

thanks

Sedat

[Jonathan Schulenberg · Accepted solution]

The uccxhruser has create permissions for stored procedures. There is no supported way to get additional permissions beyond that.

[Sedat Duztas]

Hi Jonathan,

Thanks for your kind reply. Does uccxhruser have SP create/update permission on db_cra database ?

As far as I know, it does not have these rights. When I tried to alter the "sp_agent_call_summary" ,I got permission denied error. How can we give this permission to uccxhruser or you meant the user has these permissions by design ?

thanks,

Sedat

[Jonathan Schulenberg]

You cannot alter a SP that the stock reports depend on. Copy it and create your own.

[Sedat Duztas]

I have also tried to do it but failed. I have written a very very basic SP but failed to create it. I have also copied and changed the name ,it again failed.  Do I need Cisco TAC to create for me a new user or change something on the system which allows uccxhruser to create a new SP ?

İs it related to the below case which requires Cisco TAC ?

https://cisco-marketing.hosted.jivesoftware.com/thread/64820?start=0&tstart=0   

"

Correct Answerby  target=_blankbill.talley@umb.com on Apr 20, 2016 7:46 AM

Thanks, but that process is also incomplete.  I've since figured out a couple of things.

1) there is a bug preventing creation of new stored procedures and TAC involvement is required to work around it.

2) setting the permissions doesn't do anything in regards to installing or deploying the stored proc and the doc you referenced stops at setting permissions.

3)  executing the stored proc (once the permissions issue has been resolved) will deploy the stored proc to the uccx database.  This is a step which is not documented anywhere is seems to be pretty crucial.

Thanks again for the guidance.  I really appreciate it.

"

[nanosynth]

Do you know if the "BUG" is in UCCX 10.6 as well? I am trying to do the exact same thing you are and I have all the same questions. I see all the other usernames in the SQL editor just like you, and what I need is the password for the 'informix' user as that one has all the rights one would even want. So using the 'uccxhruser' pre-built account, I then load a stored procedure I have called 'sp_agent_all_fields.sql' into the Server Studio app I am using to connect to the db_cra database, and try to run it and I get the not enough permissions thing. I also read that I would have to assign execution privileges for the stored procedure to the 'uccxHrUserRole' by using the command 'grant execute on sp_agent_all_fields.sql to uccxHrUserRole' but this doesn't work. I also see I can go into the parameters for the uccxhruser, right click, add, routines, and actually find this same stored procedure in the menu there, I then check the box for it, click next, the 'execute' box then has a check-mark, then I click next, then 'EXECUTE" and it immediately tells me it failed (execute privilege denied). What is the point of all this when everything is severely limited and fails...Id like to hear about this "BUG" some more and what TAC did to fix it and once this bug is fixed, then will I be able to do what I want here?

[Sedat Duztas]

thank you very much Jonathan. I was using squirrel sql client for Informix and I have learned today that it has some issues. I tried to create a function  with IBM OpenAdmin and managed to create it. Infact, I have still with an issue with creating a customized version of sp_agent_call_summary function. The IBM OpenAdmin and Squirrel gave me sql statement error even if I changed only name of the function. Did you meet this issue already ?

thanks,

Sedat