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

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

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

Thanks,
Evan


"Harold Lee" <haroldl@nospamplease.us.ibm.com> wrote in message
news:c4scam$1isk$1@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
>

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...