How can I create a view in Firebird 5.0 that uses a table the creator doesn’t have privileges on?

[Arkadiusz Patynowski]

I have a database on a Firebird 5.0 server. In the database, I have a view named TEST created by the user ADMIN. This user is an administrator, but does not have full privileges on all metadata. The TEST view retrieves user names from the USERS table. The user STANDARD has privileges on the TEST view and the USERS table. I connect to the database using the STANDARD user. When I try to read data from the TEST view, I get an error that says: no permission for SELECT access to TABLE USERS. Effective user is ADMIN. Is the only solution to grant privileges to the ADMIN user or to create the view as a user who has privileges on all metadata? I want certain users to be able to add new views without having to grant privileges on all the objects those views use. I’ll also add that the database was migrated from version 2.5, where this problem did not occur.

I’ve already tried changing the SQL_SECURITY mode for the entire database, because I thought it was set to DEFINER instead of INVOKER. But that didn’t help. I also tried changing the owner of the view to SYSDBA, but that’s not possible without recreating the view. The only thing that works is granting the ADMIN user privileges on the USERS table. But in version 2.5 this wasn’t necessary. I have many views with a lot of dependencies. These views are created by different users. I’d like users to be able to add views without granting additional privileges, and for whether the STANDARD user can read something to depend on that user’s privileges.

[Vlad Khorsun]

I have a database on a Firebird 5.0 server. In the database, I have a view named TEST created by the user ADMIN. This user is an administrator, but does not have full privileges on all metadata. The TEST view retrieves user names from the USERS table. The user STANDARD has privileges on the TEST view and the USERS table. I connect to the database using the STANDARD user. When I try to read data from the TEST view, I get an error that says: no permission for SELECT access to TABLE USERS. Effective user is ADMIN. Is the only solution to grant privileges to the ADMIN user or to create the view as a user who has privileges on all metadata? I want certain users to be able to add new views without having to grant privileges on all the objects those views use. I’ll also add that the database was migrated from version 2.5, where this problem did not occur.

  Both LangRefs for v2.5 and v.5 say: 

> To create a view, a non-admin user needs at least SELECT access to the underlying table(s) and/or view(s), and the EXECUTE privilege on any selectable stored procedures involved.

 

I’ve already tried changing the SQL_SECURITY mode for the entire database, because I thought it was set to DEFINER instead of INVOKER. But that didn’t help. I also tried changing the owner of the view to SYSDBA, but that’s not possible without recreating the view. The only thing that works is granting the ADMIN user privileges on the USERS table. But in version 2.5 this wasn’t necessary. I have many views with a lot of dependencies. These views are created by different users. I’d like users to be able to add views without granting additional privileges, and for whether the STANDARD user can read something to depend on that user’s privileges.

Will be good to have minimal script with all necessary GRANT and CREATE statements to better understand the problem.

Regards,

Vlad

[Arkadiusz Patynowski]

Regarding the first part of your answer: the ADMIN user connects to the database with the RDB$ADMIN role and has the Administrator checkbox selected in the user manager.

For the second part, here are the scripts showing the assumed view:
/******************************************************************************/

/******************************************************************************/
/****     Following SET SQL DIALECT is just for the Database Comparer      ****/
/******************************************************************************/
SET SQL DIALECT 3;



/******************************************************************************/
/****                                Views                                 ****/
/******************************************************************************/


/* View: TEST */
CREATE OR ALTER VIEW TEST(
    <list of fields>)
AS
select * from operator
;




/******************************************************************************/
/****                              Privileges                              ****/
/******************************************************************************/


/* Privileges of users */
GRANT ALL ON TEST TO USER ADMIN WITH GRANT OPTION;
GRANT ALL ON TEST TO USER STANDARD;

/* Privileges of views */
GRANT ALL ON OPERATOR TO VIEW TEST;

---------------------------------------------------------------------------------------------------------------

Additionally:
The STANDARD user has privileges for the operator table.
The ADMIN user does not have privileges for the operator table.

Effect:
In version 5.0 when the STANDARD user tries to display data from the view, they receive a message that the ADMIN user does not have effective privileges for the OPERATOR table.
In version 2.5, STANDARD users can view data.

[Vlad Khorsun]

