No privilege for this operation

Question

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.

You might want to include the full error message, not just the first line. The error suggests that you are not actually connecting as sysdba, because your user doesn't seem to have the create table privilege (which is a feature added in Firebird 3, before users could always create new objects in a database); and sysdba always has that privilege — Mark Rotteveel, Jul 15 '16 at 12:07
Could you post the output from `SELECT CURRENT_USER, SEC$USER_NAME, SEC$ACTIVE, SEC$ADMIN FROM SEC$USERS` — Mark Rotteveel, Jul 15 '16 at 12:14
Did you by any chance connect without specifying a username and password? (eg using `CONNECT 'database.fdb';`)? — Mark Rotteveel, Jul 15 '16 at 12:29
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/117470/discussion-between-kvk30-and-mark-rotteveel). — kvk30, Jul 16 '16 at 05:59

score 1 · Accepted Answer · answered Jul 16 '16 at 05:50

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

@kvk30 See [ISQL Command Line Switches](http://www.firebirdsql.org/file/documentation/reference_manuals/user_manuals/html/isql-switches.html) (option `-i` or `-input`), and [ISQL Script Handling](http://www.firebirdsql.org/file/documentation/reference_manuals/user_manuals/html/isql-scripts.html) — Mark Rotteveel, Jul 18 '16 at 05:59
Hello mark, for firebird 3. Do they have inbuilt ODBC driver ? — kvk30, Jul 20 '16 at 05:17
No, the ODBC driver is a separate download, see http://www.firebirdsql.org/en/odbc-driver/ — Mark Rotteveel, Jul 20 '16 at 07:32

No privilege for this operation

1 Answers1