updateでのselect権限必要性

• MySQL

(8.0.21)

-- テストテーブル作成
drop table tab1;
create table tab1(col1 int);
alter table tab1 add constraint tab1pk primary key(col1);

insert into tab1 values(1);
select * from tab1;

-- テストユーザ作成
drop user 'user1'@'%';
create user 'user1'@'%' identified by 'user1';

-- 動作確認
revoke select on test.tab1 from user1;
revoke update on test.tab1 from user1;
revoke delete on test.tab1 from user1;

grant select on test.tab1 to user1;
grant update on test.tab1 to user1;
grant delete on test.tab1 to user1;

show grants for 'user1'@'%';

start transaction;

select * from test.tab1;
update test.tab1 set col1 = 1;
update test.tab1 set col1 = 1 where col1 = 1;
delete from test.tab1;
delete from test.tab1 where col1 = 1;

rollback;

→ updateやdeleteの実行でwhere句がある場合はselect権限も必要

• Oracle

(12cR1)

-- テストテーブル作成
drop table tab1 purge;
create table tab1(col1 int);
alter table tab1 add constraint tab1pk primary key(col1);

insert into tab1 values(1);
commit;
select * from tab1;

-- テストユーザ作成
drop user user1 cascade;
create user user1 identified by user1;

grant create session to user1;

-- 動作確認
revoke select on tab1 from user1;
revoke update on tab1 from user1;
revoke delete on tab1 from user1;

grant select on tab1 to user1;
grant update on tab1 to user1;
grant delete on tab1 to user1;

select * from dba_tab_privs where TABLE_NAME = 'TAB1';

select * from test.tab1;
update test.tab1 set col1 = 1;
update test.tab1 set col1 = 1 where col1 = 1;
delete from test.tab1;
delete from test.tab1 where col1 = 1;

rollback;

→ updateやdeleteの実行にselect権限は不要

SQL92_SECURITYで制御される
(12cR1)のデフォルトはfalseのため、select権限は不要
(19c)のデフォルトはtrueのため、select権限は必要

• PostgreSQL

(11)

-- テストテーブル作成
drop table tab1;
create table tab1(col1 int);
alter table tab1 add constraint tab1pk primary key(col1);

insert into tab1 values(1);
select * from tab1;

-- テストユーザ作成
drop owned by user1 cascade;
drop user user1;
create user user1 with login encrypted password 'user1';

-- 動作確認

revoke select on tab1 from user1;
revoke update on tab1 from user1;
revoke delete on tab1 from user1;

grant select on tab1 to user1;
grant update on tab1 to user1;
grant delete on tab1 to user1;

\z tab1

start transaction;

select * from tab1;
update tab1 set col1 = 1;
update tab1 set col1 = 1 where col1 = 1;
delete from tab1;
delete from tab1 where col1 = 1;

rollback;

→ updateやdeleteの実行でwhere句がある場合はselect権限も必要

• SQL Server

(2019)

-- テストテーブル作成
use test
go
drop table tab1;
create table tab1(col1 int not null);
alter table tab1 add constraint tab1pk primary key(col1);

insert into tab1 values(1);
select * from tab1;

-- テストユーザ作成
use test
go

drop user user1;
go

use master
go

drop login user1;
go

create login user1 with password='user1', default_database=test, check_policy=off
go

use test
go

create user user1 for login user1;

-- 動作確認

revoke select on tab1 from user1;
revoke update on tab1 from user1;
revoke delete on tab1 from user1;

grant select on tab1 to user1;
grant update on tab1 to user1;
grant delete on tab1 to user1;

select * from sys.database_principals;
select * from sys.database_permissions;

begin transaction;

select * from tab1;
update tab1 set col1 = 1;
update tab1 set col1 = 1 where col1 = 1;
delete from tab1;
delete from tab1 where col1 = 1;

rollback;

→ updateやdeleteの実行でwhere句がある場合はselect権限も必要