Error "no permission for DELETE access to TABLE PLG$SRP_VIEW" when <admin-1>
tries to drop <admin-2> via ES and <admin-2> already successfully dropped some
3rd (non privileged) user in the same execute block
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Key: CORE-4770
URL: http://tracker.firebirdsql.org/browse/CORE-4770
Project: Firebird Core
Issue Type: Bug
Reporter: Pavel Zotov
Consider following script:
===
set list on;
show version;
select mon$user,mon$remote_protocol,mon$auth_method from mon$attachments
where mon$attachment_id=current_connection;
commit;
--set list off;
--set echo on;
create or alter view v_users as
select
u.sec$user_name usr_name
,sec$plugin sec_plugin
,u.sec$admin is_sec_admin
,rp.rdb$relation_name granted_role
,rp.rdb$privilege granted_privilege
from rdb$database
left join sec$users u on u.sec$user_name in ( upper('boss'), upper('acnt'),
upper('mgr1'), upper('mgr2'))
left join rdb$user_privileges rp on
u.sec$user_name = rp.rdb$user
and rp.rdb$relation_name = upper('rdb$admin')
and rp.rdb$object_type = 13
and rp.rdb$user_type = 8
;
commit;
set width usr_name 10;
set width sec_plugin 10;
set width granted_role 12;
drop user boss;
drop user acnt;
drop user mgr1;
drop user mgr2;
commit;
create user boss password '123' grant admin role
;
grant rdb$admin to boss;
commit;
create user acnt password '456' grant admin role
;
grant rdb$admin to acnt;
commit;
create user mgr1 password '789';
create user mgr2 password '890';
commit;
set count on;
select * from v_users;
set count off;
------------------ DROP ALL TOGETHER IN ONE EXECUTE BLOCK --------------
set term ^;
execute block as
begin
execute statement 'drop user mgr1'
----------------------- [ 1 ]
as user 'ACNT' password '456' role 'RDB$ADMIN'
;
execute statement 'drop user mgr2'
----------------------- [ 2 ]
as user 'BOSS' password '123' role 'RDB$ADMIN'
;
execute statement 'drop user acnt'
----------------------- [ 3 ]
--as user 'BOSS' password '123' role 'RDB$ADMIN' -- <<<<< :::::::
NB :::::::: <<<<<
;
end
^
set term ;^
commit;
drop user boss; --- [ 4 ]
commit;
set count on;
select * from v_users;
set count off;
===
When the statement marked as '[ 3 ]' is executed from SYSDBA (i.e. as it shown,
with COMMENTED "--as user 'BOSS' . . .") then script works fine.
However, if line "--as user 'BOSS' . . ." in the statement [ 3 ] will be
UNcommented:
execute statement 'drop user acnt'
----------------------- [ 3 ]
as user 'BOSS' password '123' role 'RDB$ADMIN'
;
-- then strange things appear:
1) execute block fails with message:
Statement failed, SQLSTATE = 28000
delete record error
-no permission for DELETE access to TABLE PLG$SRP_VIEW
2) statement "drop user boss; --- [ 4 ]" (that is OUTSIDE execute block and
should be issued by SYSDBA!) also FAILS with the same error;
3) statement "select * from v_users;" also FAILS with this error.
Union of STDOUT and STDERR in this case:
=======
ISQL Version: WI-T3.0.0.31807 Firebird 3.0 Beta 2
Server version:
Firebird/Windows/Intel/i386 (access method), version "WI-T3.0.0.31807 Firebird
3.0 Beta 2"
Firebird/Windows/Intel/i386 (remote server), version "WI-T3.0.0.31807 Firebird
3.0 Beta 2/tcp (balaha)/P13:C"
Firebird/Windows/Intel/i386 (remote interface), version "WI-T3.0.0.31807
Firebird 3.0 Beta 2/tcp (balaha)/P13:C"
on disk structure version 12.0
MON$USER SYSDBA
MON$REMOTE_PROTOCOL TCPv4
MON$AUTH_METHOD Srp
Statement failed, SQLSTATE = HY000
record not found for user: BOSS
After line 29 in file es-drop-user.sql
Statement failed, SQLSTATE = HY000
record not found for user: ACNT
After line 32 in file es-drop-user.sql
Statement failed, SQLSTATE = HY000
record not found for user: MGR1
After line 33 in file es-drop-user.sql
Statement failed, SQLSTATE = HY000
record not found for user: MGR2
After line 34 in file es-drop-user.sql
USR_NAME BOSS
SEC_PLUGIN Srp
IS_SEC_ADMIN <true>
GRANTED_ROLE RDB$ADMIN
GRANTED_PRIVILEGE M
USR_NAME ACNT
SEC_PLUGIN Srp
IS_SEC_ADMIN <true>
GRANTED_ROLE RDB$ADMIN
GRANTED_PRIVILEGE M
USR_NAME MGR1
SEC_PLUGIN Srp
IS_SEC_ADMIN <false>
GRANTED_ROLE <null>
GRANTED_PRIVILEGE <null>
USR_NAME MGR2
SEC_PLUGIN Srp
IS_SEC_ADMIN <false>
GRANTED_ROLE <null>
GRANTED_PRIVILEGE <null>
Records affected: 4
Statement failed, SQLSTATE = 28000
delete record error
-no permission for DELETE access to TABLE PLG$SRP_VIEW
After line 74 in file es-drop-user.sql
Statement failed, SQLSTATE = 28000
delete record error
-no permission for DELETE access to TABLE PLG$SRP_VIEW
After line 77 in file es-drop-user.sql
USR_NAME BOSS
SEC_PLUGIN Srp
IS_SEC_ADMIN <true>
GRANTED_ROLE RDB$ADMIN
GRANTED_PRIVILEGE M
USR_NAME ACNT
SEC_PLUGIN Srp
IS_SEC_ADMIN <true>
GRANTED_ROLE RDB$ADMIN
GRANTED_PRIVILEGE M
USR_NAME MGR1
SEC_PLUGIN Srp
IS_SEC_ADMIN <false>
GRANTED_ROLE <null>
GRANTED_PRIVILEGE <null>
USR_NAME MGR2
SEC_PLUGIN Srp
IS_SEC_ADMIN <false>
GRANTED_ROLE <null>
GRANTED_PRIVILEGE <null>
Records affected: 4
Statement failed, SQLSTATE = 28000
delete record error
-no permission for DELETE access to TABLE PLG$SRP_VIEW
After line 82 in file es-drop-user.sql
=======
PS. If statement "[ 1 ]" will be commented then EB works fine again - i.e.
this error is somehow related to the fact that 'ACNT' could successfuly do at
least one action against users list (he drops non-dba user mgr1).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
BPM Camp - Free Virtual Workshop May 6th at 10am PDT/1PM EDT
Develop your own process in accordance with the BPMN 2 standard
Learn Process modeling best practices with Bonita BPM through live exercises
http://www.bonitasoft.com/be-part-of-it/events/bpm-camp-virtual- event?utm_
source=Sourceforge_BPM_Camp_5_6_15&utm_medium=email&utm_campaign=VA_SF
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
