BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'test',
object_name => 'tab1',
column_name => 'col1',
policy_name => 'pol1',
function_type => DBMS_REDACT.FULL,
expression => '1=1');
END;
/
コミュニティ版にはない模様
エンタープライズ版の場合、
MySQL Enterprise Masking and De-identification
(14)
https://qiita.com/tom-sato/items/0adbee0b79382945712e
-- PostgreSQL Anonymizer のインストール
dnf -y install https://download.postgresql.org/pub/repos/yum/14/redhat/rhel-8-x86_64/postgresql_anonymizer_14-1.0.0-1.rhel8.x86_64.rpm
vim postgresql.conf
shared_preload_libraries = 'anon'
sudo systemctl restart postgresql-14
psql test
\dx
CREATE EXTENSION anon CASCADE;
\dx
SELECT anon.init();
-- マスキングルールの定義
CREATE TABLE departments (
id serial PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE employees (
id serial PRIMARY KEY,
last_name text NOT NULL,
first_name text NOT NULL,
email text NOT NULL,
salary int NOT NULL,
department_id int NOT NULL REFERENCES departments (id),
birth date NOT NULL
);
INSERT INTO departments (name) VALUES
('営業部'),
('技術部');
INSERT INTO employees (last_name, first_name, email, salary, department_id, birth) VALUES
('佐藤', '太郎', 'sato@example.com', 300000, 1, '1987-01-02'),
('鈴木', '次郎', 'suzuki@example.com', 300000, 2, '1988-03-04'),
('高橋', '三郎', 'takahashi@example.com', 350000, 1, '1989-05-06'),
('田中', '花子', 'tanaka@example.com', 350000, 2, '1990-07-08'),
('伊藤', '雪子', 'ito@example.com', 400000, 1, '1991-09-10'),
('渡辺', '月子', 'watanabe@example.com', 400000, 2, '1992-11-12');
SELECT * FROM departments;
SELECT * FROM employees;
SECURITY LABEL FOR anon ON COLUMN employees.email IS 'MASKED WITH FUNCTION anon.partial_email(email)';
SELECT * FROM pg_seclabels;
-- インプレース匿名化
begin transaction;
SELECT anon.anonymize_database();
SELECT * FROM employees;
rollback;
-- 匿名ダンプ
sudo chown postgres:postgres /usr/pgsql-14/bin/pg_dump_anon.sh
sudo chmod u+x /usr/pgsql-14/bin/pg_dump_anon.sh
ll /usr/pgsql-14/bin/pg_dump_anon.sh
/usr/pgsql-14/bin/pg_dump_anon.sh -U postgres test > bkup01.sql
(2016)から
create table tab18(
col1 int not null,
col2 varchar(50) not null,
col3 int not null
);
insert into tab18 values(1,'ASAHI SAITOU',123000);
insert into tab18 values(2,'DANJUROU KURAMAE',999343);
select * from tab18;
alter table tab18 alter column col2 add masked with (function = 'partial(1,"???",1)')
alter table tab18 alter column col3 add masked with (function = 'random(5000,100000)')
alter table tab18 add col4 varchar(100) masked with (function = 'email()')
insert into tab18 values(3,'MOMOKO KIKUIKE',123,'momoko@example.com');
alter table tab18 alter column col2 drop masked;
grant unmask to [test\ichiro]
revoke unmask from [test\ichiro]
