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

[Mark Morgan Lloyd]

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:
>> It identifies itself as 2.5, and by that I explicitly mean that I'm
>> querying it rather than trusting any claims by the Debian package
>> maintainer, and according to the documentation that's the version at
>> which rdb$admin system role was added.
>>
>> I suspect that I'm supposed to be doing something like granting
>> rdb$admin to the user concerned in the security database, but so far I
>> can't work out how.
> 
> Glad you trust the Debian maintainers then ;)

If I can get this code to fly, I'm anticipating that at least some 
people will want to set up their own servers acting in concert with the 
remainder of the tree. That implies that I've got to document e.g. 
initial user and table creation properly (i.e. to allow enough software 
to run that it can take over management), and also that I've got to be 
very careful checking server versions etc.

So in this context I trust package maintainers about as far as i can 
spit a rat :-)

> 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.

> 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..."

> Don't forget this:
> To make use of his RDB$ADMIN privileges, the grantee simply specifies
> the role when connecting to the database.
> i.e. TIBConnection.Role:='RDB$ADMIN'

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

*  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()?

I'd very much like to be able to use SQL commands for this, since it 
makes management and logging far easier.

-- 
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]