-1

I am working around a workaround to a "feature" in IBM DB2.

This fancy database has a "feature" in it which if I try to use a CREATE TABLE statement and it doesn't find the schema, it will create this schema for me, even if I don't want it to. This bug has caused me a lot of hours in debugging, because my code right now exists with the expectation that it won't create the schema if it doesn't exist

My question is -- how do I change the permissions of a particular schema (or even during the create schema phase) which a particular user does not have access to view?

I checked out this doc..

It seems with GRANT, there are the following three permissions:

ALTERIN Grants the privilege to alter or comment on all objects in the schema. The owner of an explicitly created schema automatically receives ALTERIN privilege.

CREATEIN Grants the privilege to create objects in the schema. Other authorities or privileges required to create the object (such as CREATETAB) are still required. The owner of an explicitly created schema automatically receives CREATEIN privilege. An implicitly created schema has CREATEIN privilege automatically granted to PUBLIC.

DROPIN Grants the privilege to drop all objects in the schema. The owner of an explicitly created schema automatically receives DROPIN privilege

With only ALTERIN, CREATEIN, and DROPIN, I don't see anything relevant to view access permissions :/

EDIT:

I checked out our Dash DB database for this particular table which has these special permissions for particular users using the following SQL:

SELECT * FROM SYSIBMADM.PRIVILEGES WHERE OBJECTSCHEMA = 'FAKE_SCRATCH';

This is the result:

enter image description here

EDIT 2:

I tried the following to emulate Dash DB's permissions for that user for that schema:

GRANT ALTERIN, CREATEIN, DROPIN ON SCHEMA FAKE_SCRATCH TO USER TEST_USER;

enter image description here

Still doesn't work :/

theGreenCabbage
  • 5,197
  • 19
  • 79
  • 169
  • It's all a matter of setting up permissions properly for your database -- [IMPLICIT_SCHEMA](http://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.sec.doc/doc/c0005525.html) -- but then you'd complain it's too restrictive. – mustaccio Nov 15 '16 at 00:47
  • @mustaccio thanks for this link! So I guess these `RESTRICTIVE` permissions can only be done in schema creation phase? – theGreenCabbage Nov 15 '16 at 00:49
  • No, you can only specify `RESTRICTIVE` when you create a database. In an existing database you should revoke `IMPLICIT_SCHEMA` from `PUBLIC` (and don't let your applications connect as a user with the DBADM privilege). – mustaccio Nov 15 '16 at 03:29
  • @mustaccio thanks again for the help. I checked the article you shared and even the accompanied link, but I couldn't really see where it shows us how to revoke `IMPLICIT_SCHEMA` from `PUBLIC`? Also, how would I not let the application connect as a user with the DBADM privilege? If you can write this as an answer, I would immediately mark you as answer! – theGreenCabbage Nov 15 '16 at 04:15
  • You revoke a privilege with a `REVOKE` statement, and you don't allow applications to connect by not giving away the password for authorization IDs that you want to protect. – mustaccio Nov 15 '16 at 12:59
  • @mustaccio Just wanted to clarify -- we should revoke `IMPLICIT_SCHEMA` on non-admin users right? I tried revoking it for both a regular user and admin user (`REVOKE PUBLIC`), and the admin user would still be able to create schemas implicitly – theGreenCabbage Nov 16 '16 at 00:28

1 Answers1

0

The following SQL query executed in DB2 fixed the problem:

REVOKE IMPLICIT_SCHEMA ON DATABASE FROM PUBLIC
theGreenCabbage
  • 5,197
  • 19
  • 79
  • 169