権限チェック - HTN20190109の日記

MySQL

調べた限りなし

Oracle

(19c)
権限分析

-- 権限分析ポリシーの作成

select * from DBA_PRIV_CAPTURES;

---- データベース分析

BEGIN
DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
name => 'db_wide_capture_pol',
description => 'Captures database-wide privileges',
type => DBMS_PRIVILEGE_CAPTURE.G_DATABASE);
END;
/

---- ロール分析
BEGIN
DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
name => 'dba_roles_capture_pol',
description => 'Captures DBA and LBAC_DBA role use',
type => DBMS_PRIVILEGE_CAPTURE.G_ROLE,
roles => role_name_list('dba', 'aq_user_role'));
END;
/

---- コンテキスト分析
BEGIN
DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
name => 'sqlplus_capture_pol',
description => 'Captures privilege use during SQL*Plus use',
type => DBMS_PRIVILEGE_CAPTURE.G_CONTEXT,
condition => 'SYS_CONTEXT(''USERENV'', ''MODULE'')=''sqlplus''');
END;
/

-- 権限分析ポリシーの有効化

select * from DBA_PRIV_CAPTURES;

EXEC DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE ('db_wide_capture_pol');

-- 権限分析レポートの作成

EXEC DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE ('db_wide_capture_pol');
EXEC DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT ('db_wide_capture_pol');

select count(*) from DBA_USED_PRIVS;
select count(*) from DBA_UNUSED_PRIVS;

-- 権限分析ポリシーの削除
select * from DBA_PRIV_CAPTURES;

EXEC DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE ('sqlplus_capture_pol');

PostgreSQL

(13)
https://qiita.com/U_ikki/items/c7714f8cd19ab2012dd1
https://www.cybertec-postgresql.com/en/pg_permission-inspecting-your-postgresql-security-system/

-- インストール
git clone https://github.com/cybertec-postgresql/pg_permission.git

cd pg_permission
export PATH=$PATH:/usr/pgsql-13/bin
make install

CREATE EXTENSION pg_permissions ;

-- VIEWの確認
select * from all_permissions;
select * from database_permissions;
select * from schema_permissions;
select * from table_permissions;
select * from column_permissions;
select * from sequence_permissions;
select * from view_permissions;
select * from function_permissions;

-- 権限チェック

-- 検証例
user30を使用し、スキーマレベルとテーブルレベルで足りない権限と余計な権限を付与

-- あるべき権限
スキーマレベル： schema1へのusage権限,schema1へのcreate権限
テーブルレベル: schema1.tab1へのupdate権限,schema1.tab1へのselect権限

-- 実際の権限
スキーマレベル: schema1へのusage権限,schema2へのusage権限
テーブルレベル: schema1.tab1へのupdate権限,schema1.tab1へのdelete権限

-- 検証用ユーザ作成

drop owned by user30 cascade;
drop user user30;

drop schema schema1 cascade;
drop schema schema2 cascade;

create schema schema1;
create schema schema2;

create table schema1.tab1(col1 int);

create user user30 with login encrypted password 'user30';
grant usage on schema schema1 to user30;
grant usage on schema schema2 to user30;
grant update,delete on schema1.tab1 to user30;

-- あるべき権限をテーブルに登録
select * from permission_target;

truncate table permission_target;

insert into permission_target(role_name,permissions,object_type,schema_name)
values('user30','{USAGE,CREATE}','SCHEMA','schema1');

insert into permission_target(role_name,permissions,object_type,schema_name,object_name)
values('user30','{SELECT,UPDATE}','TABLE','schema1','tab1');

select * from permission_target;

select * from permission_diffs()
where role_name = 'user30'
;

SQL Server

調べた限りなし