Firebird Security DB and Users Table

[dve83]

Hello,

Im still in my 'infant' stages with zeoslib and firebird db. I am attempting to allow my application to connect to the Security Database (in some way or the other) with an Administrative account: My purpose is to select from the USERS table to determine available database users. I also wish to allow (via application interface) creation of new users (in my own database and simultaneously add them as Database Users) in the security database.

From what I've read I cannat connect directly to the security2.fdb and I must do so via the API.

Any advice / direction would be greatly appreciated ! :-) thanks
dve83

[EgonHugeist]

dve83,

Hope this is right now: 'select * from RDB$USERS'; should be the call you need.

Downoall FlameRobin this is a very small but great tool. Here you can see all system-tables and then you can build your own statements...

Michael

[dve83]

Hi,

thanks for the reply. Im assuming I can be logged on to any database (as long as its with the dbadmin account)? Perhaps Im just being dumb: I get the following error


---------------------------
ptest
---------------------------
SQL Error: Dynamic SQL Error SQL error code = -204 Table unknown RDB$USERS At line 1, column 15. Error Code: -204. Undefined name The SQL: select * from RDB$USERS

;
---------------------------
OK
---------------------------


Perhaps tis is because I have not yet added secondary users? Does this table contain the SYSDBA account? Or does it only exists / contain newly defined database users.

Many thanks

[EgonHugeist]

Oh i'm sorry. Couldn't check it, wasn't on my computer.

the rigth call: 'select * from RDB$USER_PRIVILEGES;'

Michael

[dve83]

Hi again, thanks again for the reply. The USER_PRIVILEGES table contains roles / privileges assigned to existing users. I think what Im looking for is the table containing the users only (even before I've assigned any roles to it). As far as I can understand, this resides in the Security2.fdb. My reasons for asking: I wish to provide a admin screen in my application where database users can be managed. For this I would need a list of existing users (there after allowing add / edit / delete for the admin).

Hope this make sense

[EgonHugeist]

dve83,

Sorry no idea. Somebody else?

Michael

[dve83]

thank you though.

For any other input, herewith what I received from the firebird mailing list at yahoo. It seems I need access to the Services API. Is this available? If not, is there a workaround OR could you direct me to the correct API calls in the PAS units.

Code: Select all

>Hello,
>
>I thank you for the reply. Please forgive my limited understanding of Firebird DB thus far. I understand that security2 is the replacement for the previous security.fbd and that now I cannot connect to it (even with DBAdmin).
>
>You mentioned using the API. COuld you perhaps direct me to documentation (I am currently working via ZeosLib libraries that interface with the dll's).
If you are using ZeosLib then you are using the API. If you are trying to use old components with newer releases of Firebird then some functions that were previously available to ordinary users through a regular database connection (such as adding, deleting and modifying users) are NOT available to ordinary users. The old API documentation won't help much as it was written for InterBase before the days of security awareness, when services were openly available to ordinary uses via database parameters.

I haven't looked at ZeosLib for years but it in the past it didn't support the Services API, which exposes the code in the engine that is used by gsec, gbak, etc. to the programmer, putting it under the control of a subsystem known as the Services Manager. It is the recommended route for all server-level activities. The VERY old functions were (and still are) in the database parameter block but have been deprecated in Firebird for most of its life. From v.2.0.6 and (I think) 2.1.1, only SYSDBA could connect using these parameters.

Check whether the current version of ZeosLib provides support for the Services API: you would be looking for components whose names include clues to the specific Services API functions (such as backup, restore, user, statistics, and so on).

>From v.2.0 onward, connecting directly to security2.fdb by any means, by any user, is not allowed. All access is through the Services Manager, which makes an updatable view from the users table available to the client (gsec and other applications using the Services API).

As others have told you, v.2.5 made an interface to security2.fdb available for clients through SQL requests when connected to any user database. Ordinary users can access only their own account and only to change their passwords; SYSDBA or equivalent can access multiple accounts and add, modify or delete users.

[EgonHugeist]

dve83,

Ok here i can help you. I like that idea. Look to \Plain\ZPlainInterbase.Pas. This unit contains the firebird API. Also do we have version descendants which can help you here too. Be sure you use the latent svn files.

Fine would be if you share your findings/patches.

Michael

[dve83]

awsome. I will take a look and let you know.

[EgonHugeist]

dve83,

didn't hear something for a long time. Did you manage or broke your aproach?