I'm not sure here, but I'm going to go out on a limb. I think your issue might be with your DENY CONTROL
record. See here about half way down the page:
Denying CONTROL permission on a database implicitly denies CONNECT permission on the database. A principal that is denied CONTROL permission on a database will not be able to connect to that database.
I realize that example is for a database, but take it one more granual level. A DENY CONTROL
on a table will deny all privileges on it, I'm guessing. Do a REVOKE CONTROL
to get rid of that and see if that fixes your issue.
If so, you'll have to place the user in a database role or deny them the explicit privileges against the table.
@Chris Aldrich has given a good explanation. I will just add a few things here.
1) There is no concept of a "database user" in DB2. All authentication happens outside the database or instance, in the operating system. Also, there is no direct relationship between a user ID and a schema name, unlike in Oracle. In DB2 a schema is just a logical grouping of objects, it does not have any special security features. Any user can create any schema. For example, while logged in as mustaccio I run the statement create table foo (id int...)
, and that creates a schema MUSTACCIO (if it's not already there) and a table in it. As you see, the schema name resolution defaults to my authorization ID. However, I might as well run the statement create table alok.foo (id char(3)...)
, which in this case creates a schema ALOK and the table in it. mustaccio will be the owner of both tables.
2) Regarding the user ID mapping, I would probably say that the DAS owner dasusr1 and the fenced user db2fenc1 do not map to anything in an Oracle database. The instance owner db2inst1 maps to the oracle user ID. Whoever creates a database (might be db2inst1 or some other user authorized to do that by membership in the SYSADM group, for example) obtains DBADM and SECADM privileges in that database, which is somewhat similar to being system and/or sys (I'm not really sure what is the distinction between the two in an Oracle database). If you need a functional ID that owns database objects, you create appowner in the operating system, grant to it appropriate permissions, and connect to that user when creating objects. Similarly, you create appuser in the operating system, grant object access privileges to it, and let your application connect as that user.
3) Since there are no database users in DB2, you cannot drop a user. To delete objects in a particular schema you can use the ADMIN_DROP_SCHEMA() procedure.
Best Answer
Select privileges on all
SYSCAT
objects are granted by default to thePUBLIC
pseudo-group (unless the database is created as "restrictive"), so you shouldn't have ended up with users not being able to accesssyscat.schemata
unless you or someone before you chose to actively prevent that.Users will be able to discover what schemas exist in your database. Obviously this doesn't give them any extra privileges to see or access in any way objects in those schemas. In other words, being able to read
syscat.schemata
is pretty harmless.