Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jul 2009
    Posts
    6

    Unanswered: Unable to restrict user access

    Running DB2 v9.105 FP 5 on AIX

    I'm trying to learn about controlling user access at a table level but things aren't working the way I expect.

    I've created a new database - DBACTION - with 2 tables - general_access and restricted_access. The goal is to allow user HIREM to access general_access but not restricted_access.

    I've revoked all database level privileges from PUBLIC - my id, WHOUSE, is the only entry that appears in the DBAUTH table. I've not granted any privileges to user HIREM for table restricted_access - I verified in the tabauth table that there is no entry for that table for that user (or for PUBLIC).

    So - based on that - I would expect HIREM not to be able to access the database, and even if he could he shouldn't be able to access the restricted_access table.

    However, not only can he connect to the database, he has full access to the table.

    What am I missing???

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I am assuming that the user is not the instance owner.

    See if the user belongs to the SYSADM group (as defined in AIX) as specified in the dbm config:
    db2 get dbm cfg
    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
    Nov 2005
    Location
    IL
    Posts
    557
    As Marcus already pointed out please provide output for the following.

    db2 get dbm cfg | grep -i 'group name'

    lsuser hirem ------- make sure that (hirem) is low case

    db2 "select * from syscat.dbauth where GRANTEE in (list UNIX groups and this id that this user belongs in here separated by comma and in single quate)"


    db2 "select * from syscat.tabauth where GRANTEE in (list UNIX groups and this id that this user belongs in here separated by comma and in single quate)"

    Last sql can be modified to only run for those particular tables.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Code:
    db2 connect to dbname
    db2 get authorizations
    will give you some of this information


    Administrative Authorizations for Current User

    Direct SYSADM authority = NO
    Direct SYSCTRL authority = NO
    Direct SYSMAINT authority = NO
    Direct DBADM authority = NO
    Direct CREATETAB authority = NO
    Direct BINDADD authority = NO
    Direct CONNECT authority = NO
    Direct CREATE_NOT_FENC authority = NO
    Direct IMPLICIT_SCHEMA authority = NO
    Direct LOAD authority = NO
    Direct QUIESCE_CONNECT authority = NO
    Direct CREATE_EXTERNAL_ROUTINE authority = NO
    Direct SYSMON authority = NO

    Indirect SYSADM authority = YES
    Indirect SYSCTRL authority = NO
    Indirect SYSMAINT authority = NO
    Indirect DBADM authority = NO
    Indirect CREATETAB authority = NO
    Indirect BINDADD authority = NO
    Indirect CONNECT authority = NO
    Indirect CREATE_NOT_FENC authority = NO
    Indirect IMPLICIT_SCHEMA authority = NO
    Indirect LOAD authority = NO
    Indirect QUIESCE_CONNECT authority = NO
    Indirect CREATE_EXTERNAL_ROUTINE authority = NO
    Indirect SYSMON authority = NO
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Jul 2009
    Posts
    6
    Quote Originally Posted by Cougar8000
    As Marcus already pointed out please provide output for the following.

    db2 get dbm cfg | grep -i 'group name'
    $ db2 get dbm cfg |grep -i 'group name'
    SYSADM group name (SYSADM_GROUP) =
    SYSCTRL group name (SYSCTRL_GROUP) =
    SYSMAINT group name (SYSMAINT_GROUP) =
    SYSMON group name (SYSMON_GROUP) =


    Quote Originally Posted by Cougar8000
    lsuser hirem ------- make sure that (hirem) is low case>
    $ lsuser hirem
    hirem id=1101 pgrp=ext_mast groups=ext_mast,staff home=/home/ext_users/hirem shell=/usr/bin/ksh gecos=test id registry=compat


    $ db2 "select * from syscat.dbauth where GRANTEE in ('HIREM','EXT_MAST','STAFF')"
    0 record(s) selected.


    $ db2 "select * from syscat.tabauth where TABNAME='RESTRICTED_ACCESS' and GRANTEE in ('HIREM','EXT_MAST','STAFF')"
    0 record(s) selected.

    Quote Originally Posted by sathyaram_s
    Code:
    db2 connect to dbname
    db2 get authorizations
    will give you some of this information
    db2 get authorizations gave me this -
    $ db2 get authorizations

    Administrative Authorizations for Current User

    Direct SYSADM authority = NO
    Direct SYSCTRL authority = NO
    Direct SYSMAINT authority = NO
    Direct DBADM authority = NO
    Direct CREATETAB authority = NO
    Direct BINDADD authority = NO
    Direct CONNECT authority = NO
    Direct CREATE_NOT_FENC authority = NO
    Direct IMPLICIT_SCHEMA authority = NO
    Direct LOAD authority = NO
    Direct QUIESCE_CONNECT authority = NO
    Direct CREATE_EXTERNAL_ROUTINE authority = NO
    Direct SYSMON authority = NO

    Indirect SYSADM authority = YES
    Indirect SYSCTRL authority = NO
    Indirect SYSMAINT authority = NO
    Indirect DBADM authority = NO
    Indirect CREATETAB authority = NO
    Indirect BINDADD authority = NO
    Indirect CONNECT authority = NO
    Indirect CREATE_NOT_FENC authority = NO
    Indirect IMPLICIT_SCHEMA authority = NO
    Indirect LOAD authority = NO
    Indirect QUIESCE_CONNECT authority = NO
    Indirect CREATE_EXTERNAL_ROUTINE authority = NO
    Indirect SYSMON authority = NO
    So, it looks to me like that Indirect SYSADM authority could be the root of my problem. Now I just need to find out what that even means.

    Thanks for the help so far.

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote from the manual :

    For UNIX-based systems, if "NULL" is specified as the value of this parameter, the SYSADM group defaults to the primary group of the instance owner.
    What is the instance name and which group is the primary group of the instance owner ?

    lsuser <instance name>
    and look for pgrp ..
    I guess this will be one of ext_mast,staff or ext_mast
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    When you ran sathyaram command, were you logged in as inst owner or your id? do lsuser on both inst and your id.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  8. #8
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Quote Originally Posted by sathyaram_s
    Quote from the manual :



    What is the instance name and which group is the primary group of the instance owner ?
    I might be wrong, but I think that is only true if you explicitly set SYSADM to NULL. If you do not set it, then only inst id can do SYSADM bidding.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  9. #9
    Join Date
    Jul 2009
    Posts
    6
    Instance name is whouse.

    I ran the 'db2 get authorizations' from the hirem user.

    >lsuser whouse
    whouse id=250 pgrp=ext_mast groups=ext_mast,dasadm1 home=/whouse shell=/bin/ksh gecos=Whouse user id registry=compat roles=

    >lsuser hirem
    hirem id=1101 pgrp=ext_mast groups=ext_mast,staff home=/home/ext_users/hirem shell=/usr/bin/ksh gecos=test id registry=compat

  10. #10
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Hmm, maybe sathyaram was right about NULL. Never had to deal with it as I like to set it and forget it and not leave anything to a chance.

    Bigger question why is your end user is in the same UNIX group as your inst owner ID? That is a HUGE NONO.

    Try setting your admin to dasadm1 unix group and then check if your user still has access.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  11. #11
    Join Date
    Jul 2009
    Posts
    6
    Changed the SYSADM group from EXT_MAST to DASADM1 -

    run from instance owner 'whouse'
    >db2 get dbm cfg |grep -i 'group name'
    SYSADM group name (SYSADM_GROUP) = DASADM1
    SYSCTRL group name (SYSCTRL_GROUP) =
    SYSMAINT group name (SYSMAINT_GROUP) =
    SYSMON group name (SYSMON_GROUP) =

    run from user 'hirem' still shows -
    $ db2 get dbm cfg |grep -i 'group name'
    SYSADM group name (SYSADM_GROUP) =
    SYSCTRL group name (SYSCTRL_GROUP) =
    SYSMAINT group name (SYSMAINT_GROUP) =
    SYSMON group name (SYSMON_GROUP) =

    And hirem can still query that table.

  12. #12
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You will have to stop and start the instance for this group name change to be effective.

    I would actually setup a different group - say db2adm or something like that for the db2 instance(s).
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  13. #13
    Join Date
    Jul 2009
    Posts
    6
    You're right, stop and start is important.

    That worked!

    And now the grants and revokes are working the way I expected them to.

    I totally agree with setting up a db2adm group, I'll have to talk to the sys admin and work that out. This was all proof of concept before implementing it on the real db.

    Thanks for all your help!

  14. #14
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Make sure to create a stand alone group just for DBA's and inst id. Then set up a daily script to monitor for intrusion to this group. Do not ask me how I learned that this is a MUCH needed step.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  15. #15
    Join Date
    Jul 2009
    Posts
    6
    OK, so now I've encountered my next problem - politics.

    Boss says 'Yeah, well we can't really make changes like that. I need you to restrict access to these users but keep them in the SYSADM_GROUP.'

    I feel like I'm in a Dilbert strip today.

Posting Permissions

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