Re: [firebird-support] Re: Delegating SYSDBA and enumerating users

[hv...@users.sourceforge.net [firebird-support]]

---In firebird-support@yahoogroups.com,  wrote :
 ...

 > Now I have a couple of questions for you: > 

 > 1. Do you know if it is possible in gsec to log in under a custom ROLE? 

  Sure, it is possible

> Further to this, is there a way of GRANTing the custom ROLE the RDB$ADMIN 
> ROLE? 

  No

> I am guessing this isn't possible for 2 reasons; custom ROLE is in a 
> particular DB not the Security2.fdb, 
> and you can't GRANT a ROLE to a ROLE.
 
  Something in this direction will be available in FB4


 > 2. Do you know how things work via the .NET Provider (or rather why they 
 > don't :)? 
> When connecting using SYSDBA I see all users (ie via 
> FirebirdSql.Data.Services.FbSecurity.DisplayUsers()), 
> however logging in using another user (eg your ADM1) and the RDB$ADMIN ROLE, 
> I am still only seeing the 
> the single user ADM1.

  I don't use .NET by myself. But quick look at code show that 
FbSecurity.DisplayUsers() doesn't pass
role name into service manager (it should be done using isc_spb_sql_role_name 
tag). So, you could
add requiest to the tracker.


Regards,
Vlad

  

Re: [firebird-support] Re: Delegating SYSDBA and enumerating users

[cerrogrand...@yahoo.ca [firebird-support]]

Thanks Vlad for your replies. In response to your first reply:
a) Yes, I had read "Escalating RDB$ADMIN Scope for User Management" but I 
appreciate the link and others may too. My users with admin access have already 
been granted the RDB$ADMIN ROLE.
b) gsec -role - that is great to know the proper gsec syntax to log in using a 
ROLE. Thanks :).
So I am now getting the same result using gsec. Unfortunately in Flame Robin it 
is as originally noted: 
Now I have a couple of questions for you:
1. Do you know if it is possible in gsec to log in under a custom ROLE? Further 
to this, is there a way of GRANTing the custom ROLE the RDB$ADMIN ROLE? I am 
guessing this isn't possible for 2 reasons; custom ROLE is in a particular DB 
not the Security2.fdb, and you can't GRANT a ROLE to a ROLE.
2. Do you know how things work via the .NET Provider (or rather why they don't 
:)? When connecting using SYSDBA I see all users (ie via 
FirebirdSql.Data.Services.FbSecurity.DisplayUsers()), however logging in using 
another user (eg your ADM1) and the RDB$ADMIN ROLE, I am still only seeing the 
the single user ADM1.
Thanks,
David 

On Thursday, February 23, 2017 1:17 AM, "hv...@users.sourceforge.net 
[firebird-support]"  wrote:
 

     A little sample with Firebird 2.5

I added ordinary users 'user1' and 'user2' and admin user 'adm1'.
Now try to display list of users

1. Use sysdba account:

firebird\bin>gsec -user sysdba -pass masterkey -display
 user name    uid   gid admin full name

SYSDBA  0 0   Sql Server Administrator
USER1   0 0
USER2   0 0
ADM1    0 0 admin

Of course, sysdba could see all users.
You see - adm1 is really admin user while user1 and user2 are not admins.


2. Ordinary users could see itself only:

firebird\bin>gsec -user user1 -pass u1 -display
 user name    uid   gid admin full name

USER1   0 0


3. What about non-sysdba admin ?

firebird\bin>gsec -user adm1 -pass adm1 -display
 user name    uid   gid admin full name

ADM1    0 0 admin

Something wrong ? Let see next sample


4. Specify admin role:

firebird\bin>gsec -user adm1 -pass adm1 -role rdb$admin -display
 user name    uid   gid admin full name

SYSDBA  0 0   Sql Server Administrator
USER1   0 0
USER2   0 0
ADM1    0 0 admin

  Is it what you need ? 

