Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2016
    Posts
    29

    Unanswered: How to REVOKE access to data in table to instance on db210.5

    Hello DB2 DBA's,

    I have a requirement to block/REVOKE access of data for few tables on db2 instance. is it possible ?

    I tried to test " REVOKE DATAACCESS ON DATABASE FROM USER db2inst1" but it got failed
    SQL0555N An authorization ID cannot revoke a privilege from itself

    version 10.5

    your suggestions please.

  2. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    306
    Provided Answers: 45
    Hello,

    According to the REVOKE (database authorities) statement, you have to find an eligible user for such a statement.
    Use the following statement to find such a user:
    Code:
    select granteetype, grantee from syscat.dbauth where securityadmauth='Y' and not (grantee='DB2INST1' and granteetype='U')
    Connect to the database with such a user and run your statement.
    Regards,
    Mark.

  3. #3
    Join Date
    Apr 2016
    Posts
    29
    Thanks Mark,

    yes it worked, i have connected to the database with the other user which is have privileges to REVOKE and did it.

    And i want to ask you 2 questions.

    1) How can i revoke only for few tables on the database (example i have 100 tables, and i don't want access to db2inst1 for 30 tables) ?

    2) If i revoke DATAACCESS on DB, will able to do maintenance activities like backup,restore and reorg ? without any issue ?

  4. #4
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    306
    Provided Answers: 45
    Table access privileges are listed in the SYSCAT.TABAUTH system view.
    User groups, roles, users can have table privileges.

    Sometimes it can be a little bit complicated.
    Your user may belong to some of these groups or can be granted some these roles. So, you may need to exclude this user from some groups to retain the privileges for other members of these groups...
    Regards,
    Mark.

Tags for this Thread

Posting Permissions

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