2

I have a server that will manage multiple Firebird databases. My users are allowed to have full access on all the objects for a single database but they are not allowed to create new database and no access to other databases in the same server. How do I configure for this scenario?

Mark Rotteveel
  • 82,132
  • 136
  • 114
  • 158
Sherlyn Chew
  • 139
  • 1
  • 2
  • 8

1 Answers1

2

Since Firebird 3, users need to have an explicit privilege to create databases (Database DDL Privileges). So as long as you don't grant that privilege to a user, they cannot create a database (unless they have and apply the RDB$ADMIN role).

Giving a user access to everything in a database is harder to do. You'll need to define the access for each database object individually. The preferred way to do that, is to grant the necessary access to a role, and grant that role to the user. See SQL Privileges for details.

In Firebird 3 and earlier, users do not assume the rights of a role unless they explicitly specify that role on connect. Firebird 4 will introduce default roles, which will always be applied. It might be possible to use Firebird 3 privilege mapping to define a default role as well, but I'm not sure if that works.

Firebird does not provide a way to disallow a user to connect to a database: almost all rights are stored per database, so a user has to connect before the server knows which rights they have. If you want to disallow users to connect, you will need to create a custom ON CONNECT trigger to raises an exception for users that shouldn't be allowed to connect.

Mark Rotteveel
  • 82,132
  • 136
  • 114
  • 158
  • I'm thinking to create private security database for each databases in server and each security databases has one RDB$ADMIN with different password. So the users able to have full access for all the tables but they are unable to access other databases and create database in server. Is this a good way? – Sherlyn Chew Jan 03 '20 at 07:55
  • @SherlynChew Possibly, I haven't worked with multiple security databases much, so I don't know the ins and outs of those. – Mark Rotteveel Jan 03 '20 at 07:58
  • @SherlynChew you can even configure every database to be its own security database. However, you would need to ensure no two databases have the same user (or at least not with the same password). Username is just a string, it can be the same in number of databases. And i think there would be some tables with `PUBLIC` `select` grants in any practical database. Even if ones like `RDB$RELATIONS`. "one RDB$ADMIN with different password" - RDB$Admin is a role, not a user. Roles do not have passwords. – Arioch 'The Jan 04 '20 at 17:57