Firebird: How do I automate multiple database+user creation lazarus / 16-Databases ---------------------------------------------------------------------- Michael Collier · 2016-05-24 11:59 I'm hoping to provide firebird database facilities to remote users connecting to my PC. I guess I'm looking for SQL script (or scripts) that would do the following: 1. Create a firebird database in the users home directory, something like the firebird documentation would probably work (I have success with this): CREATE DATABASE 'C:\user1\user_database.fdb' page_size 8192 2. Grant admin rights to the user and to their own database, so user1 would be able to create-tables/insert/delete/drop-tables etc, but wouldn't have access to any other users databases. 4. Grant read-only rights to the users database for a login name such as "guest" - for report tools to use. I'm not totally up to speed on firebird security/hierarchy and their tool (gsec) might not be what I should be using. I'm reading at the moment, but has anyone done this already and has a selection of example scripts that I can fire right away please? Or generally advise me on how to go about this please? Thanks ---------------------------------------------------------------------- balazsszekely · 2016-05-24 12:37 Drop a TSQLScript to your form, set Database and Transaction properties, then: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   SQLScript1.Script.Add('GRANT ALL ON TEST_TABLE TO USER1 WITH GRANT OPTION;');  SQLScript1.Script.Add('COMMIT WORK;')  SQLScript1.Execute;This is just a basic example, you really need to read this:   1. http://www.firebirdsql.org/refdocs/langrefupd25-security- rdbadmin.html   2. http://www.firebirdsql.org/refdocs/langrefupd25-security-grant- revoke.html ---------------------------------------------------------------------- Graeme · 2016-05-25 08:13 @Michael: Adding to this… You might want to take a look at Firebird 3.0 too if you can. It has so many long awaited features included. One being, per-database configuration, per-database security DB — all of which might also be useful to you. ---------------------------------------------------------------------- Michael Collier · 2016-05-25 09:10 Thanks for the SQL and firebird3 recommendations. I have success on windows but installation issues on linux are getting in the way now. I have posted a separate thread about it here http://forum.lazarus.freepascal.org/index.php/topic,32742.0.html For the matter at hand though (giving users access to their own databases) the following approach seems to work for me on firebird3. 1. The CREATE DATABASE command worked OK via an ODBC connection but not using IBConnection or Zeos connection - they both tried to prepare the SQL and gave errors, I tried executeDirect() but didn't work. In the end I decided I would just copy an existing firebird database file (ie database.fdb)  into the users sub-directory. Hopefully this will be satisfactory... 2. Use IBConnection to connect to the copied database and execute something like the following (I seemed to need to do both but later discovered my ADO/ODBC browsing tool needing closing/opening so maybe both are not needed..) the user needs to specify RDB$ADMIN in the role parameter of their ODBC connection or they won't be able to create tables etc: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   CREATE USER username PASSWORD 'password'  GRANT ADMIN ROLE ;  Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i GRANT RDB$ADMIN TO username ;  3. Create a read_only user, something like: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i CREATE USER guest PASSWORD 'guest' ;  4. To give SELECT only access to the guest user, it seems that this has to be done after tables have been created, I couldn't find a way to allow SELECT on ALL (thus future) tables. So I'm giving my users a tool to generate GRANT SELECT statements on the current list of table names via ibconnection.gettablenames() procedure. If anyone has anything to add I'd be interested to hear... ---------------------------------------------------------------------- balazsszekely · 2016-05-25 09:43 Quote@Michael Collier If anyone has anything to add I'd be interested to hear... In my opinion your approach is an overkill.  I would use a single database with a login system. When a specific user logs in, you query the rights from a table and enable/disable features accordingly. ---------------------------------------------------------------------- hrayon · 2016-05-25 10:10 Maybe he wants something like that web hosting providers offer ---------------------------------------------------------------------- Michael Collier · 2016-05-25 10:14 Quote from: hrayon on May 25, 2016, 12:10:51 pmMaybe he wants something like that web hosting providers offer Yes that's correct ---------------------------------------------------------------------- hrayon · 2016-05-25 15:03 I do not know how the web hosting providers do exactly, but I think the setting you want is on file level control within the operating system, or the web server, not directly controlled by the database. I do not know if this will help in something: https://sourceforge.net/projects/ibwebadmin/ ---------------------------------------------------------------------- Michael Collier · 2016-05-26 09:18 Ok thanks for the web link, it wasn't what I was looking for at first but may be useful in the future (I'll install and play with it today). Everything else is working fine now, thanks everyone, solved.