Additional actions may be required during creation DB with owner other than 'SYSDBA'

[pavel-zotov]

Additional (difficult to explain) asctions may be needed when we create database and its owner has name that does not meet requirement to be SQL identifier (i.e. it must be enclosed in double quotes).
The set of such actions depends on:

1. whether we create self-secutity DB or use default security.db (i.e. %FB_HOME%\securityN.fdb)
2. whether DB owner name starts with one of following:
   single quote ( ascii_char(39), i.e. ' ) or
   double quote ( ascii_char(34), i.e. " )

This is Python script (its name: try-create-db-using-misc-db-owner-names.py) that can be used to test misc combinations of above-mentioned parameters:

import subprocess
from pathlib import Path

#########################
###  S E T T I N G S  ###
#########################
#DB_OWNR = '"""q"'
#DB_OWNR = chr(34) + chr(39) + 'a' + chr(34)
#DB_OWNR = chr(34) + 'A' + chr(39) + chr(34)
#DB_OWNR = '"Q"""'
#DB_OWNR = '"<W>"'
#DB_OWNR = chr(34) + 'X' + chr(39) + chr(34) + chr(34) + chr(34)
#DB_OWNR = chr(34) + 'Y' + chr(34) + chr(34) + chr(39) + chr(34)
#DB_OWNR = chr(34) + "O'Brien" + chr(34)
#DB_OWNR = '"dba_junior"'
DB_OWNR = 'dba_helper'

SELFSEC = 0
FB_HOME = r'C:\FB\60SS'
#######################

if SELFSEC:
    DB_NAME = r'r:\temp\tmp_self_security.fdb'
else:
    DB_NAME = r'r:\temp\tmp_default_secdb.fdb'

#-----------------------------------------------------

dbconf = Path(FB_HOME) / 'databases.conf'
dbclst = dbconf.read_text().splitlines()

dbcupd = []
skip_flag = 0
for p in dbclst:
    if p.lstrip().startswith('selfsec_alias'):
        skip_flag = 1
    if not skip_flag:
        dbcupd.append(p)
    if skip_flag and p.strip() == '}':
        skip_flag = 0

ss_section = f"""
selfsec_alias = {DB_NAME}
{{
    UserManager = Srp
    AuthClient = Srp
    AuthServer = Srp
    SecurityDatabase = selfsec_alias
}}
"""

with open(Path(FB_HOME) / 'databases.conf', 'w') as f:
    f.write( '\n'.join(dbcupd) )
    f.write(ss_section)

#-----------------------------------------------------

tmp_sql = Path(__file__).with_suffix('.sql.tmp')
tmp_log = Path(__file__).with_suffix('.log.tmp')

CREATE_DB_SQL = f"""
set bail on;
set echo on;
set list on;
shell if exist {DB_NAME} del {DB_NAME};
create database '{DB_NAME}' user {DB_OWNR};
commit;

--connect '{DB_NAME}' user SYSDBA; -------------------- [ 1 ]

create or alter user {DB_OWNR} password '123';
commit;

--shell ping -n 65 127.0.0.1 > nul; ------------------- [ 2 ]

connect 'inet://{DB_NAME}' user {DB_OWNR} password '123';
set echo off;
select
    '>' || trim(d.mon$owner) || '<' as mon_owner
   ,d.mon$sec_database as sec_db
   ,d.mon$creation_date as mon_date
   ,'>' || trim(a.mon$user) || '<' as mon_user
   ,trim(a.mon$auth_method) as auth_using
   ,a.mon$remote_protocol as att_prot
   ,a.mon$client_version as mon_clnt
from mon$database d
join mon$attachments a on a.mon$attachment_id = current_connection
;
quit;
"""
tmp_sql.write_text( CREATE_DB_SQL.strip() )

#exit(0)

#-----------------------------------------------------

print(f'Running {str(tmp_sql)}. Please wait...')
with open(tmp_log, 'w') as f:
    p = subprocess.run( [ Path(FB_HOME) / 'isql', '-q', '-i', tmp_sql], stdout = f, stderr = subprocess.STDOUT)
    print('=== start of log ===')
    print(tmp_log.read_text().strip())
    print('=== finish of log ===')
    print(f'ISQL completed with {p.returncode=}')

This script generates SQL which, in turn, is called by ISQL utility and tries:

• create DB using local protocol and set owner name to some value that differs from SYSDBA;
• adds this owner to securioty DB (create or alter user {DB_OWNR} password '123';);
• makes re-connect using inet:// protocol using login and password for just created {DB_OWNR}
  Result must show data from mon$database and mon$attachments (i.e. connect via TCP must be successful).

Settings in the start part of this script are:
DB_OWNR = '"""q"' -- what owner must be assigned to created DB;
SELFSEC = 1 -- do we check case when DB is self-security or we want to use default security DB;
FB_HOME = r'C:\FB\60SS' -- path to the checked FB instance (for Windows: folder where both databases.conf and ISQL are).

::: NOTE :::

1. Be sure that your %FB_HOME%\databases.conf does not contain section with name selfsec_alias - it will be overwritten;
2. Drive 'R:' must exists on your system. or change the script by substituting some other letter (see var. DB_NAME).

Please note on two important lines of generated SQL script (within multi-line variable CREATE_DB_SQL):
connect '{DB_NAME}' user SYSDBA; -------------------- [ 1 ]
and
shell ping -n 65 127.0.0.1 > nul; ------------------- [ 2 ]

If these lines both are 'in work' (uncommented) then no problem occurs during script.
The main question is: for what they are and could they be commented out.
The brief answer: no, incommon case we can't.

In some cases we can comment out ('turn off') only one of them, in other cases - both, but there are also combinations which require these bhoth lines to be 'in work' (i.e. we can not comment them).

Change setting DB_OWNR to some value that can be used in 'CREATE USER ...' statement.
Run script:
%PYTHON_HOME%\python.exe try-create-db-using-misc-db-owner-names.py

Script will generate temporary SQL file (try-create-db-using-misc-db-owner-names.sql.tmp) and run it as child process.
The log (try-create-db-using-misc-db-owner-names.log.tmp) will contain output (stdout and stderr).

This is overall result for different cases:

Excel with results:
try-create-db-using-misc-db-owner-names.xlsx
Python script:
try-create-db-using-misc-db-owner-names.py

PS.
Issue "MAY BE REQUIRED" (in excel) needs to be explained.
I can not reproduce this problem in every run but i definitely did see that in this (one of several!) script:

set bail on;
set echo on;
set list on;
shell if exist r:\temp\tmp_default_secdb.fdb del r:\temp\tmp_default_secdb.fdb;
create database 'r:\temp\tmp_default_secdb.fdb' user "w'";
commit;

-- ################################
-- We have to re-connect as SYSDBA otherwise attempt to create user fails with:
-- Statement failed, SQLSTATE = 28000
-- add record error
-- -no permission for INSERT access to TABLE "PLG$SRP"."PLG$SRP_VIEW"
-- -Effective user is w'
-- ################################
-- connect 'r:\temp\tmp_default_secdb.fdb' user sysdba;

create or alter user "w'" password '123';
commit;

-- ################################
-- We have to wait here for ~65s otherwise:
-- Statement failed, SQLSTATE = 08006
-- Error occurred during login, please check server firebird.log for details 
-- firebird.log:
--    Authentication error
--    I/O error during "CreateFile (open)" operation for file "C:\FB\60SS\SECURITY6.FDB"
--    Error while trying to open file
--    The process cannot access the file because it is being used by another process
-- ################################
-- shell ping -n 65 127.0.0.1 > nul;

connect 'inet://r:\temp\tmp_default_secdb.fdb' user "w'" password '123';
set echo off;
select '>' || trim(d.mon$owner) || '<' as mon_owner, d.mon$sec_database as sec_db, d.mon$creation_date, '>' || trim(a.mon$user) || '<' as mon_user, trim(a.mon$auth_method) as auth_using
from mon$database d join mon$attachments a on a.mon$attachment_id = current_connection
;
commit;