Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2010
    Posts
    56

    Unanswered: how to prevent user to see tables belong to another users

    Hi,
    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
    etc' .
    it is possible ?

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi Elin,

    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:
    Code:
    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.
    Regards,
    Mark.

  3. #3
    Join Date
    Oct 2010
    Posts
    56
    Hi Mark, Pls see the attachement :
    I tried to tevoke select on syscat.tables from usrerb ,but the user still have the ability to select from syscat.tablesClick image for larger version. 

Name:	revoke.PNG 
Views:	4 
Size:	45.8 KB 
ID:	16910

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi Elin,

    Your user may have these privileges implicitly via, let's say, group PUBLIC or having DBADM authority on the datadase.
    Try to revoke these privileges from group PUBLIC.
    Regards,
    Mark.

  5. #5
    Join Date
    Oct 2010
    Posts
    56
    Hi Mark,
    Very strange ..
    The user doesn't have dbadm privilage, and public doesn't have any privilage on syscat.tables.
    But still, my user can select from syscat.tables ..

  6. #6
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Elin,

    What's the result of the following queries?

    Code:
    SELECT * 
    FROM TABLE(AUTH_LIST_AUTHORITIES_FOR_AUTHID('USERB', 'U'))
    WHERE AUTHORITY='DATAACCESS';
    
    SELECT  
      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))
      UNION ALL
    select ROLENAME, 'R' from table(AUTH_LIST_ROLES_FOR_AUTHID(A.AUTHID, 'U'))
      UNION ALL
    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';
    Regards,
    Mark.

  7. #7
    Join Date
    Oct 2010
    Posts
    56
    Mark,
    Many thanks for you !!!
    I see now that staff group has the selrct permission.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •