Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369

    Unanswered: ID used to create db in v9.7

    On Windows:
    Do you login using the instance owner id (the user ID running the DB2 instance service - db2admin in my case) or using your personal ID when you need to create a db? This ID will become the authid for the db (as per sysibm.sysversions) and will have secadm/dbadm/etc.... I believe it should be done using db2admin.


    This is not just for Windows:
    DB2 is bundled with another IBM product, db is created during product installation. My concern is that if the developer use their personal ID to install the product (and create the db), then the instance owner won't have secadm/dbadm and developer's ID will have way too much access. And I won't be able to revoke it until the dev grants me secadm.

    What do you do in this case? Provide db2admin account to whoever creates the db (I can't create it ahead of time)? For now, I reset the pwd for db2admin and provided it to the dev.

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    I prefer to run Windows DB2-instances with domain-accounts (not local accounts).
    That gives more flexibility and control (instance can then use domain
    resources, which is easier for LOAD from network-drive, EXPORT to network drive, backup to network-disk, restore from network-drive etc.). I ensure that the domain-accounts that run instances have non-expiring passwords.

    Similarly I ensure that SYSADM_GROUP and SYSCTRL_GROUP (etc.) are domain-groups and not local groups. I ensure that only trained DBAs can get SYSADM. The domain accounts of DBAs are in the domain-group for SYSADM_GROUP (and others).
    DBAs login to Windows as themselves (never as the instance account - that can be enforced, although DBAs can use "runas" to start a db2cwadmin session as the instance account if desired ).
    So I never use db2admin.

    For controlled development environments, Developers (for development databases only) get bindadd,connect, creattab, externalroutine, implicit schema, load, sqladm, explain, accessctrl. I try to ensure that DBAs are responsible for 'create database' on any controlled environments.

    I don't restrict developers on their personal environments however (i.e. non-shared environments) only when they must use a database that's shared between two or more different users, or which can create any artefact that gets promoted to a higher environment.

    For bundled-databases, I ensure that database creation is a DBA responsibilty - and only DBAs can run the relevant scripts in controlled environments *having checked them first*.
    At the worst case you can cobble a 'transfer ownership' script if events overtake you.

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    I don't really know about domains, local accounts are ok at this time. This is just some test server for POC.

    If you login as yourself and create a db, then the db is created with your authid and you have secadm/dbadm.. Do you then grant other DBAs secadm/dbadm? On Unix/Linux, we login using our personal ID and then sudo to the instance ID and do everything as the instance owner.

    I came in this morning to find out that the developer added himself to the sysadm_group's group on Win. We're trying to setup standards and restrict access, but it's hard to do when people have local admin or root. I don't really care about this Win server, but we do want to control what's happening on another servers.

  4. #4
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    It is very much in your interest to learn how DB2 interacts with Windows-domains, and why this is important for a DB2 DBA.

    Local-groups and Local-users are subject to the control of local-administrators, domain-groups are not.

    I tend to grant to groups, not users, and those groups are always domain groups. So people who are DB2 DBAs get enrolled in domain-groups that are tied to DB2-instances.

    If you use local groups you are subject to the whims of local administrators, and you guarantee "no control".

    It is precisely that reason that I ensure that sysadm_group (and the other _groups) are all domain groups.

    Local-administrators cannnot (usually, in a well run active directory) put themselves into domain groups - because
    usually separate people administer the domain.

    It does'nt really matter if this is a POC or not - what matters is that the DB2 DBA understands the infrastructure sufficiently well to be able to control and secure DB2 installation.

    Equally I try to understand why the developers want to be in sysadm_group - usually that means I've not given them enough tools to do their job, or they are in need of education to get alternate ways of achieving what they need while still allowing a controlled environment.

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    After your explanation, I understand why it's important to use domains. Thank you.

    I would have to learn about it if I need to support DB2 on Win (we don't have any Win servers other than this one), but I'd rather work with DB2/zOS than DB2/Win. Windows is my last choice.

  6. #6
    Join Date
    Mar 2012
    Location
    Canberra, Australia
    Posts
    38
    I agree, my preference is for DB2 on AIX (having seen some weird stuff on Solaris). DB2 on Windows has a few issues or should I say Windows has a few issues!

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I don't know which IBM product you are talking about (I have installed "included" copies of DB2 for a lot of IBM products recently), but for some of them there is an option for the DBA to create the database separately (and before) the application software product install, and then use the already existing database. I prefer to create the database with instance owner, and then typically grant dbadm authority to an account used by the application install process to create the schema during the application product install.
    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
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    This is the product:
    IBM - Rational Asset Analyzer - Software

    Yes, we should be able to create db prior to installing the product. I did this for other IBM products on Linux/AIX and then gave dbadm and sysctrl (to create bp, tbs) and removed it after everything was setup.

    I want db to be created using the instance owner id and configure applications to connect using their own appl id. The developers are saying that this can't be done (they checked with IBM support) - the same id that installs the product must also create the db and that's the id appl will use to connect to the db. I don't believe this... I've been fighting with them all week and I think they're just not willing to spend more time to get things setup the way I want it. I gave them different options including setting up everything using db2admin, taking db backup, dropping db / uninstalling product, reinstalling product using their appl id and then restoring db from the backup I took. They don't want to try anything because they finally got it working their way. I'm not sure if I want to support this db.

Posting Permissions

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