Thanks for this Alan.
Unfortunately when connected under the ROLE RDB$ADMIN, this still results in an
error as RDB$USERS is an unknown table. Same goes for replacing this with
USERS. As stated in Helen Borrie's The Firebird Book (p.729):"From Firebird 2.0
onward, the USER table is replaced by one named RDB$USERS and isnot accessible
by any user, even SYSDBA. All access to RDB$USERS is done via a viewnamed
USERS."
In case you meant what many posts suggest using RDB$USER_PRIVILEGES, there are
a similar number of responses which clearly state that doing is not the correct
approach. If a user has no privileges, they won't appear in the list. Of course
the additional USER fields (first/middle/last names) aren't accessible either.
Any other suggestions?
On Tuesday, February 21, 2017 1:32 PM, "'Alan McDonald' [email protected]
[firebird-support]" <[email protected]> wrote:
In a production environment using Firebird v2.5, we need to delegate
authority of USER CRUD operations to more than one person without these admins
sharing the SYSDBA user and password. These admins have been created as users
with ADMIN ROLE, and are logged in under the RDB$ADMIN ROLE (eg in Flame Robin
or via the .NET Provider, or '-admin' switch in gsec). With this ROLE, it is
possible to perform Creation, Update, and Deletion operations of CRUD via Flame
Robin as well as gsec. The roadblock, however, is not being able to
list/enumerate the users (ie Read). In gsec when logged in as SYSDBA all users
are displayed via the 'display' command, whereas using another RDB$ADMIN
superuser only the logged in user is displayed. The latter is also the case
when using the .NET Provider and making the call to
FirebirdSql.Data.Services.FbSecurity.DisplayUsers(). Via Flame Robin menu
Server | Manager Users, you are prompted with the Database Credentials dialog
with Username pre-populated with 'SYSDBA' and read-only. To delegate user
management, it is necessary to have users other than SYSDBA to be able to list
and enumerate them. This is required even simply to avoid name clashes. Any
suggestions would be greatly appreciated on how it is possible to do this, or
if it isn't possible, potential alternative(s). Lastly, are there differences
and/or does this problem disappear with Firebird v3.0? My end goal here is
implementation in Visual Studio C# using SQL through
FirebirdSql.Data.FirebirdClient or directly through FirebirdSql.Data.Services,
although solutions through other approaches would be welcome. Thanks in
advance,David
CREATE OR ALTER VIEW USERS( USER_NAME, SYS_USER_NAME, GROUP_NAME,
UID, GID, PASSWD, PRIVILEGE, COMMENT, FIRST_NAME,
MIDDLE_NAME, LAST_NAME, FULL_NAME)ASSELECT 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 X'20', '') || COALESCE
(RDB$middle_name || _UNICODE_FSS X'20', '') ||
COALESCE (RDB$last_name, '') FROM RDB$USERS /* to allow listing
of all users for all users see also GRANT UPDATE ON USERS TO PUBLIC
WHERE CURRENT_USER = 'SYSDBA' OR CURRENT_USER =
RDB$USERS.RDB$USER_NAME */;
/******************************************************************************//****
Privileges
****//******************************************************************************/
/* Privileges of users */GRANT SELECT, INSERT,
UPDATE(FIRST_NAME,GID,GROUP_NAME,LAST_NAME,MIDDLE_NAME,PASSWD,UID), DELETE ON
USERS TO PUBLIC; /* Privileges of roles */GRANT ALL ON USERS TO RDB$ADMIN; /*
Privileges of views */GRANT ALL ON RDB$USERS TO VIEW USERS; #yiv0217423226
#yiv0217423226 -- #yiv0217423226ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv0217423226
#yiv0217423226ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv0217423226
#yiv0217423226ygrp-mkp #yiv0217423226hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv0217423226 #yiv0217423226ygrp-mkp #yiv0217423226ads
{margin-bottom:10px;}#yiv0217423226 #yiv0217423226ygrp-mkp .yiv0217423226ad
{padding:0 0;}#yiv0217423226 #yiv0217423226ygrp-mkp .yiv0217423226ad p
{margin:0;}#yiv0217423226 #yiv0217423226ygrp-mkp .yiv0217423226ad a
{color:#0000ff;text-decoration:none;}#yiv0217423226 #yiv0217423226ygrp-sponsor
#yiv0217423226ygrp-lc {font-family:Arial;}#yiv0217423226
#yiv0217423226ygrp-sponsor #yiv0217423226ygrp-lc #yiv0217423226hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv0217423226
#yiv0217423226ygrp-sponsor #yiv0217423226ygrp-lc .yiv0217423226ad
{margin-bottom:10px;padding:0 0;}#yiv0217423226 #yiv0217423226actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv0217423226
#yiv0217423226activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv0217423226
#yiv0217423226activity span {font-weight:700;}#yiv0217423226
#yiv0217423226activity span:first-child
{text-transform:uppercase;}#yiv0217423226 #yiv0217423226activity span a
{color:#5085b6;text-decoration:none;}#yiv0217423226 #yiv0217423226activity span
span {color:#ff7900;}#yiv0217423226 #yiv0217423226activity span
.yiv0217423226underline {text-decoration:underline;}#yiv0217423226
.yiv0217423226attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv0217423226 .yiv0217423226attach div a
{text-decoration:none;}#yiv0217423226 .yiv0217423226attach img
{border:none;padding-right:5px;}#yiv0217423226 .yiv0217423226attach label
{display:block;margin-bottom:5px;}#yiv0217423226 .yiv0217423226attach label a
{text-decoration:none;}#yiv0217423226 blockquote {margin:0 0 0
4px;}#yiv0217423226 .yiv0217423226bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv0217423226
.yiv0217423226bold a {text-decoration:none;}#yiv0217423226 dd.yiv0217423226last
p a {font-family:Verdana;font-weight:700;}#yiv0217423226 dd.yiv0217423226last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv0217423226
dd.yiv0217423226last p span.yiv0217423226yshortcuts
{margin-right:0;}#yiv0217423226 div.yiv0217423226attach-table div div a
{text-decoration:none;}#yiv0217423226 div.yiv0217423226attach-table
{width:400px;}#yiv0217423226 div.yiv0217423226file-title a, #yiv0217423226
div.yiv0217423226file-title a:active, #yiv0217423226
div.yiv0217423226file-title a:hover, #yiv0217423226 div.yiv0217423226file-title
a:visited {text-decoration:none;}#yiv0217423226 div.yiv0217423226photo-title a,
#yiv0217423226 div.yiv0217423226photo-title a:active, #yiv0217423226
div.yiv0217423226photo-title a:hover, #yiv0217423226
div.yiv0217423226photo-title a:visited {text-decoration:none;}#yiv0217423226
div#yiv0217423226ygrp-mlmsg #yiv0217423226ygrp-msg p a
span.yiv0217423226yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv0217423226
.yiv0217423226green {color:#628c2a;}#yiv0217423226 .yiv0217423226MsoNormal
{margin:0 0 0 0;}#yiv0217423226 o {font-size:0;}#yiv0217423226
#yiv0217423226photos div {float:left;width:72px;}#yiv0217423226
#yiv0217423226photos div div {border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}#yiv0217423226
#yiv0217423226photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv0217423226
#yiv0217423226reco-category {font-size:77%;}#yiv0217423226
#yiv0217423226reco-desc {font-size:77%;}#yiv0217423226 .yiv0217423226replbq
{margin:4px;}#yiv0217423226 #yiv0217423226ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv0217423226 #yiv0217423226ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv0217423226
#yiv0217423226ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv0217423226
#yiv0217423226ygrp-mlmsg select, #yiv0217423226 input, #yiv0217423226 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv0217423226
#yiv0217423226ygrp-mlmsg pre, #yiv0217423226 code {font:115%
monospace;}#yiv0217423226 #yiv0217423226ygrp-mlmsg *
{line-height:1.22em;}#yiv0217423226 #yiv0217423226ygrp-mlmsg #yiv0217423226logo
{padding-bottom:10px;}#yiv0217423226 #yiv0217423226ygrp-msg p a
{font-family:Verdana;}#yiv0217423226 #yiv0217423226ygrp-msg
p#yiv0217423226attach-count span {color:#1E66AE;font-weight:700;}#yiv0217423226
#yiv0217423226ygrp-reco #yiv0217423226reco-head
{color:#ff7900;font-weight:700;}#yiv0217423226 #yiv0217423226ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv0217423226 #yiv0217423226ygrp-sponsor
#yiv0217423226ov li a {font-size:130%;text-decoration:none;}#yiv0217423226
#yiv0217423226ygrp-sponsor #yiv0217423226ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv0217423226
#yiv0217423226ygrp-sponsor #yiv0217423226ov ul {margin:0;padding:0 0 0
8px;}#yiv0217423226 #yiv0217423226ygrp-text
{font-family:Georgia;}#yiv0217423226 #yiv0217423226ygrp-text p {margin:0 0 1em
0;}#yiv0217423226 #yiv0217423226ygrp-text tt {font-size:120%;}#yiv0217423226
#yiv0217423226ygrp-vital ul li:last-child {border-right:none
!important;}#yiv0217423226
