Jump to content
Henry Olive

FB3 Get Connected Users List

Recommended Posts

I wish everyone a healthy & happy new year
 

I used to get Connected Users List in Interbase like below


SELECT DISTINCT TM.TMP$USER_HOST COMPUTERNAME, U.LONGNAME,U.ADMINRIGHT,
                  U.DEPARTMENT, U.IP_ADRESS,U.USERNAME,U.ADMINRIGHT,
                  Case When E.TELLIST <> ''No'' then E.EXTENSION else '''' end as EXTENSION
                  FROM TMP$ATTACHMENTS TM
                  JOIN URIGHT U ON U.COMPUTERNAME=TM.TMP$USER_HOST
                  JOIN EMPLOYEE E ON E.EMPNO=U.EMPNO
                  WHERE TMP$USER_HOST IS NOT NULL

 

I tried below code in FB3,08  but it is not what i'm looking for

SELECT RDB$USER
FROM RDB$USER_PRIVILEGES
group by 1

Could some please convert above IB SQL to FB SQL
Thank You

 

 


 

Share this post


Link to post

Hi, and happy new year.

 

As far as I know about Firebird the system tables are prefixed MON$, RDB$ and (SEC$  starting with FB 3.0)  not TMP$

 

to get privilege(s) on a table you can use

select RDB$USER,LIST(RDB$PRIVILEGE) from rdb$user_privileges where RDB$RELATION_NAME=<tablename> GROUP BY 1

but some fields can be involved too so in this "list" you can find some repetitions

Here is the result of a

SELECT a.RDB$USER,  a.RDB$RELATION_NAME,-- a.RDB$FIELD_NAME,
LIST(a.RDB$PRIVILEGE) RIGHTS
FROM RDB$USER_PRIVILEGES a 
WHERE a.RDB$RELATION_NAME='LLANCEMENT'
GROUP BY 1,2;
Quote

RDB$USER        RDB$RELATION_NAME    RIGHTS
FOURNISSEUR  LLANCEMENT                         S     ,U     ,U     ,U     ,U     ,U     ,U     ,U     ,U     ,U     ,U     ,U     ,U     
SYSDBA            LLANCEMENT                         D     ,I     ,R     ,S     ,U     
USINE               LLANCEMENT                         S     ,U     ,U     ,U     ,U     ,U     ,U     ,U     ,U     ,U     ,U     ,U     

But this don't give the "reality" of the rights user FOURNISSEUR (supplier) don't have the same field access than USINE (factory)

Quote

RDB$USER    RDB$RELATION_NAME    RDB$FIELD_NAME    RIGHTS
FOURNISSEUR                        LLANCEMENT                         [null]    S     
FOURNISSEUR                        LLANCEMENT                         COLIS                              U     
FOURNISSEUR                        LLANCEMENT                         COMPLEMENT                         U     
FOURNISSEUR                        LLANCEMENT                         DATE_DEPART                        U     
FOURNISSEUR                        LLANCEMENT                         DATE_DEPART1TIMESTAMP              U     
FOURNISSEUR                        LLANCEMENT                         DATE_DEPART2                       U     
FOURNISSEUR                        LLANCEMENT                         DATE_DEPART2TIMESTAMP              U     
FOURNISSEUR                        LLANCEMENT                         DATE_DEPART3                       U     
FOURNISSEUR                        LLANCEMENT                         DATE_DEPART3TIMESTAMP              U     
FOURNISSEUR                        LLANCEMENT                         DATE_LIV                           U     
FOURNISSEUR                        LLANCEMENT                         LAST_MODIF                         U     
FOURNISSEUR                        LLANCEMENT                         NOTE2                              U     
FOURNISSEUR                        LLANCEMENT                         REMARQUE                           U     
SYSDBA                             LLANCEMENT                         [null]    D     ,I     ,R     ,S     ,U     
USINE                              LLANCEMENT                         [null]    S     
USINE                              LLANCEMENT                         COMPLEMENT                         U     
USINE                              LLANCEMENT                         DATE_DEPART                        U     
USINE                              LLANCEMENT                         DATE_DEPART1TIMESTAMP              U     
USINE                              LLANCEMENT                         DATE_DEPART2                       U     
USINE                              LLANCEMENT                         DATE_DEPART2TIMESTAMP              U     
USINE                              LLANCEMENT                         DATE_DEPART3                       U     
USINE                              LLANCEMENT                         DATE_DEPART3TIMESTAMP              U     
USINE                              LLANCEMENT                         FIN_FABS                           U     
USINE                              LLANCEMENT                         LAST_MODIF                         U     
USINE                              LLANCEMENT                         NOTE1                              U     
USINE                              LLANCEMENT                         REMARQUE                           U     

And take in mind you can change ROLE.

 

I never really looked into the possibility of changing the access to the editing areas of a shape from the rights declared in a Firebird database, Interesting challenge for my future retirement

Share this post


Link to post

At least in FB2.5 only SYSDBA could view all attachments, other users only see their own connections which is useless. To get more accessible connection list, you need onConnect & onDisconnect triggers that would add/remove records from a table

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×