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
orsystem
- a fixed predefined oracle user that is the application object
owner, let's call itappowner
- 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 whereasappuser
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.
Best Answer
@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 statementcreate 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.