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?
NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
This topic has been locked.
1 reply Latest Post - 2011-09-28T12:53:35Z by CarlosLyrio
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 PostACCEPTED ANSWER
Re: connect to db user x doesn't work?2011-09-28T12:53:35Z in response to JamesBrooksHello 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.