Ismael L. Donis García wrote:
I have created a new user. New database was created by this new user and the user, was granted the rdb$admin role and I attached the database with that role. However I can't create new users under this account as it returns an error
no permission for insert/write access to TABLE USERS
Is it possible to create users under other account than SYSDBA ?
Vlad Khorsun answers:
He became SYSDBA in that database, but not at secury database. The error is as expected as you should be SYSDBA in security2.fdb to manage users.
Christian Waldmann answers:
To let an other user add users, you have to modify the security2.fdb.
I have added the user 'ADMINISTRATOR' to the view USERS in the security database and added the required grants:
/* ============= start of script */
DROP VIEW USERS;
CREATE VIEW USERS(
USER_NAME,
SYS_USER_NAME,
GROUP_NAME,
UID,
GID,
PASSWD,
PRIVILEGE,
"COMMENT",
FIRST_NAME,
MIDDLE_NAME,
LAST_NAME,
FULL_NAME)
AS
SELECT RDB$USER_NAME, RDB$SYS_USER_NAME, RDB$GROUP_NAME, RDB$UID, RDB$GID, RDB$PASSWD,
RDB$PRIVILEGE, RDB$COMMENT, RDB$FIRST_NAME, RDB$MIDDLE_NAME, RDB$LAST_NAME,
COALESCE (RDB$first_name || _UNICODE_FSS ' ', '') ||
COALESCE (RDB$middle_name || _UNICODE_FSS ' ', '') ||
COALESCE (RDB$last_name, '')
FROM RDB$USERS
WHERE CURRENT_USER = 'SYSDBA'
OR CURRENT_USER = 'ADMINISTRATOR' /* the additional user */
OR CURRENT_USER = RDB$USERS.RDB$USER_NAME
;
/* Create(Add) Crant */
GRANT ALL ON RDB$USERS TO VIEW USERS;
GRANT ALL ON USERS TO ADMINISTRATOR;
GRANT ALL ON USERS TO SYSDBA WITH GRANT OPTION;
GRANT SELECT ON USERS TO PUBLIC;
GRANT UPDATE (FIRST_NAME, GID, GROUP_NAME, LAST_NAME, MIDDLE_NAME, PASSWD, UID) ON USERS TO PUBLIC;
/* ============= end of script */
To modify the security2.fdb, you have to:
- stop the firebird server,
- make a copy of security2.fdb
- start the firebird server
- connect to the copy of security2.fdb and modify
- stop the firebird server,
- replace the original security2.fdb with the modified
- start the firebird server