Unanswered: how to prevent user to see tables belong to another users
My db2 version is 10.5 (on linux)
My customer ask me to create a user with one table only .
I have to prevent from this user the ability to see any another table under any another schema .
For examp : when i connected the db with this user :
list tables for all - return just the table under this user .
select * from syscat.tables -same thing .
db2look - same thing
it is possible ?
Your are not able to do so.
What you can do is to revoke all privileges on the system catalog tables and views from this user and create your own set of views like:
create or replace view MYSYSCAT.TABLES as select * from syscat.tables where tabschema=USER
create or replace view MYSYSCAT.COLUMNS as select * from syscat.columns where tabschema=USER
The main problem here is that you can not tell other applications and utilities that they must use this new schema MYSYSCAT to show the objects metadata instead of standard SYSCAT schema.
But your user can query these views directly (if you grant this user an appropriate access on them, of course) to get metadata.
FROM TABLE(AUTH_LIST_AUTHORITIES_FOR_AUTHID('USERB', 'U'))
P.PRIVILEGE, P.OBJECTTYPE, P.OBJECTSCHEMA, P.OBJECTNAME
, U.AUTHID, U.AUTHIDTYPE
FROM SYSIBMADM.PRIVILEGES P
CROSS JOIN TABLE(VALUES 'USERB') A (AUTHID)
JOIN TABLE (
SELECT GROUP, 'G' FROM table(AUTH_LIST_GROUPS_FOR_AUTHID(A.AUTHID))
select ROLENAME, 'R' from table(AUTH_LIST_ROLES_FOR_AUTHID(A.AUTHID, 'U'))
SELECT * FROM TABLE(VALUES ('PUBLIC', 'G'), (A.AUTHID, 'U')) T (AUTHID, AUTHIDTYPE)
) U (AUTHID, AUTHIDTYPE) ON U.AUTHID=P.AUTHID AND U.AUTHIDTYPE=P.AUTHIDTYPE
WHERE P.OBJECTSCHEMA = 'SYSCAT' AND P.OBJECTNAME = 'TABLES';