Re: [firebird-support] How non-SYSDBA user can see connections by other users?

[rem...@yahoo.com [firebird-support]]

> Yes, you can use database level on connect/on disconnect triggers. E.g.

FYI, one problem of this solution is that in case of a server crash / power 
outages the on disconnect trigger is never called. The same happens when a 
connection is killed by another user (DELETE FROM MON$ATTACHMENTS ...). So 
there must be another mechanism that cleans the CONNECTED_USERS table in such 
cases. At least in FB 2.5 where I tested it.

Regards, Michal

Re: [firebird-support] How non-SYSDBA user can see connections by other users?

[Ivan Přenosil ivan.preno...@seznam.cz [firebird-support]]

>  SYSDBA users can see other connected users using monitoring tables 
> but is it possible to implement such feature for non-SYSDBA users? Are 
> there event/triggers that act uppon connecting and disconnecting and 
> which can insert usual database records. Triggers no MON$ tables are 
> not suitable because MON$ tables are populated only during query time.

Yes, you can use database level on connect/on disconnect triggers. E.g.

CREATE TABLE CONNECTED_USERS (
   ID INTEGER,
   USERNAME VARCHAR(32));

GRANT SELECT ON CONNECTED_USERS TO PUBLIC;

SET TERM ^;
CREATE TRIGGER TR_ON_CONNECT ON CONNECT AS
BEGIN
   INSERT INTO CONNECTED_USERS(ID, USERNAME) VALUES (CURRENT_CONNECTION, 
CURRENT_USER);
END^
CREATE TRIGGER TR_ON_DISCONNECT ON DISCONNECT AS
BEGIN
   DELETE FROM CONNECTED_USERS WHERE ID=CURRENT_CONNECTION;
END^
SET TERM ;^

I.

Re: [firebird-support] How non-SYSDBA user can see connections by other users?

[Dalton Calford dcalf...@distributel.ca [firebird-support]]

You have a few choices in how to do this.


You can either upgrade your users to RDB$ADMIN role rights (not recommended) or 
you can grant the user to have rights to a view that gets it's data from a 
stored procedure that connects back to the database in a separate 
connect/transaction as a user that is a member of the RDB$ADMIN role - and you 
setup the database triggers so that the database prevents that specialized user 
from connecting to the database from any application other than the firebird 
server and the servers ip address (you can get this information from the mon$ 
tables).


This is a better way, but, due to someone being able to read the DDL, they may 
get a way to abuse your system by reading the system tables and getting the 
special users username/password/role.


It is better to have them connect to a different, more secure database, with a 
low end user account that can only run that one view, then that view, connects 
back to the primary database to get the information.   This keeps all the 
private DDL information away from anyone who has read access to the system 
tables.   So, even if they can read the metadata of the primary database, it 
does not give them the right to read the metadata of the second database, thus 
keeping the private credentials away from the user.


Also, you will need to filter the output to not show these hidden/system 
connections to the regular user.


There are a few other ways, but, they rely upon OS specific tricks that are a 
bit too complicated to mention in a simple email.


best regards


Dalton




From: firebird-support@yahoogroups.com  on 
behalf of 'Thomas Steinmaurer' t...@iblogmanager.com [firebird-support] 

Sent: October 20, 2016 10:30:17 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] How non-SYSDBA user can see connections by 
other users?



> SYSDBA users can see other connected users using monitoring tables but is it
> possible to implement such feature for non-SYSDBA users? Are there
> event/triggers that act uppon connecting and disconnecting and which can 
> insert
> usual database records. Triggers no MON$ tables are not suitable because MON$
> tables are populated only during query time.

The following users get the full picture when querying monitoring tables:

* Database owner
* SYSDBA
* Users as a member of the special per database RDB$ADMIN role. Role needs to 
be specified at connect time.

--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] How non-SYSDBA user can see connections by other users?

['Thomas Steinmaurer' t...@iblogmanager.com [firebird-support]]

> SYSDBA users can see other connected users using monitoring tables but is it
> possible to implement such feature for non-SYSDBA users? Are there
> event/triggers that act uppon connecting and disconnecting and which can 
> insert
> usual database records. Triggers no MON$ tables are not suitable because MON$
> tables are populated only during query time.

The following users get the full picture when querying monitoring tables:

* Database owner
* SYSDBA
* Users as a member of the special per database RDB$ADMIN role. Role needs to 
be specified at connect time.



--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] How non-SYSDBA user can see connections by other users?

[Stefan Heymann li...@stefanheymann.de [firebird-support]]

>> SYSDBA users can see other connected users using monitoring tables
>> but is it possible to implement such feature for non-SYSDBA users?

The only one who can also "see" connections from user users is the
database owner. Other than that I don't know of a way to do that.


Regards

