14 May 2025
Authorization in Metadb 1.4 has two levels:
- Registration
- Privileges
In "Metadb authorization part 1: granular
privileges"
we discussed privileges. This article covers registration, which is
defined using the commands REGISTER USER and DEREGISTER USER.
Returning to the examples in part 1, we began by creating two users:
CREATE USER jenny WITH PASSWORD 'fChU4R9JvZjRwsEb', COMMENT 'Jenny Wren';
CREATE USER mark WITH PASSWORD 'KBYwLhdLTQTmxXRs', COMMENT 'Mark Tapley';
The CREATE USER command does
more than create a user; it also registers the user, which is the
first step in authorization. Registering a user tells Metadb that it
is a user of this Metadb instance, rather than a user of some other
database in the database system.
Once a user is registered, it can receive privileges via the
GRANT command as described in
part 1. GRANT allows only registered users to be granted
privileges.
In most cases registration is handled by CREATE USER, but there are
instances where it is not possible to use CREATE USER because the
user already exists for some other purpose. For example, a user may
have been created to work with another database, or even another
Metadb instance, in the same database system.
Suppose that a database system contains three Metadb databases called
m1, m2, and m3, and jenny is registered in m1 and m2 but
not m3. We would like to register jenny in m3. In this case,
issuing CREATE USER is not an option, because jenny already
exists. Instead we use the REGISTER USER command:
REGISTER USER jenny;
In addition we can create a user schema with the command CREATE SCHEMA, which also would
have been executed by CREATE USER:
CREATE SCHEMA FOR USER jenny;
In summary, CREATE USER does three things:
- Creates the user
- Registers the user by executing
REGISTER USER - Creates the user schema by executing
CREATE SCHEMA
Since we wanted to add a user in m3 that already exists, we issued
REGISTER USER and CREATE SCHEMA manually.
Now suppose that we would like to remove jenny from having access to
m1. Normally we could use DROP USER in m1, but this would
return an error message because jenny is also registered in m2.
Instead we can simply deregister jenny in m1:
DEREGISTER USER jenny;
This also has the effect of removing all privileges from jenny in
m1.
A similar case is when we would like to drop a user, and that user is
registered in two or more Metadb instances in the same database
system. Suppose that mark is registered in m1, m2 and m3.
Again if we tried to use DROP USER, it would fail with an error.
Instead we have to issue DEREGISTER USER, in each of the three
Metadb instances, to deregister mark. Then we can issue DROP USER
in any one of the instances.