Hi Alan and all,
Thanks for this further insight into your approach. I am quite new to FB, but
from what I have read I am wondering a few things with this approach. First
looking at Borrie's 2nd edition (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."
so I am now left wondering how your approach works.
1. I see the new RDB$USERS table and view being created in the DB where I ran
the SQL. In Flame Robin, the new RDB$USERS appears red (not sure what that
means). Does this table and view somehow supplant the RDB$USERS and USERS view
mentioned above?
2. When I ran the SQL, it complained on trying to create the RDB$ADMIN ROLE, as
it already exists.
3. When I create a user using "CREATE USER xxx PASSWORD 'yyy'", do I also need
to duplicate adding records into RDB$USERS?
4. I see the GRANTing to PUBLIC, however I have seen various posts and/or
sections of the Firebird books cautioning on GRANTing to PUBLIC for obvious
security reasons. The new RDB$USERS.PASSWD is also now visible to all users,
right?
5. The example oCo*, oEo*, and oSo* users listed have all been given RDB$ADMIN.
Our need is to delegate SYSDBA, giving a select subset of people (eg 2 people
in IT admin roles) administrative capability and CRUD operations on users,
without disclosing the SYSDBA credentials. Is there a similar intention here,
that different kinds of users would have the RDB$ADMIN capability and others
wouldn't??
Lastly, do you (or anyone else) know how things work in FB v3.0? Will this all
be a moot point? Reading Harrison's 'Migration Guide to Firebird 3.0', I see I
could create local, per database, users (p. 63), and seems to infer from this
(p. 70)"Only the SYSDBA or users that have the rdb$admin role in the security
database and are logged in with that role can create new users."and the pages
around this that users with RDB$ADMIN ROLE can perform CREATE/ALTER/DROP, but I
still don't know if in Firebird 3.0 it will be possible to enumerate/list the
users (for any of the 3 security models).
Thanks again,David
On Tuesday, February 21, 2017 4:19 PM, "'Alan McDonald' [email protected]
[firebird-support]" <[email protected]> wrote:
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 is
not accessible by any user, even SYSDBA. All access to RDB$USERS is done via a
view
named 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?
----------------------------------------------------------
I run FB2.5.6
The key here is the grant to PUBLIC
Here is my meta data. All these users I grant ADMIN rights to are able to
enumerate all users. I use the services API not SQL.
But here is my metadata complete:
CREATE TABLE RDB$USERS (
RDB$USER_NAME VARCHAR(128) CHARACTER SET UNICODE_FSS NOT NULL,
RDB$SYS_USER_NAME VARCHAR(128) CHARACTER SET UNICODE_FSS,
RDB$GROUP_NAME VARCHAR(128) CHARACTER SET UNICODE_FSS,
RDB$UID INTEGER,
RDB$GID INTEGER,
RDB$PASSWD VARCHAR(64) CHARACTER SET OCTETS NOT NULL,
RDB$PRIVILEGE INTEGER,
RDB$COMMENT BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET UNICODE_FSS,
RDB$FIRST_NAME VARCHAR(32) CHARACTER SET UNICODE_FSS DEFAULT _UNICODE_FSS '',
RDB$MIDDLE_NAME VARCHAR(32) CHARACTER SET UNICODE_FSS DEFAULT _UNICODE_FSS '',
RDB$LAST_NAME VARCHAR(32) CHARACTER SET UNICODE_FSS DEFAULT _UNICODE_FSS ''
);
/* 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 X'20', '') ||
COALESCE (RDB$middle_name || _UNICODE_FSS X'20', '') ||
COALESCE (RDB$last_name, '')
FROM RDB$USERS
;
ALTER TABLE RDB$USERS ADD PRIMARY KEY (RDB$USER_NAME);
CREATE ROLE RDB$ADMIN;
/* Privileges of users */
GRANT RDB$ADMIN TO "0C0AYLMERS";
GRANT RDB$ADMIN TO "0C0DAVIESS";
GRANT RDB$ADMIN TO "0C0META";
GRANT RDB$ADMIN TO "0C0SLEGER";
GRANT RDB$ADMIN TO "0E0META";
GRANT RDB$ADMIN TO "0E0SKELLERR";
GRANT RDB$ADMIN TO "0E0SLEGER";
GRANT RDB$ADMIN TO "0E0WARDK";
GRANT RDB$ADMIN TO "0S0AYLMERS";
GRANT RDB$ADMIN TO "0S0DAVIESS";
GRANT RDB$ADMIN TO "0S0META";
GRANT RDB$ADMIN TO "0S0SLEGER";
GRANT RDB$ADMIN TO "0S0WARDK";
GRANT SELECT, INSERT,
UPDATE(FIRST_NAME,GID,GROUP_NAME,LAST_NAME,MIDDLE_NAME,PASSWD,UID), DELETE ON
USERS TO PUBLIC;
/* Privileges of views */
GRANT ALL ON RDB$USERS TO VIEW USERS;
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)
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 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;
#yiv7903094194 #yiv7903094194 -- #yiv7903094194ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv7903094194
#yiv7903094194ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv7903094194
#yiv7903094194ygrp-mkp #yiv7903094194hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv7903094194 #yiv7903094194ygrp-mkp #yiv7903094194ads
{margin-bottom:10px;}#yiv7903094194 #yiv7903094194ygrp-mkp .yiv7903094194ad
{padding:0 0;}#yiv7903094194 #yiv7903094194ygrp-mkp .yiv7903094194ad p
{margin:0;}#yiv7903094194 #yiv7903094194ygrp-mkp .yiv7903094194ad a
{color:#0000ff;text-decoration:none;}#yiv7903094194 #yiv7903094194ygrp-sponsor
#yiv7903094194ygrp-lc {font-family:Arial;}#yiv7903094194
#yiv7903094194ygrp-sponsor #yiv7903094194ygrp-lc #yiv7903094194hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv7903094194
#yiv7903094194ygrp-sponsor #yiv7903094194ygrp-lc .yiv7903094194ad
{margin-bottom:10px;padding:0 0;}#yiv7903094194 #yiv7903094194actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv7903094194
#yiv7903094194activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv7903094194
#yiv7903094194activity span {font-weight:700;}#yiv7903094194
#yiv7903094194activity span:first-child
{text-transform:uppercase;}#yiv7903094194 #yiv7903094194activity span a
{color:#5085b6;text-decoration:none;}#yiv7903094194 #yiv7903094194activity span
span {color:#ff7900;}#yiv7903094194 #yiv7903094194activity span
.yiv7903094194underline {text-decoration:underline;}#yiv7903094194
.yiv7903094194attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv7903094194 .yiv7903094194attach div a
{text-decoration:none;}#yiv7903094194 .yiv7903094194attach img
{border:none;padding-right:5px;}#yiv7903094194 .yiv7903094194attach label
{display:block;margin-bottom:5px;}#yiv7903094194 .yiv7903094194attach label a
{text-decoration:none;}#yiv7903094194 blockquote {margin:0 0 0
4px;}#yiv7903094194 .yiv7903094194bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv7903094194
.yiv7903094194bold a {text-decoration:none;}#yiv7903094194 dd.yiv7903094194last
p a {font-family:Verdana;font-weight:700;}#yiv7903094194 dd.yiv7903094194last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv7903094194
dd.yiv7903094194last p span.yiv7903094194yshortcuts
{margin-right:0;}#yiv7903094194 div.yiv7903094194attach-table div div a
{text-decoration:none;}#yiv7903094194 div.yiv7903094194attach-table
{width:400px;}#yiv7903094194 div.yiv7903094194file-title a, #yiv7903094194
div.yiv7903094194file-title a:active, #yiv7903094194
div.yiv7903094194file-title a:hover, #yiv7903094194 div.yiv7903094194file-title
a:visited {text-decoration:none;}#yiv7903094194 div.yiv7903094194photo-title a,
#yiv7903094194 div.yiv7903094194photo-title a:active, #yiv7903094194
div.yiv7903094194photo-title a:hover, #yiv7903094194
div.yiv7903094194photo-title a:visited {text-decoration:none;}#yiv7903094194
div#yiv7903094194ygrp-mlmsg #yiv7903094194ygrp-msg p a
span.yiv7903094194yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv7903094194
.yiv7903094194green {color:#628c2a;}#yiv7903094194 .yiv7903094194MsoNormal
{margin:0 0 0 0;}#yiv7903094194 o {font-size:0;}#yiv7903094194
#yiv7903094194photos div {float:left;width:72px;}#yiv7903094194
#yiv7903094194photos div div {border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}#yiv7903094194
#yiv7903094194photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv7903094194
#yiv7903094194reco-category {font-size:77%;}#yiv7903094194
#yiv7903094194reco-desc {font-size:77%;}#yiv7903094194 .yiv7903094194replbq
{margin:4px;}#yiv7903094194 #yiv7903094194ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv7903094194 #yiv7903094194ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv7903094194
#yiv7903094194ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv7903094194
#yiv7903094194ygrp-mlmsg select, #yiv7903094194 input, #yiv7903094194 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv7903094194
#yiv7903094194ygrp-mlmsg pre, #yiv7903094194 code {font:115%
monospace;}#yiv7903094194 #yiv7903094194ygrp-mlmsg *
{line-height:1.22em;}#yiv7903094194 #yiv7903094194ygrp-mlmsg #yiv7903094194logo
{padding-bottom:10px;}#yiv7903094194 #yiv7903094194ygrp-msg p a
{font-family:Verdana;}#yiv7903094194 #yiv7903094194ygrp-msg
p#yiv7903094194attach-count span {color:#1E66AE;font-weight:700;}#yiv7903094194
#yiv7903094194ygrp-reco #yiv7903094194reco-head
{color:#ff7900;font-weight:700;}#yiv7903094194 #yiv7903094194ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv7903094194 #yiv7903094194ygrp-sponsor
#yiv7903094194ov li a {font-size:130%;text-decoration:none;}#yiv7903094194
#yiv7903094194ygrp-sponsor #yiv7903094194ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv7903094194
#yiv7903094194ygrp-sponsor #yiv7903094194ov ul {margin:0;padding:0 0 0
8px;}#yiv7903094194 #yiv7903094194ygrp-text
{font-family:Georgia;}#yiv7903094194 #yiv7903094194ygrp-text p {margin:0 0 1em
0;}#yiv7903094194 #yiv7903094194ygrp-text tt {font-size:120%;}#yiv7903094194
#yiv7903094194ygrp-vital ul li:last-child {border-right:none
!important;}#yiv7903094194