Stefan

Re: [firebird-support] Looking for detailed documentation on the new Firebird 3 security authentication process.

[Dalton Calford dcalf...@distributel.ca [firebird-support]]

Hi Alain,


We use full user authentication at our company, so everyone logs into the 
database using their own credentials.  Currently, each user has an account in 
the firebird security database.   Each user also has a windows domain account.  
 This means each user has, at a minimum, two separate usernames and passwords 
to maintain, while IT staff have to be diligent to clean up users from the 
firebird security database, after a staff member has left the company.


When I log onto a Firebird Database, without providing username or password, on 
a linux host, the Firebird engine uses my local linux username as my Firebird 
username and I have any rights that the SYSDBA has granted to my linux 
username, even though, my linux user name is not in the firebird security 
database.


Firebird on windows, starting with the 2.x version, started to allow this 
behaviour and new security grant commands where created to allow for default 
rights (such as someone with administrative rights on the local machine 
automatically logging in as themselves with sysdba role access rights).   So, 
if you logged into your windows machine as 
"MY_COMPANY_DOMAIN\MY_DOMAIN_WINDOWS_USER_NAME" and opened a firebird 
connection as yourself, then you would see the above when you did a select 
current_user . in the database.


With Firebird 3.0, this has been extended so that trusted rights are passed 
from windows machine to windows machine in the same domain.   This is 
accomplished by the client, who verified the user via the domain 
authentication/password, sending a time/domain sensitive token to the server, 
which the server then uses to get the details about the user and provides the 
user ID to any software that requests it.  This means you only administrate one 
set of user accounts for all your databases and those are the same accounts 
used for machine login and OS/network rights.


So a user changes their domain password and immediately their firebird password 
changes as well.


This works on a windows to windows basis, but, when a windows client, tries to 
attach to a linux server using the same mechanism, the connection fails.   This 
is true even is the linux box is a full member of the domain via samba.


So, that is why Samba is important - it means the Linux User Authentication 
Method is linked to the Windows User Authentication Method and that means that 
the firebird database server does not need to maintain a separate security 
database for authentication as the OS handles that.   Of coarse, SQL rights are 
still managed and maintained within the database itself.


For people who are not familiar with domain trusts, linux or plugin 
authentication modules, could be confused by this.   It also is not needed by 
users who only use the SYSDBA account.


I am looking for as much infomation as I can get, in order to either write a 
module that queries the linux PAM system, by providing the user provided 
USERNAME/PASSWORD or, better yet, have it take care of the handshake with the 
domain for the use of the windows token.


I hope this explains why Samba is needed, why this is different from actual 
grants and what my questions where about.


I am asking here as I am trying to determine if this is already available but 
the documentation is hard to find, or, barring that, I will in turn ask on the 
development list.


best regards


Dalton


From: Alain Bastien 
Sent: October 20, 2016 4:50:34 AM
To: Dalton Calford
Subject: Fwd: [firebird-support] Looking for detailed documentation on the new 
Firebird 3 security authentication process.

May I reply ?

As far as I know and performed the same experience,  Only the Grant function 
SYSDBA gives to the user to a DATABASE and/or specific VIEWS and/or TABLES

are enough.  SAMBA access has nothing to do with.

Is that your issue ?



Kind Regards

Alain Bastien
34 Dr Ross Avenue
Rose Hill 72102
Mauritius
Mobile Tel: +230  5 719 30 30
Skype:alainbastien
Viber: 7320143





-- Forwarded message --
From: Dalton Calford dcalf...@distributel.ca 
[firebird-support] 
mailto:firebird-support@yahoogroups.com>>
Date: Wed, Oct 19, 2016 at 10:03 PM
Subject: [firebird-support] Looking for detailed documentation on the new 
Firebird 3 security authentication process.
To: "firebird-support@yahoogroups.com" 
mailto:firebird-support@yahoogroups.com>>




Hi Everyone.


I have a linux machine (Ubuntu 16.04 64bit Server) with Firebird 3.01 64 bit 
installed.

That machine is a member of our corporate domain and authenticates via 
PAM/Samba4 for all user access.


I want to have Firebird client applications on remote windows machines to use 
the linux user authentication (PAM/DOMAIN) instead of a security database.


Is this currently possible?Is this theorectically possible?   Where can I 
find documentation for this?


best regards


Dalton

[firebird-support] How non-SYSDBA user can see connections by other users?

[jonatan.laurit...@yahoo.dk [firebird-support]]

SYSDBA users can see other connected users using monitoring tables but is it 
possible to implement such feature for non-SYSDBA users? Are there 
event/triggers that act uppon connecting and disconnecting and which can insert 
usual database records. Triggers no MON$ tables are not suitable because MON$ 
tables are populated only during query time.

J.