DB2 (LUW) equivalent for Oracle "drop user cascade"

This question is related to creating and initialising test and development databases for an application that has the database running on LUW (linux-unix-windows)

On oracle, we have

• an os user (on linux) called oracle,

• an admin oracle user called sys or system

• a fixed predefined oracle user that is the application object

owner, let's call it appowner

• an oracle user that is the application user, ie has limited

previleges on the objects owned by appowner, let's call it
appuser

whenever there is a need to initialise the database, ie to start from scratch, we first connect to oracle using sys or system oracle user and then issue this command:

DROP USER appowner CASCADE;

and then we go about recreating the user and the objects it owns from scratch. We also grant some previliges to appuser on the objects created by appowner

The application always logs in as appuser rather than appowner unless there are any administrative tasks to be performed on the database.

Now we are porting this application to db2, and this is where we are flummoxed.

For starters, db2 creates these os users, which are also db2 users:

dasusr1
db2inst1
db2fenc1

How do these three users map to sys/system, appowner and appuser?

I believe dasusr1 is the rough equivalent of sys/system,
db2inst1 is the rough equivalent of appowner, and
db2fenc1 is the rough equivalent of appuser

(please correct me if I'm wrong, and I fully appreciate that the mapping will not be exact)

That being the case, if I have to remove all objects owned by db2inst1, do I login as dasusr1 and drop the user db2inst1?

There isn't really a one to one mapping between the oracle users and db2 users, because

• db2inst1 can create multiple databases whereas appuser is mapped to one database in oracle

• within a db2 database, there can be multiple schemas, whereas in oracle, one users maps to one schema

So it's a little confusing. It w

It might be helpful to review DB2 in general first.

When you install DB2 it creates an instance. Think of this as a "server". An instance can have zero to many databases within it (can't say I know the limit). When you created DB2 locally it created an instance for you named DB2. Usually when you install DB2 on say a Unix server, you name your instances. You can have more than one instance on the same physical server. You dsinst1 id is the "owner" of the instance (usually the instance is named after the ID that owns it). You never want to delete this ID without scrapping the instance or you can end up with some very strange behaviors. This ID (since it is instance ower) has the power to create databases, but does not have to. This blog entry by fellow DB2 user Ember Crooks helps explain the concept of an instance to those more familiar with Oracle.

Instances can have databases created within them. We'll come back to this later.

The DAS (or DB2 Administration Server) is really more like a service. It is what allows some of the GUI tools like DB2 Control Center to interface with DB2. From what I understand, this service isn't necessary for DB2 to run, but is often necessary for end-users to interface with DB2 from a non-command line perspective. (Mustaccio am I correct here?). No matter how many instances you have on a physical box, there is only one installation of the DAS. This one installation can service all the instances. There is usually a separate ID that is needed to execute the DAS service. This is your dasusr1. Again, you don't want to delete this ID. This ID usually has no authority to create any objects within your database.

Now for the fence ID (ie, db2fenc1). Having one of these is a good idea. When you create an instance you can specify a fence ID. If you do, it becomes that ID. If you don't, then the instance owner ID becomes the fence ID. The fence ID is used for running certain stored procedures within DB2. In DB2 there are two ways to run stored procedures: fenced and unfenced. Unfenced means the stored procedure runs within the DB2 engine's memory. It will run faster, but if the stored procedure is more than SQL (ie, contains C++ or Java code for example) and it crashes, it can threaten the engine and actually crash it and cause harm. Fenced stored procedures run outside of the DB2 engine memory. They are thus slower, but if they crash they do not threaten the DB2 engine because they ran in their own protected memory area. An example of how this is used by DB2: if you run the db2 export utility from the ADMIN_CMD stored procedure, DB2 will switch the user on you of who actually executes the export. This is because you are doing file I/O and DB2 wishes to run this fenced to protect the engine. So even if you are running this stored procedure call as the instance owner, DB2 will switch the ID to the fenced ID (with no chance of you altering this behavior). If you look at the permissions of who owns the export file, you will notice it is the fenced ID, rather than the instance ID (or other ID you used to fire off the stored procedure). Of course if your instance ID is your fenced ID, then it will look one and the same to you.

(Big breath).

OK. So I said all that to introduce you to DB2 in a nutshell. Now we get to what you are probably worrying about -- who owns what? Which ever ID creates the database owns the database. There are "groups" that DB2 accepts that generally have the DBADM (ie, database administrator) authority to create databases. So if you created a database using your instance owner ID, then it would be the owner of the database and any objects it creates within the database. If a different ID connects to the database (as long as it has the correct permissions) it can create objects and own them.

Here is the interesting thing about groups and IDs within DB2. They aren't really objects from what I can see. So you can't drop them. You can't create them either (at least from what I can see). When you grant or revoke a permission to an ID, DB2 then stores mention of that group or ID. But DB2 does not really "create" them. Most often I have seen DB2 pointed to the OS or to an LDAP or both to authenticate the IDs. If the IDs are removed, you have to manually remove the permissions from the IDs and/or transfer ownership of the objects. There is nothing magical about this here. I asked a similar question having to do with doing a backup/restore to a different system and how that affects permissions.

And lastly for your comment about schemas, generally each ID maps to its own schema. Whether or not the schema exists (created by the DBADM ID explicitly or by the end user through the IMPLICIT_SCHEMA permission) is another question. Generally whatever ID connects to a database, DB2 assumes the schema is the same as the user id and you have to switch schemas (SET SCHEMA MYSCHEMA) or explicitly mention your objects (SELECT * FROM MY_OTHER_SCHEMA.MY_TABLE) to work with them.

StackExchange Database Administrators Q#49171, answer score: 3