[fpc-pascal] Re: Connecting to Firebird using FPC/Lazarus over a LAN with user creation privilege

[Reinier Olislagers]

On 10-3-2013 12:03, Mark Morgan Lloyd wrote:
> Reinier Olislagers wrote:
>> On 9-3-2013 19:56, Mark Morgan Lloyd wrote:
>>> Reinier Olislagers wrote:
>>>> On 9-3-2013 17:31, Mark Morgan Lloyd wrote:
>> Well...
>> Background: Create user via SQL:
>> http://www.firebirdsql.org/refdocs/langrefupd25-security-sql-user-mgmt.html
>>
>> CREATE USER mark PASSWORD '8charmax' -- if you want to let that user add
>> other users, add GRANT ADMIN ROLE
>>
>> GRANT ADMIN ROLE gives the new user the RDB$ADMIN role in the security
>> database. This allows him to manage user accounts, but doesn't give him
>> any special privileges in regular databases.
> 
> But I specified that when using isql to set up the initial users, and
> (in the context of isql) it didn't appear to have any effect until I
> also explicitly added rdb$admin.
Note that you have
- GRANT ADMIN ROLE when creating the user (which is created in the FB
security db). This allows that user to add users etc. as you
demonstrated in your initial post
- GRANT rdb$admin to whomever when connected to a certain db: this gives
the user the permission to do anything he wants with that db/grants
admin permissions.

Are you saying you had to run a GRANT rdb$admin to whoever when
connected to the security db? IIRC (and as you probably found out)
connecting to the security db with isql/regular SQL clients has been
blocked.
The only way to connect is through the services API (or indirectly via
CREATE USER etc)

> 
>> Yes granting rdb$admin for full control seems like a good idea:
>> http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-security-rdbadmin.html
>>
>> Connect to the target db
>> GRANT RDB$ADMIN TO mark
>> (execute as e.g. SYSDBA)
> 
> (From isql) I'm currently getting failures like "no permission for
> direct access to security database..."
Yep, you need to connect to the target/newly created db, not the
security db.


> Yes, I'd got there after finding that it was required by isql etc. My
> current position appears to be that
> 
> *  Having run isql as root and set up a user borg_um with admin rights
By default [1], the account under which you run isql has no impact on FB
security (in contrast to postgresql where you have a dedicated
postgresql account).
Using user SYSDBA would be the "regular" way to run isql

[1] not really true, there is a trusted authentication mode where the OS
username is used in FB 2+ (IIRC the version). Haven't tried that.
> 
> *  and having added rdb$admin to it
> 
> *  I can then tell isql to run as borg_um and create unprivileged users.
> 
> But I can't do that last step from a TIBConnection. There's obviously a
> possibility that I'm Doing It Wrong: what's the Firebird equivalent of
> PQExec(), I'm using isc_dsql_execute_immediate()?
Strange.
Setting the user to borg_um, the role to RDB$ADMIN, correct password,
the db to the newly created db, then connecting with the right
transactions etc and doing something like TIBConnection.Execute('CREATE
USER...') (or whatever the actual command is) should work.

> I'd very much like to be able to use SQL commands for this, since it
> makes management and logging far easier.
If you don't get it sorted, let me knoe & I'll try and set up a demo
program.