Subject Re: [Firebird-Architect] Create of RDB$USERS
Author Brad Pepers
Jim Starkey wrote:
> A detail left dangling is how the authentication table RDB$USERS gets
> created by the default security plugin, SecurityDb. After running
> around various rathole, I think the following makes sense:
> 1. SecurityDb creates RDB$USERS on the first call to
> fb_update_account_info.
> 2. If RDB$USERS does not exist, fb_authenticate_user return success
> This means that any database with a security database of "self" (which
> includes the security database itself) has no password protection until
> either a successful fb_update_account_info is executed or the table
> RDB$USERS is created by some other process. In most cases, the table
> will be created and populated by gbak.
> Among other things, this should simplify the installation procedure
> which needs only to create an empty security database then execute a
> "create user SYSDBA password '<whatever>'".
> Here is an updated version of the RDB$USERS tables implemented by Vulcan:

A couple questions on this:

1. Just to clarify but the password is stored as some sort of hash so
thats why its character set ascii right?

2. Why the use of 128 for the user_name and name_part columns? Just an
arbitrary number choice? The actual length limit will be 32 characters
since its using UNICODE_FSS encoding right?

3. Why the rdb$ in front of every column name? I suppose if you join
this table with another the column names will more likely be unique this
way but is there a deeper reason to do this?

4. Isn't the use of domains over-blown here? I can see defining a
domain for a case where there is a global type of data used in many
tables but for every column in the table? Thats like defining a new int
type for every variable instead of just using int. Is the rdb$password
domain likely to be used for any other system table? Are the rdb$uid
and rdb$gid used anywhere else or are they just data that can optionally
be set but doesn't mean anything to Firebird?

> static const char *creationDDL [] =
> {
> "create domain rdb$user_name varchar(128) CHARACTER SET
> "create domain rdb$uid integer;",
> "create domain rdb$gid integer;",
> "create domain rdb$password varchar(64) CHARACTER SET ASCII;",
> "create domain rdb$user_privilege integer;",
> "create domain rdb$comment BLOB sub_type TEXT segment size 80
> "create domain rdb$name_part varchar(128) CHARACTER SET
> "create table rdb$users(\n"
> "rdb$user_name rdb$user_name not null primary key,\n"
> "rdb$sys_user_name rdb$user_name,\n"
> "rdb$group_name rdb$user_name,\n"
> "rdb$uid rdb$uid,\n"
> "rdb$gid rdb$gid,\n"
> "rdb$password rdb$password,\n"
> "rdb$privilege rdb$user_privilege,\n"
> "rdb$comment rdb$comment,\n"
> "rdb$first_name rdb$name_part,\n"
> "rdb$middle_name rdb$name_part,\n"
> "rdb$last_name rdb$name_part);",
> };
> If there are any more additions or corrections, please let me know
> sooner rather than later.

Brad Pepers