It is often said that the only difference between a database user and a database is that a user has a password, while a database does not. That was once true, but no longer is. While it remains true that databases cannot be assigned a password, users can have many more attributes than databases.
- A permanent space specification
- A temporary space specification
- A spool space specification
- One or more account strings
- A BEFORE journal specification
- An AFTER journal specification
- A default journal table specification
- A fallback specification for all tables created within the permanent space for the user
Even in the minimal case, the definition of a database is always a proper subset of the definition of a user, while a user is always a proper superset of a database definition.
- A password
- A startup string
- A default role
- A default time zone
- A default date form
- A default database
- A default character set
- A default collation sequence
- A default user profile assigned using a GRANT request
- A user definition that can be partially external to the DBMS through remote user management software
When you define a user, you are defining a user in the generic sense of the term, but you are also defining a database that has the same name as the newly defined user. Note that these seemingly separate entities are actually a single database object. An important, but often overlooked, distinction between users and databases is that it is possible to define the database component of a user with a PERM space value of 0 bytes. A user defined in this way is essentially only a user, and has virtually no overlap with the attributes of databases.
Be aware that such pure users are, by default, terminal nodes in their hierarchy because they have no permanent space to pass on to child users or databases.
If you define a user with no permanent space, you must still define some minimum value of SPOOL space (and perhaps of TEMPORARY space if the user will be working with materialized global temporary tables) for the user. These can be inherited from the owner database or user or can be specified explicitly.