CODEWITHSUNDEEP
databasedb2
SRS
SRS

Reputation: 449

How to revoke user access to database metadata tables in DB2

I am trying to revoke access to a user (be it a testUser or db2instance user) from getting the metadata information of a database using the Sysibm schema. If I run the following query

SELECT NAME, COLTYPE, LENGTH FROM SYSIBM.SYSCOLUMNS WHERE TBNAME = 'tableName';

I get the information on the table I had passed in the query. What can be done to restrict this access to a user?

I ran the 

REVOKE ALL ON TABLE SYSIBM.SYSCOLUMNS FROM user;

and received the following error: 

com.ibm.db2.jcc.am.SqlSyntaxErrorException: An attempt to revoke a privilege, security label, exemption, or role from "USER" was denied because "USER" does not hold this privilege, security label, exemption, or role.. SQLCODE=-556, SQLSTATE=42504

Even with db2instance user, I get the same error message. However, the select statement gives me the correct result though.

I believe the Sysibm is where all the database related information is stored. I want to restrict access to that so that the user cannot get the metadata about my database.

Upvotes: 0

Views: 1305

Answers (2)

Mark Barinstein
Mark Barinstein

Reputation: 12314

Starting from 10.5.5 you are able to CREATE PERMISSION on system catalog tables. This gives you an ability to "hide" some objects for particular users, but you need to code the logic yourself.

It may be, for example, something like this: a user can see the table definition if it has any table privilege or has DATAACCESS or ACCESSCTRL on the database...

Upvotes: 0

Paul Vernon
Paul Vernon

Reputation: 3901

From https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.sec.doc/doc/c0054269.html

In a non-restrictive database, the special group PUBLIC is granted the following privileges:

SELECT on all SYSCAT and SYSIBM tables

(among other things. See the manual page for the full list)

So you need to REVOKE access from PUBLIC

You might want to read the article that is linked from the above page too. A practical guide to restrictive databases

Upvotes: 3

Related Questions