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

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 <firebird-support@yahoogroups.com> on behalf of 'Thomas Steinmaurer' ts@... [firebird-support] <firebird-support@yahoogroups.com>
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.