Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2012
    Posts
    2

    Unanswered: check if the tables have select privileges for a user in db2

    hi,

    I'm trying to execute a db2 query from unix shell script to find the all the schemas in the database which has a table - TAB_A in those schema and select permission for my user id on the table on that schema.

    ie, i would pass arguments table name (TAB_A) and user id (matt02) and the select query should fetch me all the schemas which has a TAB_A and also have select privileges for matt02 on TAB_A.

    thanks
    matt

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    DB2 does not authorize by schema, only by table, or the entire database for certain athorities like DBADM. You can find the table authorities in SYSCAT.TABAUTH catalog view. Database level authorities are in SYSCAT.DBAUTH. To find the list of all tables, independent of authorizations granted, you would look in SYSCAT.TABLES (but check the table "type" since it includes other objects such as views, etc).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Sep 2013
    Posts
    4
    db2 look -d dbname -z schemaname -t <tablname> -a -e -l -x -o >output.txt

    you can find what privileges are there to users on that table.

  4. #4
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Don't forget that your user-account might inherit permissions (on tables or other objects) via ROLES or GROUP memberships , in addition to direct GRANT to users.
    So if your site uses ROLES then for tables/views, you also need to check syscat.roleauth (keeping in mind that roles can be nested) in addition to syscat.tabauth.
    Similarly if your user-account is a member of multiple groups then you need to check for each of those group-names in the relevant *auth views.

Posting Permissions

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