[firebird-support] Re: Starting auditing session

2017-02-21 Thread hv...@users.sourceforge.net [firebird-support] 
---In firebird-support@yahoogroups.com,  wrote :
 
 > I've started looking into Firebird tracing capabilities and there seems to
> be very little documentation on the topic besides the official manual and
 > a few commercial applications that are well known.
 
  Does you read
https://www.firebirdsql.org/file/documentation/release_notes/html/en/2_5/rnfb25-trace.html
 
https://www.firebirdsql.org/file/documentation/release_notes/html/en/2_5/rnfb25-trace.html
and 
https://www.firebirdsql.org/file/documentation/release_notes/html/en/2_5/rnfb25-apiods-api.html#rnfb25-apiods-api-svctrace
 
https://www.firebirdsql.org/file/documentation/release_notes/html/en/2_5/rnfb25-apiods-api.html#rnfb25-apiods-api-svctrace
?

 > I am aware of the "fbtracemgr" utility but I would like to handle things
> myself.
 
  Good. Note, you need not "auditing" session but usual "user trace" session.
Audit is a bit different, read the docs, please.

 > First of all: how do I start a tracing session? Is it a sql command that I
> call like a function or I select from like in a procedure?
 
  FIrst, you need to know how to work with Services API.
Second, read again 
https://www.firebirdsql.org/file/documentation/release_notes/html/en/2_5/rnfb25-apiods-api.html#rnfb25-apiods-api-svctrace
 
https://www.firebirdsql.org/file/documentation/release_notes/html/en/2_5/rnfb25-apiods-api.html#rnfb25-apiods-api-svctrace
 
  Also, you can read source code of fbtracemgr
https://github.com/FirebirdSQL/firebird/blob/B2_5_Release/src/utilities/fbtracemgr/traceMgrMain.cpp
 
https://github.com/FirebirdSQL/firebird/blob/B2_5_Release/src/utilities/fbtracemgr/traceMgrMain.cpp

 > Second: how do I feed Firebird the tracing session configuration file?
> Does it need to be a "physical" file on the same server as FB or can it be
> read from like STDIN?

  If you read docs, you know that trace configuration is sent as a part of SPB

Hope it helps,
Vlad

[firebird-support] Starting auditing session

2017-02-21 Thread ch...@vp44.net [firebird-support] 
Hi all.


I've started looking into Firebird tracing capabilities and there seems to
be very little documentation on the topic besides the official manual and
a few commercial applications that are well known.


I am aware of the "fbtracemgr" utility but I would like to handle things
myself.


First of all: how do I start a tracing session? Is it a sql command that I
call like a function or I select from like in a procedure?


Second: how do I feed Firebird the tracing session configuration file?
Does it need to be a "physical" file on the same server as FB or can it be
read from like STDIN?


Thanks!

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

2017-02-21 Thread 'Alan McDonald' a...@meta.com.au [firebird-support] 
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$UIDINTEGER,
RDB$GIDINTEGER,
RDB$PASSWD VARCHAR(64) CHARACTER SET OCTETS NOT NULL,
RDB$PRIVILEGE  INTEGER,
RDB$COMMENTBLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET 
UNICODE_FSS,
RDB$FIRST_NAME VARCHAR(32) CHARACTER SET UNICODE_FSS DEFAULT 
_UNICODE_FSS '',
RDB$MIDDLE_NAMEVARCHAR(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' a...@meta.com.au 
[firebird-support]"  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,

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

2017-02-21 Thread cerrogrand...@yahoo.ca [firebird-support] 
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' a...@meta.com.au 
[firebird-support]"  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:#ff;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

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

2017-02-21 Thread 'Alan McDonald' a...@meta.com.au [firebird-support] 
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;

[firebird-support] Delegating SYSDBA and enumerating users

2017-02-21 Thread cerrogrand...@yahoo.ca [firebird-support] 
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

[firebird-support] dropping the connection

2017-02-21 Thread Nick Upson n...@telensa.com [firebird-support] 
Hi

The problem I'm trying to solve is users leaving inactive db_workbench
sessions attached to a database while other work is going on by other
processes, causing a large OIT gap and the consequent problems. The
solution needs to be automatic and unattended.

db_workbench doesn't have a timeout setting (I asked)

deleting the row from mon$statements doesn't kill the transaction;
deleting the row from mon$transactions or mon$attachments gives "operation
not supported"

I could automate a kill of the fb_inetserver process but I would rather
something with less risk

I figure other people have had this and solved it before, I hope.

currently using firebird 2.1 on centos, shortly moving to 2.5

[firebird-support] Re: Error registered in Windows event Viewer

2017-02-21 Thread michael.vilhelm...@microcom.dk [firebird-support] 
Thanks for the answer. 

Mivi

[firebird-support] Re: Error registered in Windows event Viewer

2017-02-21 Thread Dmitry Yemanov dim...@users.sourceforge.net [firebird-support] 
21.02.2017 15:53, michael.vilhelm...@microcom.dk wrote:
>
> ANS4005E Error processing
> '\\db07\c$\ProgramData\firebird\fb_lock_068acd221200a400': file
> not found
>
> What does this mean?
>
> Should I be worried?

Nothing to worry about. Some MS or 3rd party software (backup? 
antivirus?) attempts to scan files in the FB directory but fails to do 
that for the lock-file being removed by the FB engine in the meantime.


Dmitry

[firebird-support] Error registered in Windows event Viewer

2017-02-21 Thread michael.vilhelm...@microcom.dk [firebird-support] 
Hi

We have a server running Firebird 2.5.6 SuperServer.On a Windows 64 bits. 

Every once in a while we get this in the Event Viewer:

 ANS4005E Error processing 
'\\db07\c$\ProgramData\firebird\fb_lock_068acd221200a400': file not 
found
  
Eventid: 4099 Source: AdsmClientService
  
  
 XML Version of the same. 

 http://schemas.microsoft.com/win/2004/08/events/event 
http://schemas.microsoft.com/win/2004/08/events/event;>
   
  
 4099 
 2 
 0 
 
0x80 
  
 
22033 
 Application 
 
DB07.ephosting.mic 
  
   
   
 ANS4005E Error processing 
'\\db07\c$\ProgramData\firebird\fb_lock_068acd221200a400': file not 
found 
   
 
  
 Everything seems to run perfectly, and after some hours it goes away. 
Sometimes almost a day. 

 The FIREBIRD.LOG has no entrys.

 What does this mean?
 Should I be worried?