Topic
  • 1 reply
  • Latest Post - ‏2011-09-28T12:53:35Z by CarlosLyrio
JamesBrooks
JamesBrooks
2 Posts

Pinned topic connect to db user x doesn't work?

‏2011-08-04T19:10:33Z |
I had a sql file open in Optim DA and needed to create sequences as another user. I was connected to the database through the instance owner, DB2INST1. I ran this code in the SQL script:

***********
CONNECT TO MYDATAB user USER using PASSWD;

set current schema DFLT_TR;

CREATE SEQUENCE DFLT_TR.SEQ_TABLE_KEY AS BIGINT
MINVALUE 1 MAXVALUE 9999999999
START WITH 1 INCREMENT BY 1
CACHE 500 CYCLE NO ORDER;

**********

The Sequence was created, but I got the following error when my application tried to use the sequence.

<Attribute Name="ErrorDescription" Value="&quot;USER&quot; does not have the required authorization or privilege to perform operation &quot;USAGE&quot; on object &quot;DFLT_TR.SEQ_TABLE_KEY&quot;.. SQLCODE=-551, SQLSTATE=42501, DRIVER=4.11.77"/>
I saw in syscat.sequences that the DEFINER was DB2INST1. I don't know what happened unless the main connection overruled the connect statement.

I went back and ran the same create statements via the command line and everything work OK.

Has anyone else seen this?
Updated on 2011-09-28T12:53:35Z at 2011-09-28T12:53:35Z by CarlosLyrio
  • CarlosLyrio
    CarlosLyrio
    1 Post

    Re: connect to db user x doesn't work?

    ‏2011-09-28T12:53:35Z  
    Hello JamesBrooks,
    not sure you have already fix that, but looks to me this problem is really addressed to privilege authorization. Check the user your application is using, maybe this application user doesn't have the necessary authority to access the sequence.

    Regards,
    Carlos.