GRANT statement gives authorization id not valid error

Jatinder

Hi,

I am trying to grant connect privilege to a user present on my O.S.
(Windows) using following statement.

GRANT CONNECT ON DATABASE TO user "user1"

now when I execute this statemnt from db2 clp(ofcourse prefixing db2)
it works fine, but when I put it inside an sql file and then when I try
to run the file using db2 -tvf, it gives me following error msg
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0567N "user1" is not a valid authorization ID. SQLSTATE=42602

I also tried running the above statemnt from db2 command line
interactive mode, it also results in above error.
I intend to execute above statement programmatically, hence I need to
use sql file.

Any solutions?

Sep 11 '06 #1

Knut Stolze

Jatinder wrote:

Hi,

I am trying to grant connect privilege to a user present on my O.S.
(Windows) using following statement.

GRANT CONNECT ON DATABASE TO user "user1"

now when I execute this statemnt from db2 clp(ofcourse prefixing db2)
it works fine, but when I put it inside an sql file and then when I try
to run the file using db2 -tvf, it gives me following error msg
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0567N "user1" is not a valid authorization ID. SQLSTATE=42602

I also tried running the above statemnt from db2 command line
interactive mode, it also results in above error.

Remove the double quotes. Those quotes cause DB2 to take the user name as
case-sensitive string. If you execute it on the db2 clp (i.e. an operating
system shell), the shell interprets the double quotes and DB2 never sees
them.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Sep 11 '06 #2

Jatinder

Without quotes works, however the user name has a space in the name,
now how do I handle this if I am not to use double quotes? I tried
single quote, but it gives
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "'user1'" was found following "on
database to
user". Expected tokens may include: "END-OF-STATEMENT".
SQLSTATE=42601

Knut Stolze wrote:

Jatinder wrote:

Hi,

I am trying to grant connect privilege to a user present on my O.S.
(Windows) using following statement.

GRANT CONNECT ON DATABASE TO user "user1"

now when I execute this statemnt from db2 clp(ofcourse prefixing db2)
it works fine, but when I put it inside an sql file and then when I try
to run the file using db2 -tvf, it gives me following error msg
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0567N "user1" is not a valid authorization ID. SQLSTATE=42602

I also tried running the above statemnt from db2 command line
interactive mode, it also results in above error.

Remove the double quotes. Those quotes cause DB2 to take the user name as
case-sensitive string. If you execute it on the db2 clp (i.e. an operating
system shell), the shell interprets the double quotes and DB2 never sees
them.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Sep 11 '06 #3

Knut Stolze

Jatinder wrote:

Without quotes works, however the user name has a space in the name,

Fold the name to all upper case, then. This is what's happening w/o quotes.
Since you need those quotes because of the special characters, you have to
perform that step yourself.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Sep 11 '06 #4

Jatinder

It works after I have all the characters in double quotes as uppercase.
Thanks!

Jatinder
Knut Stolze wrote:

Jatinder wrote:

Without quotes works, however the user name has a space in the name,

Fold the name to all upper case, then. This is what's happening w/o quotes.
Since you need those quotes because of the special characters, you have to
perform that step yourself.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Sep 11 '06 #5