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. _______________________________________________ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