Regards,
Vlad  #yiv3993243899 #yiv3993243899 -- #yiv3993243899ygrp-mkp {border:1px solid 
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv3993243899 
#yiv3993243899ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv3993243899 
#yiv3993243899ygrp-mkp #yiv3993243899hd 
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 
0;}#yiv3993243899 #yiv3993243899ygrp-mkp #yiv3993243899ads 
{margin-bottom:10px;}#yiv3993243899 #yiv3993243899ygrp-mkp .yiv3993243899ad 
{padding:0 0;}#yiv3993243899 #yiv3993243899ygrp-mkp .yiv3993243899ad p 
{margin:0;}#yiv3993243899 #yiv3993243899ygrp-mkp .yiv3993243899ad a 
{color:#ff;text-decoration:none;}#yiv3993243899 #yiv3993243899ygrp-sponsor 
#yiv3993243899ygrp-lc {font-family:Arial;}#yiv3993243899 
#yiv3993243899ygrp-sponsor #yiv3993243899ygrp-lc #yiv3993243899hd {margin:10px 
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv3993243899 
#yiv3993243899ygrp-sponsor #yiv3993243899ygrp-lc .yiv3993243899ad 
{margin-bottom:10px;padding:0 0;}#yiv3993243899 #yiv3993243899actions 
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv3993243899 
#yiv3993243899activity 
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv3993243899
 #yiv3993243899activity span {font-weight:700;}#yiv3993243899 
#yiv3993243899activity span:first-child 
{text-transform:uppercase;}#yiv3993243899 #yiv3993243899activity span a 
{color:#5085b6;text-decoration:none;}#yiv3993243899 #yiv3993243899activity span 
span {color:#ff7900;}#yiv3993243899 #yiv3993243899activity span 
.yiv3993243899underline {text-decoration:underline;}#yiv3993243899 
.yiv3993243899attach 
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 
0;width:400px;}#yiv3993243899 .yiv3993243899attach div a 
{text-decoration:none;}#yiv3993243899 .yiv3993243899attach img 

[firebird-support] Re: Delegating SYSDBA and enumerating users

[hv...@users.sourceforge.net [firebird-support]]

A little sample with Firebird 2.5

I added ordinary users 'user1' and 'user2' and admin user 'adm1'.
Now try to display list of users

1. Use sysdba account:

firebird\bin>gsec -user sysdba -pass masterkey -display
 user nameuid   gid admin full name

SYSDBA  0 0   Sql Server Administrator
USER1   0 0
USER2   0 0
ADM10 0 admin

Of course, sysdba could see all users.
You see - adm1 is really admin user while user1 and user2 are not admins.


2. Ordinary users could see itself only:

firebird\bin>gsec -user user1 -pass u1 -display
 user nameuid   gid admin full name

USER1   0 0


3. What about non-sysdba admin ?

firebird\bin>gsec -user adm1 -pass adm1 -display
 user nameuid   gid admin full name

ADM10 0 admin

Something wrong ? Let see next sample


4. Specify admin role:

firebird\bin>gsec -user adm1 -pass adm1 -role rdb$admin -display
 user nameuid   gid admin full name

SYSDBA  0 0   Sql Server Administrator
USER1   0 0
USER2   0 0
ADM10 0 admin

  Is it what you need ? 

Regards,
Vlad

[firebird-support] Re: Delegating SYSDBA and enumerating users

[hv...@users.sourceforge.net [firebird-support]]

---In firebird-support@yahoogroups.com,  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.
 
  Does you read this chapter ?

https://www.firebirdsql.org/file/documentation/release_notes/html/en/2_5/rnfb25-admin.html#rnfb25-prvlgs-super
 
https://www.firebirdsql.org/file/documentation/release_notes/html/en/2_5/rnfb25-admin.html#rnfb25-prvlgs-super


 > 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.
 
  Ok


 > 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. 

  Does you pass RDB$ADMIN role name to a gsec command line ?

> The latter is also the case when using the .NET Provider and making the call 
> to FirebirdSql.Data.Services.FbSecurity.DisplayUsers(). 

  Does you specify RDB$ADMIN role when using Services API ?

> Via Flame Robin menu Server | Manager Users, you are prompted with the 
> Database Credentials dialog with Username pre-populated with 'SYSDBA' and 
> read-only.
 
  I don't know if Flame Robin ask for role in this dialog.

  It is enough for start :)

Regards,
Vlad