You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
New objects and some old object of database cannot be granted via role privileges. Suggestion is the same as for check of existing objects: move checks to code like it's already done for generators and exceptions.
To test a problem you can use the script that create users, roles, objects and step-by-step grants a privilege on every possible object 1) with grant option, 2) without grant option, 3) from role having it without grant option to prevent delegating:
set echo on;
create database 'localhost:/tmp/23976.fdb';
create or alter user user1 password 'pass';
create or alter user user2 password 'pass';
create or alter user user3 password 'pass';
create role role1; -- Has privileges with grant option
create role role2; -- Has privileges without errors and without grant option
create role role3; -- Must get errors in granting privileges from role2
grant role1 to user1;
grant role2 to user2;
grant role3 to user3;
create procedure p as begin end;
create function f returns int as begin end;
create generator g;
create exception e 'ex';
create table tab(id int);
create package pak as begin end;
grant create table to role1 with grant option;
grant create procedure to role1 with grant option;
grant execute on procedure p to role1 with grant option;
grant execute on function f to role1 with grant option;
grant usage on generator g to role1 with grant option;
grant usage on exception e to role1 with grant option;
grant select on tab to role1 with grant option;
grant update(id) on tab to role1 with grant option;
grant execute on package pak to role1 with grant option;
commit;
connect 'localhost:/tmp/23976.fdb' user 'user1' password 'pass' role 'role1';
select rdb$role_name from rdb$roles where rdb$role_in_use(rdb$role_name);
grant create table to role2;
grant execute on procedure p to role2;
grant execute on function f to role2;
grant usage on generator g to role2;
grant usage on exception e to role2;
grant select on tab to role2;
grant update(id) on tab to role2;
grant execute on package pak to role2;
commit;
-- create own objects
create table tab_of_user1(i int);
create procedure proc_of_user1 as begin end;
commit;
-- try to grant privileges for owned objects
grant select on table tab_of_user1 to role2;
grant execute on procedure proc_of_user1 to role2;
commit;
connect 'localhost:/tmp/23976.fdb' user 'user2' password 'pass' role 'role2';
-- check every privilege
create table t(i integer);
execute procedure p;
select f() from rdb$database;
select gen_id(g, 1) from rdb$database;
select * from tab;
-- try to grant every privilege to role3 and sure this causes an error
------------------------------------------------
grant create table to role3;
------------------------------------------------
grant execute on procedure p to role3;
------------------------------------------------
grant execute on function f to role3;
------------------------------------------------
grant usage on generator g to role3;
------------------------------------------------
grant usage on exception e to role3;
------------------------------------------------
grant select on tab to role3;
------------------------------------------------
grant update(id) on tab to role3;
------------------------------------------------
grant execute on package pak to role3;
------------------------------------------------
Note a couple of check for granting privileges on owned object.
Submitted by: @romansimakov
New objects and some old object of database cannot be granted via role privileges. Suggestion is the same as for check of existing objects: move checks to code like it's already done for generators and exceptions.
To test a problem you can use the script that create users, roles, objects and step-by-step grants a privilege on every possible object 1) with grant option, 2) without grant option, 3) from role having it without grant option to prevent delegating:
set echo on;
create database 'localhost:/tmp/23976.fdb';
create or alter user user1 password 'pass';
create or alter user user2 password 'pass';
create or alter user user3 password 'pass';
create role role1; -- Has privileges with grant option
create role role2; -- Has privileges without errors and without grant option
create role role3; -- Must get errors in granting privileges from role2
grant role1 to user1;
grant role2 to user2;
grant role3 to user3;
create procedure p as begin end;
create function f returns int as begin end;
create generator g;
create exception e 'ex';
create table tab(id int);
create package pak as begin end;
grant create table to role1 with grant option;
grant create procedure to role1 with grant option;
grant execute on procedure p to role1 with grant option;
grant execute on function f to role1 with grant option;
grant usage on generator g to role1 with grant option;
grant usage on exception e to role1 with grant option;
grant select on tab to role1 with grant option;
grant update(id) on tab to role1 with grant option;
grant execute on package pak to role1 with grant option;
commit;
connect 'localhost:/tmp/23976.fdb' user 'user1' password 'pass' role 'role1';
select rdb$role_name from rdb$roles where rdb$role_in_use(rdb$role_name);
grant create table to role2;
grant execute on procedure p to role2;
grant execute on function f to role2;
grant usage on generator g to role2;
grant usage on exception e to role2;
grant select on tab to role2;
grant update(id) on tab to role2;
grant execute on package pak to role2;
commit;
-- create own objects
create table tab_of_user1(i int);
create procedure proc_of_user1 as begin end;
commit;
-- try to grant privileges for owned objects
grant select on table tab_of_user1 to role2;
grant execute on procedure proc_of_user1 to role2;
commit;
connect 'localhost:/tmp/23976.fdb' user 'user2' password 'pass' role 'role2';
-- check every privilege
create table t(i integer);
execute procedure p;
select f() from rdb$database;
select gen_id(g, 1) from rdb$database;
select * from tab;
-- try to grant every privilege to role3 and sure this causes an error
------------------------------------------------
grant create table to role3;
------------------------------------------------
grant execute on procedure p to role3;
------------------------------------------------
grant execute on function f to role3;
------------------------------------------------
grant usage on generator g to role3;
------------------------------------------------
grant usage on exception e to role3;
------------------------------------------------
grant select on tab to role3;
------------------------------------------------
grant update(id) on tab to role3;
------------------------------------------------
grant execute on package pak to role3;
------------------------------------------------
Note a couple of check for granting privileges on owned object.
Commits: 1dfb1d2 7b8d6c2 f436eeb
The text was updated successfully, but these errors were encountered: