Bryan,

Maybe something in here will provide that ah-haa moment. ;-)

C:\Programs64\Firebird_3_0_embedded>isql employee -user sysdba
Database: employee, User: SYSDBA
SQL> show version;
ISQL Version: WI-V3.0.4.33054 Firebird 3.0
Server version:
Firebird/Windows/AMD/Intel/x64 (access method), version "WI-V3.0.4.33054 Firebird 3.0"
on disk structure version 12.0
SQL> create user test password 'testuser' grant admin role;
SQL> -- This statement looks like DDL, so isql should auto-commit it, right?
CON> -- But, under the surface, it is DML (on the security database, natch!)
CON> -- which isql does not autocommit, so....
CON> -- we need to commit that DML explicitly before it can be available
CON> -- to grant it the RDB$ADMIN role in the current database;
SQL> --;
SQL> commit;
SQL> select sec$user_name, sec$active, sec$admin, sec$plugin from sec$users;
SEC$USER_NAME SEC$ACTIVE SEC$ADMIN SEC$PLUGIN
=============================== ========== ========= ===============================
SYSDBA <true> <true> Srp
DEVELOPER <true> <false> Srp
TEST <true> <true> Srp

SQL> -- Now the user exists, we grant the role to it;
SQL> grant rdb$admin to test;
SQL> commit;
SQL> select rdb$user, rdb$privilege, rdb$relation_name from rdb$user_privileges
CON> where rdb$user = 'test';
SQL> -- no result there:
CON> -- RDB$USER in RDB$USER_PRIVILEGES is stored here as string. Engine
CON> -- always stores it in upper case and of course it is case-sensitive.;
SQL> --
SQL> select rdb$user, rdb$privilege, rdb$relation_name from rdb$user_privileges
CON> where rdb$user = 'TEST';

RDB$USER RDB$PRIVILEGE RDB$RELATION_NAME
=============================== ============= ===============================
TEST M RDB$ADMIN

SQL>quit;

C:\Programs64\Firebird_3_0_embedded>isql employee -user test2 -role rdb$admin
Database: employee, User: TEST, Role: RDB$ADMIN

SQL> create user test2 password 'test2user';
SQL> select sec$user_name, sec$active, sec$admin, sec$plugin from sec$users;

SEC$USER_NAME SEC$ACTIVE SEC$ADMIN SEC$PLUGIN
=============================== ========== ========= ===============================
SYSDBA <true> <true> Srp
DEVELOPER <true> <false> Srp
TEST <true> <true> Srp
TEST2 <true> <false> Srp

Take careful note: the GRANT ADMIN ROLE gives user management privileges to
the user in ANY database on the server, whereas the RDB$ADMIN role applies
only in the database where it was granted. In other words, the user must

1) be committed first into the security database with the
GRANT ADMIN ROLE (and don't forget, you have to commit
it if you add it later with ALTER USER as well)
2) be granted RDB$ADMIN in all the databases where you want it
to have user management privileges
3) be logged in to a database with the RDB$ADMIN role

In Fb 3 and Fb 4-alpha, you must have *both* and perform your login with
the role. In Fb 4 beta 1, a user with the GRANT ADMIN ROLE (SEC$ADMIN TRUE)
does not need to log in with the RDB$ADMIN role. I don't know whether this
is going to be backported to Fb 3.0.5.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com