Hi All - DB2 8.1.5 on AIX 5.2.0.0

I'm having trouble with assigning select privileges to
groups in my enviroment.

Its no problem creating the groups, assigning the privileges and
puttinng users into the groups

But it seems like the database doesn't scan for privileges
assigned to a persons group, only those assigned individually

I know it sounds strange, but I've tested it a dozen times.

Has anyone experienced anything similar ?

we are using this for a long time
creating groups at os level / attach user to this group
only grants are given to groups..
grant select on xx.yy to group zzz

Did you
- revoke all authorities from PUBLIC (what public can do, everybody can do)
- set the authentication to server (and not to client ?)

My settings on the DBM:

Database manager authentication (AUTHENTICATION) = SERVER
Cataloging allowed without authority (CATALOG_NOAUTH) = NO
Trust all clients (TRUST_ALLCLNTS) = YES
Trusted client authentication (TRUST_CLNTAUTH) = CLIENT
Bypass federated authentication (FED_NOAUTH) = NO

And all public priviliges are on objects not related to this user

I know it sounds crazy and you most likely already checked on it, but you might want to double check that those users are in those groups? We have never incountered such a problem. We DO NOT give access to PUBLIC nor to individual id's, only group access.

I have checked, look at the attached files:

in 'dwlprk gruppen' you can see that user 'sta' belongs to the group 'dwlprk'

in 'dwlprk privileges' you can see the group 'dwlprk' having select
privileges on the view 'lpr.v_adm1977'

But then 'sta refused' speaks for itself !!

Somehow I don't trust those fancy GUI things.

Can you check that "grep dwlprk /etc/group" indeed shows you "sta" as a member?

What does "select * from syscat.tabauth where tabschema='LPR' and tabname='V_ADM1977'" give you?

I've checked both (before) but here are the results:

db2inst2@viggo:/home/db2inst2> lsgroup DWLPRK
DWLPRK id=285 admin=false users=sta adms=jej,db2inst2 registry=files

and:

DB2INST2 | DWLPRK | G | LPR | V_ADM1977 | N N N N N Y N N

where the Y is yes to selectauth.

I'm thoroughly mystified - I think I'll call IBM, thanks for trying guys.

Are these errors in Static SQL ? eg. Stored Procedures ? or are these in dynamic SQL eg. Command window. ?

Cheers
Sathyaram

The documentation says :

GROUP privileges are not checked for static INSERT statements.
GROUP privileges are not checked for static SELECT INTO statements.

But notes, these SIUD privleges for tables referred in static statements need to be given to users who bind the packages (or create SPs). Once the packages are created, a group can be grant EXECUTE permission on the package and that will not be a problem.

I guess you will face this issue primarily in Dev environment ..

HTH

Cheers
Sathyaram

Hi Sathyaram

No it works fine in static (SP's)

It doesn't work dynamically

If you guys are interested

It turns out its a reverse uppercase problem.

If you assign privileges on windows to a group these
are upcased - no problem, I knew that and thus had created my
group on the Backend AIX with capitals.

But DB2 doesn't seem to grasp this.

If you create the groups with lowercase names on the backend -
the whole thing works. Even though the privileges and the group checks
are automatically upcased ???!!!

I'm pretty sure it's a bug

But at least it works now.

It is always a little guy problem

Glad you figured it out.