Results 1 to 11 of 11

Thread: User Account

  1. #1
    Join Date
    Dec 2005
    Posts
    39

    Unanswered: User Account

    Do I need to use the syscat.dbauth table to retrieve useraccounts and groupnames in DB2 UDB for LUW? Thanks.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    DB2 uses the user accounts and groups of the operating system.
    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
    Dec 2005
    Posts
    39
    Hmm. So is there a query to retrieve those??

  4. #4
    Join Date
    Dec 2005
    Posts
    39
    I checked sysibm.sysuserauth table. Basically, I need to retrieve users, groups and privileges.

  5. #5
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    If you are looking to get a list of groups who have access on the db run this:
    db2look -d dbname -z schema -x

    This will give you all of the grants that are defined on that particular db.

    then if you need to get a list of users in those groups assuming you are on AIX run lsgroup &groupname to get the list of people in that group, or do
    cat /etc/groups
    to get the list of ALL of the groups that are defined on the os.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  6. #6
    Join Date
    Dec 2005
    Posts
    39
    Thanks! But I am looking for queries which can accomplish that.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Yes, all the authorized users and groups that have been granted DB2 access are in the catalog somewhere, and you can query it with SQL. You may have to check several of the "AUTH" tables, depending on what you are looking for.

    However, the DB2 catalog does not contain all the users and groups, just the ones who have already been granted DB2 access. You would have to issue an operating system command to get all the users and groups. You could write a C Stored Procedure to issue the OS commands.

    When you grant access to DB2 objects in the Control Center, it gets a list of users or groups from the OS.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Dec 2005
    Posts
    39
    Ok. Out of 4 users, I have 3 of them listed in syscat.tabauth and the remaining one along with 2 of those was listed in syscat.dbauth. How to get all of them? Is there something called a UNION so that I can retrieve this 1 user name from syscat.tabauth and others from syscat.dbauth??

  9. #9
    Join Date
    Dec 2005
    Posts
    39
    ok I got it. It's UNION ALL.

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    UNION is similar to UNION all, but UNION will eliminate any duplicate rows (based only on the columns you select).

    You need to look at all the syscat "AUTH" views. Some may be empty, but they could contain authorization data. They are documented in the SQL Reference VOL 1, Appendix.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Dec 2005
    Posts
    39
    Yeah. UNION eliminated all duplicate rows. syscat AUTH views? Arent those syscat.dbauth, syscat.tabauth, syscat.schemaauth primarily? I am using the first 2 to get my data.

Posting Permissions

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