Regarding the first part of your answer: the ADMIN user connects to the database with the RDB$ADMIN role and has the Administrator checkbox selected in the user manager.

  How this is coresponds with your statement: " I want certain users to be able to add new views without having to grant privileges on all the objects those views use. " ?

And, later: "The ADMIN user does not have privileges for the operator table. "

 

For the second part, here are the scripts showing the assumed view:

I didn't ask for DDL only, I ask for the script to reproduce the issue. For example, I should guess who is creator of which object.

I have no time nor wish to recreate it for you, sorry.

  So, please, provide the full script that works on FB2.5 but not on FB5.

Regards,

Vlad

[Arkadiusz Patynowski]

OK, to reproduce the problem, you need to do the following:
1. Create user: MYADMIN with password "test" and add this user to RDB$ADMIN role
2. Create user STANDARD with password "test"
3. Execute this script:
/******************************************************************************/
/****        Generated by IBExpert 2023.7.20.1 16.09.2025 14:38:24         ****/

/******************************************************************************/

SET SQL DIALECT 3;

SET NAMES WIN1250;

CREATE DATABASE 'localhost/3055:D:\TMP\TEST.FDB'
USER 'SYSDBA'
PASSWORD 'masterkey'
PAGE_SIZE 16384
DEFAULT CHARACTER SET WIN1250 COLLATION WIN1250;



/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/



CREATE TABLE USERS (
    ID    INTEGER NOT NULL,
    NAME  VARCHAR(20)
);



/******************************************************************************/
/****                             Primary keys                             ****/
/******************************************************************************/

ALTER TABLE USERS ADD CONSTRAINT PK_USERS PRIMARY KEY (ID);

/******************************************************************************/
/****                              Privileges                              ****/
/******************************************************************************/


/* Privileges of users */

GRANT ALL ON USERS TO USER STANDARD;


/******************************************************************************/
/****                            DDL privileges                            ****/
/******************************************************************************/
4. Execute this script:
/******************************************************************************/
/****        Generated by IBExpert 2023.7.20.1 16.09.2025 14:42:22         ****/

/******************************************************************************/

SET SQL DIALECT 3;

SET NAMES WIN1250;

CONNECT 'localhost/3055:D:\TMP\TEST.FDB' USER 'MYADMIN' PASSWORD 'test' ROLE 'RDB$ADMIN';

/******************************************************************************/
/****                                Views                                 ****/
/******************************************************************************/

/* View: MYUSERS */
CREATE VIEW MYUSERS(
    ID,
    NAME)
AS
select * from USERS

;




/******************************************************************************/
/****                              Privileges                              ****/
/******************************************************************************/


/* Privileges of users */

GRANT ALL ON MYUSERS TO USER MYADMIN WITH GRANT OPTION;
GRANT ALL ON MYUSERS TO USER STANDARD;


/******************************************************************************/
/****                            DDL privileges                            ****/
/******************************************************************************/
5. Connect to database by user : STANDARD and try to execute this sql: select * from myusers

Ofcourse, you need to change path to db.

Performing these steps on version 2.5 is successful, meaning that STANDARD users can read data from the view. In version 5.0, I get the error described above.

[Vlad Khorsun]

OK, to reproduce the problem, you need to do the following:
1. Create user: MYADMIN with password "test" and add this user to RDB$ADMIN role
2. Create user STANDARD with password "test"

... 

  You still not provided full script, but now it is more clear at least.

  You want weird thing - allow MYADMIN create view but not allow MYADMIN to select from

table that view uses (USERS). To do it, you fool FB25 creating view in the attachment with

RDB$ADMIN role active. When attachment of user STANDARD select's from view it uses

privileges of current user to select from table. 

  But this doesn't work in FB5. It have no separate SQL SECURITY clause for VIEW and looks like

it uses DEFINER user to check access rights (while FB2.5 uses both DEFINER and INVOKER

rights combined, IIRC). But user MYADMIN have no rights to select from USERS and its role 

RDB$ADMIN is not in effect here. You might make  RDB$ADMIN as default role for MYADMIN 

and this will work, but I'm not sure if it is what you originally wanted.

  For me is not fully clear should Firebird use DEFINER and/or INVOKER priveleges when checking 

access for query with view object.  If you consider it as a bug in FB5 - create a ticket or ask in 

fb-devel, please.So far, I don't think its a bug, btw.

  In any case, I consider your original intention as very weird and not sure it is legal at all.

Regards,

Vlad