3

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 would help if someone with experience in porting applications from oracle to db2 could throw some light on the equivalence between the db2 and oracle users, which would finally lead to finding out the db2 equivalent for oracle drop user cascade.

2
  • Presumably you are talking about DB2 LUW. The architectures under IBM i or z/OS is much different. As such, any detailed answer to this question will be platform specific.
    – WarrenT
    Sep 3, 2013 at 23:37
  • thanks I wasn't aware of the difference, have modified the question and tags to reflect this
    – alok
    Sep 4, 2013 at 4:41

2 Answers 2

3

@Chris Aldrich has given a good explanation. I will just add a few things here.

1) There is no concept of a "database user" in DB2. All authentication happens outside the database or instance, in the operating system. Also, there is no direct relationship between a user ID and a schema name, unlike in Oracle. In DB2 a schema is just a logical grouping of objects, it does not have any special security features. Any user can create any schema. For example, while logged in as mustaccio I run the statement create table foo (id int...), and that creates a schema MUSTACCIO (if it's not already there) and a table in it. As you see, the schema name resolution defaults to my authorization ID. However, I might as well run the statement create table alok.foo (id char(3)...), which in this case creates a schema ALOK and the table in it. mustaccio will be the owner of both tables.

2) Regarding the user ID mapping, I would probably say that the DAS owner dasusr1 and the fenced user db2fenc1 do not map to anything in an Oracle database. The instance owner db2inst1 maps to the oracle user ID. Whoever creates a database (might be db2inst1 or some other user authorized to do that by membership in the SYSADM group, for example) obtains DBADM and SECADM privileges in that database, which is somewhat similar to being system and/or sys (I'm not really sure what is the distinction between the two in an Oracle database). If you need a functional ID that owns database objects, you create appowner in the operating system, grant to it appropriate permissions, and connect to that user when creating objects. Similarly, you create appuser in the operating system, grant object access privileges to it, and let your application connect as that user.

3) Since there are no database users in DB2, you cannot drop a user. To delete objects in a particular schema you can use the ADMIN_DROP_SCHEMA() procedure.

3

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.

Hope this helps.

1
  • It would be great if your response and mustaccio's could be merged into one accepted answer. Unfortunately I don't consider myself qualified to edit either of your answers yet.
    – alok
    Sep 4, 2013 at 4:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.