0

I'm running DB2 11.5 and want to grant select, insert, update or delete rights for all tables in a specific schema. SELECTINAUTH etc. seems to be perfect for this.

When I'm looking to the SYSCAT.SCHEMAAUTH Catalog View I can see the following columns:

  • ALTERINAUTH
  • CREATEINAUTH
  • DROPINAUTH
  • SELECTINAUTH
  • INSERTINAUTH
  • UPDATEINAUTH
  • DELETEINAUTH

The first 3 privileges can be granted by e.g. GRANT ALTERINAUTH ON SCHEMA TO USER . But this don't work for the last 4 columns.

The Knowledge Center tells that this are implicit privileges, but I can't find any hint, how a user or a group can get this privileges implicit or explicit.

Does anybody know when or how e.g. INYSERTINAUT is set to Y?

  • 1
    This functionality isn't out yet. – mustaccio Jul 28 '20 at 14:50
  • At the database level you can grant data access, but that is regardless of schema – Lennart Jul 28 '20 at 15:15
  • 1
    It's available as a technical preview at the moment with 11.5. You should ask your IBM rep on how to enable it, if you are interested. It should work as it works with Db2 Warehouse now. GRANT (schema privileges) statement. – Mark Barinstein Jul 28 '20 at 15:31
  • Thanks to everyone for your answers. I already reached out to IBM support, but didn't get any response yet. @MarkBarinstein: Thank you for the link. I will add it to my support ticket. It should help IBM to find the answer. :-) – Cyberider Jul 30 '20 at 7:17
  • Tell them, that it's some new db2 registry variable controlling this functionality. – Mark Barinstein Jul 30 '20 at 7:29
1

I got the following information from IBM: "So currently the usage of SELECTINAUTH, INSERTINAUTH, UPDATEINAUTH is only available in DB2 Warehouse" and "Schema authorization is one of these features and we have publicly stated (at IDUG conferences etc) that we will be brought to Db2 in an update to 11.5.".

It can be enabled with db2set DB2_ENABLE_SCHEMA_AUTHORIZATION=true, but it's not documented and therefore not supported yet. (as IBM explained).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.