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=""USER" does not have the required authorization or privilege to perform operation "USAGE" on object "DFLT_TR.SEQ_TABLE_KEY".. 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?
Pinned topic connect to db user x doesn't work?
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2011-09-28T12:53:35Z at 2011-09-28T12:53:35Z by CarlosLyrio
CarlosLyrio 270000E3DX1 Post
Re: connect to db user x doesn't work?2011-09-28T12:53:35ZThis is the accepted answer. This is the accepted answer.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.