(8.0.22)
drop user 'user60'@'%';
create user 'user60'@'%' identified by 'user60';
drop role role1;
create role role1;
grant select on test.tab1 to role1;
grant create routine on test.* to role1;
grant alter routine on test.* to role1;
grant role1 to 'user60'@'%';
set default role role1 to 'user60'@'%';
mysql -u user60 -h mmm066 -puser60 test
select current_role();
drop procedure proc1;
delimiter //
create procedure proc1()
sql security definer
begin
declare vdone int;
declare vcol1 int;
declare vschema varchar(64);
declare vcnt int;
declare cur1 cursor for select col1 from test.tab1 order by col1;
declare cur2 cursor for select table_schema,count(*) cnt from information_schema.tables group by table_schema order by table_schema;
declare continue handler for sqlstate '02000' set vdone = 1;
set vdone = 0;
open cur1;
fetch cur1 into vcol1;
while vdone != 1 do
select vcol1;
fetch cur1 into vcol1;
end while;
close cur1;
set vdone = 0;
open cur2;
fetch cur2 into vschema,vcnt;
while vdone != 1 do
select vschema,vcnt;
fetch cur2 into vschema,vcnt;
end while;
close cur2;
end//
delimiter ;
call proc1();
drop procedure proc1;
delimiter //
create procedure proc1()
sql security invoker
begin
declare vdone int;
declare vcol1 int;
declare vschema varchar(64);
declare vcnt int;
declare cur1 cursor for select col1 from test.tab1 order by col1;
declare cur2 cursor for select table_schema,count(*) cnt from information_schema.tables group by table_schema order by table_schema;
declare continue handler for sqlstate '02000' set vdone = 1;
set vdone = 0;
open cur1;
fetch cur1 into vcol1;
while vdone != 1 do
select vcol1;
fetch cur1 into vcol1;
end while;
close cur1;
set vdone = 0;
open cur2;
fetch cur2 into vschema,vcnt;
while vdone != 1 do
select vschema,vcnt;
fetch cur2 into vschema,vcnt;
end while;
close cur2;
end//
delimiter ;
call proc1();
→Oracleのような癖はない
(19c)
https://docs.oracle.com/cd/F19136_01/dbseg/configuring-privilege-and-role-authorization.html#GUID-5C57B842-AF82-4462-88E9-5E9E8FD59874
https://qiita.com/tlokweng/items/e501ff3c73f3a5a67a23
定義者権限で実行される名前付きPL/SQLブロックでは、すべてのロールは使用禁止になっています。
実行者権限を使用して実行する名前付きPL/SQLブロックと無名PL/SQLブロックは、使用可能なロールを通じて付与された権限に基づいて実行されます。
drop user user60 cascade;
create user user60 identified by user60;
grant dba to user60 container=current;
sqlplus user60/user60@pdb1
set serveroutput on
create or replace procedure proc1
AUTHID DEFINER
as
cursor cur1 is select col1 from test.tab1 order by col1;
cursor cur2 is select owner,count(*) cnt from all_tables group by owner order by owner;
cursor cur3 is select owner,count(*) cnt from dba_tables group by owner order by owner;
begin
for c1 in cur1 loop
dbms_output.put_line('col1=' || c1.col1);
end loop;
for c2 in cur2 loop
dbms_output.put_line('owner,count(*)=' || c2.owner||','||c2.cnt);
end loop;
dbms_output.put_line('===========');
for c3 in cur3 loop
dbms_output.put_line('owner,count(*)=' || c3.owner||','||c3.cnt);
end loop;
end;
/
sho error
exec proc1;
set serveroutput on
create or replace procedure proc1
AUTHID CURRENT_USER
as
cursor cur1 is select col1 from test.tab1 order by col1;
cursor cur2 is select owner,count(*) cnt from all_tables group by owner order by owner;
cursor cur3 is select owner,count(*) cnt from dba_tables group by owner order by owner;
begin
for c1 in cur1 loop
dbms_output.put_line('col1=' || c1.col1);
end loop;
for c2 in cur2 loop
dbms_output.put_line('owner,count(*)=' || c2.owner||','||c2.cnt);
end loop;
dbms_output.put_line('===========');
for c3 in cur3 loop
dbms_output.put_line('owner,count(*)=' || c3.owner||','||c3.cnt);
end loop;
end;
/
sho error
exec proc1;
set serveroutput on
declare
cursor cur1 is select col1 from test.tab1 order by col1;
cursor cur2 is select owner,count(*) cnt from all_tables group by owner order by owner;
cursor cur3 is select owner,count(*) cnt from dba_tables group by owner order by owner;
begin
for c1 in cur1 loop
dbms_output.put_line('col1=' || c1.col1);
end loop;
for c2 in cur2 loop
dbms_output.put_line('owner,count(*)=' || c2.owner||','||c2.cnt);
end loop;
dbms_output.put_line('===========');
for c3 in cur3 loop
dbms_output.put_line('owner,count(*)=' || c3.owner||','||c3.cnt);
end loop;
end;
/
-- 定義者権限プロシージャの場合
プロシージャコンパイルのために下記権限の直接付与が必要
grant select any dictionary to user60 container=current;
grant select on test.tab1 to user60 container=current;
-- 実行者権限プロシージャの場合
プロシージャコンパイルのために下記権限の直接付与が必要
grant select any dictionary to user60 container=current;
grant select on test.tab1 to user60 container=current;
※マニュアルの記載と異なる
-- 無名ブロックの場合
権限追加は不要
(13)
drop owned by user60 cascade;
drop user user60;
create user user60 encrypted password 'user60';
drop role role1;
create role role1;
grant connect on database test to role1;
grant usage on schema schema1 to role1;
grant select on schema1.tab1 to role1;
grant role1 to user60;
PGPASSWORD=user60 psql -U user60 -h mmm067 -d test
create or replace procedure public.proc1()
security definer
language plpgsql
as $$
declare
cur1 cursor for select col1 from schema1.tab1 order by col1;
cur2 cursor for select schemaname,count(*) cnt from pg_stat_all_tables group by schemaname order by schemaname;
cur3 cursor for select schemaname,count(*) cnt from pg_stat_user_tables group by schemaname order by schemaname;
begin
for c1 in cur1 loop
raise notice 'col1=%', c1.col1;
end loop;
for c2 in cur2 loop
raise notice 'owner,count(*)=%,%', c2.schemaname,c2.cnt;
end loop;
raise notice '===========';
for c3 in cur3 loop
raise notice 'owner,count(*)=%,%', c3.schemaname,c3.cnt;
end loop;
end;
$$
;
call proc1();
create or replace procedure public.proc1()
security invoker
language plpgsql
as $$
declare
cur1 cursor for select col1 from schema1.tab1 order by col1;
cur2 cursor for select schemaname,count(*) cnt from pg_stat_all_tables group by schemaname order by schemaname;
cur3 cursor for select schemaname,count(*) cnt from pg_stat_user_tables group by schemaname order by schemaname;
begin
for c1 in cur1 loop
raise notice 'col1=%', c1.col1;
end loop;
for c2 in cur2 loop
raise notice 'owner,count(*)=%,%', c2.schemaname,c2.cnt;
end loop;
raise notice '===========';
for c3 in cur3 loop
raise notice 'owner,count(*)=%,%', c3.schemaname,c3.cnt;
end loop;
end;
$$
;
call proc1();
do
language plpgsql
$$
declare
cur1 cursor for select col1 from schema1.tab1 order by col1;
cur2 cursor for select schemaname,count(*) cnt from pg_stat_all_tables group by schemaname order by schemaname;
cur3 cursor for select schemaname,count(*) cnt from pg_stat_user_tables group by schemaname order by schemaname;
begin
for c1 in cur1 loop
raise notice 'col1=%', c1.col1;
end loop;
for c2 in cur2 loop
raise notice 'owner,count(*)=%,%', c2.schemaname,c2.cnt;
end loop;
raise notice '===========';
for c3 in cur3 loop
raise notice 'owner,count(*)=%,%', c3.schemaname,c3.cnt;
end loop;
end;
$$
;
→Oracleのような癖はない
(2019)
use test
go
drop user user60;
use master
go
drop login user60;
create login user60 with password='user60', default_database=test, check_policy=off
use test
go
create user user60 for login user60;
use master
go
alter server role sysadmin add member user60;
use test
go
create or alter procedure proc1
with execute as self
as
begin
set nocount on;
declare @col1 int;
declare @schema_name varchar(100);
declare @cnt int;
declare cur1 insensitive cursor for select col1 from tab1 order by col1;
open cur1;
fetch next from cur1 into @col1;
while (@@fetch_status = 0)
begin
print '@col1='+cast(@col1 as varchar);
fetch next from cur1 into @col1;
end
close cur1;
deallocate cur1;
declare cur2 insensitive cursor for select schema_name(schema_id) schema_name,count(*) cnt
from sys.tables group by schema_name(schema_id) order by schema_name(schema_id);
open cur2;
fetch next from cur2 into @schema_name,@cnt;
while (@@fetch_status = 0)
begin
print '@schema_name='+@schema_name+',@cnt='+cast(@cnt as varchar);
fetch next from cur2 into @schema_name,@cnt;
end
close cur2;
deallocate cur2;
end
go
exec dbo.proc1;
create or alter procedure proc1
with execute as caller
as
begin
set nocount on;
declare @col1 int;
declare @schema_name varchar(100);
declare @cnt int;
declare cur1 insensitive cursor for select col1 from tab1 order by col1;
open cur1;
fetch next from cur1 into @col1;
while (@@fetch_status = 0)
begin
print '@col1='+cast(@col1 as varchar);
fetch next from cur1 into @col1;
end
close cur1;
deallocate cur1;
declare cur2 insensitive cursor for select schema_name(schema_id) schema_name,count(*) cnt
from sys.tables group by schema_name(schema_id) order by schema_name(schema_id);
open cur2;
fetch next from cur2 into @schema_name,@cnt;
while (@@fetch_status = 0)
begin
print '@schema_name='+@schema_name+',@cnt='+cast(@cnt as varchar);
fetch next from cur2 into @schema_name,@cnt;
end
close cur2;
deallocate cur2;
end
go
exec dbo.proc1;
set nocount on;
declare @col1 int;
declare @schema_name varchar(100);
declare @cnt int;
declare cur1 insensitive cursor for select col1 from tab1 order by col1;
open cur1;
fetch next from cur1 into @col1;
while (@@fetch_status = 0)
begin
print '@col1='+cast(@col1 as varchar);
fetch next from cur1 into @col1;
end
close cur1;
deallocate cur1;
declare cur2 insensitive cursor for select schema_name(schema_id) schema_name,count(*) cnt
from sys.tables group by schema_name(schema_id) order by schema_name(schema_id);
open cur2;
fetch next from cur2 into @schema_name,@cnt;
while (@@fetch_status = 0)
begin
print '@schema_name='+@schema_name+',@cnt='+cast(@cnt as varchar);
fetch next from cur2 into @schema_name,@cnt;
end
close cur2;
deallocate cur2;
→Oracleのような癖はない
