No privilege for this operation

Extracted code from documentation,

create table sales_catalog(
 item_id varchar(10) not null primary key,
 item_name_desc varchar(50) not null,
 item_desc varchar(50));

Error after using SYSDBA as the user in Firebird SQL3.

Statement failed, SQLSTATE = 42000
 unsuccessful metadata update
 CREATE TABLE SALES_CATALOG failed
 There is no privilege for this operation.

Solution

I did some experimenting, and the problem seems to be that if you connect to a database using ISQL without specifying a host name, it will use Firebird embedded to connect to the database (previous versions of Firebird didn't do that on Windows).

Firebird embedded does not require a username and password as it assumes that if you have direct read/write access to the database, that you are allowed to connect to it.

When you connect without specifying a username and password (eg using connect 'database.fdb' instead of connect 'database.fdb' user sysdba, Firebird embedded will use your OS username to connect.

This can be checked because ISQL reports the username when connecting:

SQL> connect 'd:\data\db\fb3\dbofnormal.fdb';
Database: 'd:\data\db\fb3\dbofnormal.fdb', User: MARK

Firebird 3 added new metadata privileges, for example creating a table in a database now requires that you are either the owner of the database (the username used in the create database statement), sysdba (or another admin user), or that you have the create table privilege. See also User Privileges for Metadata Changes. In earlier version any user would be allowed to create tables once they had access to the database.

Now on to the problem: the user (in my example MARK), does not have the create table privilege, so attempting to do so will fail:

SQL> create table testmark ( id integer generated by default as identity primary key);
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-CREATE TABLE TESTMARK failed
-There is no privilege for this operation

There are a few ways to solve this:

Specify a user with sufficient privileges in the connect statement (eg sysdba):
```
connect 'database.fdb' user sysdba;
```
Include the host name in the connect statement to connect through Firebird server instead of Firebird embedded, so that you are required to specify user name and password:
```
connect 'localhost:database.fdb' user sysdba password 'masterkey';
```
Connect once to your database as sysdba (see first item), and give the necessary privileges to the user (in my case mark):
```
grant create table to user mark;
```
From this moment forward this user can create tables (you may need to grant additional privileges, eg create view, create procedure, etc).