So the easy thing to do on Commerce build is to use your DB2 instance owner for everything related to the database. But that’s not really the best choice. It’s almost always the choice I see when a DBA was not involved with the architecture or build of a Commerce system. You’ll notice in the instance XML that there is an option to specify a separate DBUser and DBAName. Generally, it is best to have different values for these. The DBAName is the instance owner, but the DBUser name is a dedicated user for Commerce to use to connect to the database.
The DBUser should have its own ID. This is the ID that commerce will use to connect to the database going forward. What it really needs is Select, Update, Insert, and Delete (SUID) on all tables. If set up when the system is built, then it will also be the primary schema name used. During upgrades, you can add it into the SYSADM group too, so it can create tables and other objects as needed. But remove it after that – it does not need that level of permissions all the time. Why separate this? Well, it is best to address potential security threats at every level. I frankly don’t know what protections Commerce puts in place against SQL injection, but if the id that Commerce is using doesn’t have the authority to drop anything, then that’s so much more protection against the potential threat. If PCI (DSS) compliance is a concern, this is also necessary.
What other IDs to use?
Do not share the password to the ID that commerce uses to access to the database with developers. That means that each developer has their own ID that belongs to a group that has SUID on all Commerce tables. Do not let developers create or drop objects – we expect them to work through a DBA for that so we can apply basic sanity checks. This also puts you on a good footing for PCI compliance.
Use a dedicated ID for stagingprop(and stagingcopy). The id needs SUID on all tables listed in STGMERTAB and STGSITETAB and STGMRSTAB along with the STAGLOG, KEYS, and CMFILE tables. We also create aliases on these tables – with the schema the same as the stagingprop user. If you don’t do this, you’ll get SQL0204 (Exception: com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -204). Commerce does not specify the schema when doing anything. Stagignprop allows you to specify the dbschema to get around this, but stagingcopy does not, so these aliases are a good idea.
Use dedicated IDs for data load, misc. batch jobs, and search, with permissions granted only explicitly as needed by the ID for the processes it is running on that particular server. This sometimes requires working through permissions one by one as failures are encountered, which can be
One last dedicated group is one that specifically has select access only on all tables – with membership severely restricted on production. This can be useful for people who should not be updating data. But on production there are two issues – #1 is access to confidential or personal data and #2 is the fact that a bad read-only query can tank database performance – something you don’t want to allow to happen on production.
What to revoke
One last thing to consider is one of DB2’s Default groups – PUBLIC. This group means “anyone who can log into the database server” (assuming you are using server authentication – please, never use client authentication). Rarely do you really want to allow anyone who can simply log into a server to access your database, even if it is a dedicated db server. By default it has CONNECT on the database, and select on all the system tables (db2 system tables – the data dictionary type stuff). Revoke at least CONNECt from it, and consider revoking other permissions as well. I like to leave package permissions in place because PUBLIC is very useful when doing BINDs. But when you revoke the CONNECT privilege, you change the meaning of the group to “anyone who can connect to the database” – a much more restricted and useful group.
Is it easiest just to use the db2 instance owning id for everything? Maybe, but it is not PCI compliant, and you may end up with issues with people doing something they shouldn’t. Proper division of IDs will lead to a system that runs more smoothly and reduce security concerns.
