Christian Waldmann wrote:
With Firebird 1.5 I have modified the security.fdb to let an non-SYSDBA user add and delete USERS.
The same modification works with Firebird 2.1.2 and security2.fdb only for adding users with an non-SYSDBA user. Deleting is not possible, because a non-SYSDBA user can not see other users.
Two questions:
- Is this a bug or a feature that a non-SYSDBA user can ad users?
- Can I configure firebird 2.1, so that a non-SYSDBA user (with the needed grants in the security2.fdb) can delete users?
Ivan Prenosil answers:
Just look at view USERS in security database ...
Christian Waldmann adds:
Good hint, thanks.
This is my script to add a second user (called 'ADMINISTRATOR') that can administrate users, applied to the security2.fdb:
The modifications are: add the new user to the where clause and grant the view to the new user
SET SQL DIALECT 3;
SET NAMES WIN1252;
/*-------------------------------------------------------------------------*/
/* Dropping old views */
/*-------------------------------------------------------------------------*/
DROP VIEW USERS;
/*-------------------------------------------------------------------------*/
/* Creating new views */
/*-------------------------------------------------------------------------*/
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'
OR CURRENT_USER = RDB$USERS.RDB$USER_NAME
;
/*-------------------------------------------------------------------------*/
/* Restoring privileges */
/*-------------------------------------------------------------------------*/
GRANT SELECT, UPDATE, DELETE, INSERT, REFERENCES ON RDB$USERS TO VIEW USERS;
GRANT SELECT ON USERS TO "PUBLIC";
/******************************************************************************/
/**** Privileges ****/
/******************************************************************************/
GRANT ALL ON USERS TO ADMINISTRATOR;