restrict privileges

[Frank Swarbrick]

I feel like an idiot for asking this, but neither I nor my DBA can figure
this out.
How do you create a database object that does not implicitly allow access by
any defined user?

For instance, I have a user who has been granted only the CONNECT privilege
to a certain database. But he is still able to do selects and updates and
other things to tables created by another user. Everything I've read
indicates this should not be allowed; that the user must be granted these
privileges explicitly. What are we missing?

================================================== ==========
About DB2 Administration Tools Environment
================================================== ==========
DB2 administration tools level:
Product identifier SQL09050
Level identifier 03010107
Level DB2 v9.5.0.808
Build level s071001
PTF NT3295
================================================== ==========
Java development kit (JDK):
Level IBM Corporation 1.5.0
================================================== ==========
Here's some more things that show what I am attempting:
connect to securedb user fswarbri using

Database Connection Information

Database server = DB2/NT 9.5.0
SQL authorization ID = FSWARBRI
Local database alias = SECUREDB
SELECT * FROM SYSIBMADM.PRIVILEGES WHERE OBJECTNAME = 'SECTEST1'

AUTHID AUTHIDTYPE PRIVILEGE GRANTABLE OBJECTSCHEMA OBJECTNAME OBJECTTYPE

------------------ ----------- --------- ------------ ---------- ----------
OPSUSER U UPDATE Y OPSUSER SECTEST1 TABLE

OPSUSER U REFERENCE Y OPSUSER SECTEST1 TABLE

OPSUSER U SELECT Y OPSUSER SECTEST1 TABLE

OPSUSER U INSERT Y OPSUSER SECTEST1 TABLE

OPSUSER U INDEX Y OPSUSER SECTEST1 TABLE

OPSUSER U DELETE Y OPSUSER SECTEST1 TABLE

OPSUSER U ALTER Y OPSUSER SECTEST1 TABLE

OPSUSER U CONTROL N OPSUSER SECTEST1 TABLE
8 record(s) selected.
SELECT * FROM OPSUSER.SECTEST1

COL1
--------------------
123
456
987

3 record(s) selected.

As you can see, only OPSUSER has any privileges on OPSUSER.SECTEST1, and yet
user FSWARBRI is able to query on the table (and update it, for that
matter).

Thanks!

Frank

[jefftyzzer]

On Mar 4, 12:02 pm, "Frank Swarbrick" <[e-mail]>
wrote:

I feel like an idiot for asking this, but neither I nor my DBA can figure
this out.
How do you create a database object that does not implicitly allow access by
any defined user?

For instance, I have a user who has been granted only the CONNECT privilege
to a certain database. But he is still able to do selects and updates and
other things to tables created by another user. Everything I've read
indicates this should not be allowed; that the user must be granted these
privileges explicitly. What are we missing?

================================================== ==========
About DB2 Administration Tools Environment
================================================== ==========
DB2 administration tools level:
Product identifier SQL09050
Level identifier 03010107
Level DB2 v9.5.0.808
Build level s071001
PTF NT3295
================================================== ==========
Java development kit (JDK):
Level IBM Corporation 1.5.0
================================================== ==========

Here's some more things that show what I am attempting:
connect to securedb user fswarbri using

Database Connection Information

Database server = DB2/NT 9.5.0
SQL authorization ID = FSWARBRI
Local database alias = SECUREDB

SELECT * FROM SYSIBMADM.PRIVILEGES WHERE OBJECTNAME = 'SECTEST1'

AUTHID AUTHIDTYPE PRIVILEGE GRANTABLE OBJECTSCHEMA OBJECTNAME OBJECTTYPE

------------------ ----------- --------- ------------ ---------- ----------
OPSUSER U UPDATE Y OPSUSER SECTEST1 TABLE

OPSUSER U REFERENCE Y OPSUSER SECTEST1 TABLE

OPSUSER U SELECT Y OPSUSER SECTEST1 TABLE

OPSUSER U INSERT Y OPSUSER SECTEST1 TABLE

OPSUSER U INDEX Y OPSUSER SECTEST1 TABLE

OPSUSER U DELETE Y OPSUSER SECTEST1 TABLE

OPSUSER U ALTER Y OPSUSER SECTEST1 TABLE

OPSUSER U CONTROL N OPSUSER SECTEST1 TABLE

8 record(s) selected.

SELECT * FROM OPSUSER.SECTEST1

COL1
--------------------
123
456
987

3 record(s) selected.

As you can see, only OPSUSER has any privileges on OPSUSER.SECTEST1, and yet
user FSWARBRI is able to query on the table (and update it, for that
matter).

Thanks!

Frank

Could it be that you need to explicitly remove/revoke this user from
the PUBLIC role?

--Jeff

[Frank Swarbrick]

Now this is interesting. The DBA went in to one of the instances running on
Linux and did the following from Control Center:
Selected the group PUBLIC
Revoked all privileges for all types of objects (specifically SCHEMA and
PACKAGES. I don't think any tables has explicit rights to any tables.)
Granted back privileges for all packages owned by NULLID.

He also had to remove the user at an OS level from a certain group
(db2admin?).

And now the user is no longer able to access tables to which that user has
not been granted privileges.

But I tried the same thing on my Windows PC running DB2 Express-C (9.5) and
it is still not enforcing the restrictions.

Huh?

Frank

[Serge Rielau]

If a user has privileges to SELECT, DELETE, UPDATE, or INSERT data
without having gotten in diractly then the access must have been granted
to either a group (or role) the user belongs to or PUBLIC.

So, take a look at that end.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Serge Rielau]

Frank Swarbrick wrote:
db2admin? Well....
If you want to hide data from dbadm you will need to use LBAC.
(or encrypt)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Frank Swarbrick]

>>On 3/4/2008 at 2:42 PM, in message
<63*************@mid.individual.net>,
Serge Rielau<sr*****@ca.ibm.comwrote:

Frank Swarbrick wrote:
db2admin? Well....
If you want to hide data from dbadm you will need to use LBAC.
(or encrypt)

No, I was saying that the db2 removed the 'restricted' user for a certain
Linux group, and I believe that group was named 'db2admins'. But I'm
probably wrong...

[Frank Swarbrick]

Wow, even odder. Now things seem to be working fine on my work PC. I am
able to restrict the PUBLIC group appropriately and grant rights to specific
users as necessary.

Maybe last night's reboot cleared things up. Who knows! Oh well. I still
have no idea what I did to cause the problem, but...

Frank