By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,846 Members | 2,094 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,846 IT Pros & Developers. It's quick & easy.

CREATE USER MAPPING Problem

P: n/a
I have a federated database (7.2 FP11, AIX 4.3) that seems to work
fine in most aspects. Today I'm trying to add a new user mapping with
the following statement:

create user mapping for mngdrq01 server opencases options
(remote_authid 'mngdrq01', remote_password 'xxxxx')

Querying the federated database works fine as long as the user
mentioned above is part of the sysadm group or has dbadm authority. As
soon as those authorities are removed, the federated database gives me
an error that said user ID has no select permissions.

As the remote database is also a UDB database on the same physical
machine, I have validated that the user ID does have select
permissions when logging directly into the remote database.

The documentation for CREATE USER MAPPING mentions that if the auth
name and the auth ID are different, then SYSADM or DBADM is required,
but if they are the same, then no privileges are required. Am I
misinterpreting this statement?

I don't want this particular user to have either DBADM or SYSADM, just
select privileges. Has anyone else been able to get this to work
without either of the admin privileges?

Thanks,
Evan
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Perhaps you need to grant select to the non-sysadm/non-dbadm user on the
nickname itself in the federated database.

-Harold

Evan Smith wrote:
I have a federated database (7.2 FP11, AIX 4.3) that seems to work
fine in most aspects. Today I'm trying to add a new user mapping with
the following statement:

create user mapping for mngdrq01 server opencases options
(remote_authid 'mngdrq01', remote_password 'xxxxx')

Querying the federated database works fine as long as the user
mentioned above is part of the sysadm group or has dbadm authority. As
soon as those authorities are removed, the federated database gives me
an error that said user ID has no select permissions.

As the remote database is also a UDB database on the same physical
machine, I have validated that the user ID does have select
permissions when logging directly into the remote database.

The documentation for CREATE USER MAPPING mentions that if the auth
name and the auth ID are different, then SYSADM or DBADM is required,
but if they are the same, then no privileges are required. Am I
misinterpreting this statement?

I don't want this particular user to have either DBADM or SYSADM, just
select privileges. Has anyone else been able to get this to work
without either of the admin privileges?

Thanks,
Evan


Nov 12 '05 #2

P: n/a
You, sir, were correct. I had never granted permissions on a nickname
before. But this is all it took.

Thanks,
Evan
"Harold Lee" <ha*****@nospamplease.us.ibm.com> wrote in message
news:c4***********@gazette.almaden.ibm.com...
Perhaps you need to grant select to the non-sysadm/non-dbadm user on the
nickname itself in the federated database.

-Harold

Evan Smith wrote:
I have a federated database (7.2 FP11, AIX 4.3) that seems to work
fine in most aspects. Today I'm trying to add a new user mapping with
the following statement:

create user mapping for mngdrq01 server opencases options
(remote_authid 'mngdrq01', remote_password 'xxxxx')

Querying the federated database works fine as long as the user
mentioned above is part of the sysadm group or has dbadm authority. As
soon as those authorities are removed, the federated database gives me
an error that said user ID has no select permissions.

As the remote database is also a UDB database on the same physical
machine, I have validated that the user ID does have select
permissions when logging directly into the remote database.

The documentation for CREATE USER MAPPING mentions that if the auth
name and the auth ID are different, then SYSADM or DBADM is required,
but if they are the same, then no privileges are required. Am I
misinterpreting this statement?

I don't want this particular user to have either DBADM or SYSADM, just
select privileges. Has anyone else been able to get this to work
without either of the admin privileges?

Thanks,
Evan

Nov 12 '05 #3

P: n/a
Evan Smith wrote:
You, sir, were correct. I had never granted permissions on a nickname
before. But this is all it took.
Perhaps you need to grant select to the non-sysadm/non-dbadm user on the
nickname itself in the federated database.


Some of the 7.x documentation was a bit confused on this point, as I
recall. To make matters worse, the db2cc from that period of time had
no way to set privileges on nicknames either.

My head still has a flat spot from banging it against the wall...
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.