Ejemplo completo de enmascaramiento de datos dinámico
A continuación, se presenta un ejemplo completo en el que se muestra cómo puede crear y adjuntar políticas de enmascaramiento a una columna. Estas políticas permiten a los usuarios acceder a una columna y ver diferentes valores, según el grado de ofuscación de las políticas asociadas a sus roles. Debe ser superusuario o tener el rol sys:secadmin
para ejecutar este ejemplo.
Creación de una política de enmascaramiento
En primer lugar, cree una tabla y rellénela con los valores de tarjeta de crédito.
--create the table
CREATE TABLE credit_cards (
customer_id INT,
credit_card TEXT
);
--populate the table with sample values
INSERT INTO credit_cards
VALUES
(100, '4532993817514842'),
(100, '4716002041425888'),
(102, '5243112427642649'),
(102, '6011720771834675'),
(102, '6011378662059710'),
(103, '373611968625635')
;
--run GRANT to grant permission to use the SELECT statement on the table
GRANT SELECT ON credit_cards TO PUBLIC;
--create two users
CREATE USER regular_user WITH PASSWORD '1234Test!';
CREATE USER analytics_user WITH PASSWORD '1234Test!';
--create the analytics_role role and grant it to analytics_user
--regular_user does not have a role
CREATE ROLE analytics_role;
GRANT ROLE analytics_role TO analytics_user;
A continuación, cree una política de enmascaramiento para aplicarla al rol de análisis.
--create a masking policy that fully masks the credit card number
CREATE MASKING POLICY mask_credit_card_full
WITH (credit_card VARCHAR(256))
USING ('000000XXXX0000'::TEXT);
--create a user-defined function that partially obfuscates credit card data
CREATE FUNCTION REDACT_CREDIT_CARD (credit_card TEXT)
RETURNS TEXT IMMUTABLE
AS $$
import re
regexp = re.compile("^([0-9]{6})[0-9]{5,6}([0-9]{4})")
match = regexp.search(credit_card)
if match != None:
first = match.group(1)
last = match.group(2)
else:
first = "000000"
last = "0000"
return "{}XXXXX{}".format(first, last)
$$ LANGUAGE plpythonu;
--create a masking policy that applies the REDACT_CREDIT_CARD function
CREATE MASKING POLICY mask_credit_card_partial
WITH (credit_card VARCHAR(256))
USING (REDACT_CREDIT_CARD(credit_card));
--confirm the masking policies using the associated system views
SELECT * FROM svv_masking_policy;
SELECT * FROM svv_attached_masking_policy;
Asociación de una política de enmascaramiento
Adjunte las políticas de enmascaramiento a la tabla de tarjetas de crédito.
--attach mask_credit_card_full to the credit card table as the default policy
--all users will see this masking policy unless a higher priority masking policy is attached to them or their role
ATTACH MASKING POLICY mask_credit_card_full
ON credit_cards(credit_card)
TO PUBLIC;
--attach mask_credit_card_partial to the analytics role
--users with the analytics role can see partial credit card information
ATTACH MASKING POLICY mask_credit_card_partial
ON credit_cards(credit_card)
TO ROLE analytics_role
PRIORITY 10;
--confirm the masking policies are applied to the table and role in the associated system view
SELECT * FROM svv_attached_masking_policy;
--confirm the full masking policy is in place for normal users by selecting from the credit card table as regular_user
SET SESSION AUTHORIZATION regular_user;
SELECT * FROM credit_cards;
--confirm the partial masking policy is in place for users with the analytics role by selecting from the credit card table as analytics_user
SET SESSION AUTHORIZATION analytics_user;
SELECT * FROM credit_cards;
Creación de una política de enmascaramiento
En la siguiente sección, se muestra cómo modificar una política de enmascaramiento de datos dinámico.
--reset session authorization to the default
RESET SESSION AUTHORIZATION;
--alter the mask_credit_card_full policy
ALTER MASKING POLICY mask_credit_card_full
USING ('00000000000000'::TEXT);
--confirm the full masking policy is in place after altering the policy, and that results are altered from '000000XXXX0000' to '00000000000000'
SELECT * FROM credit_cards;
Desconexión y eliminación de una política de enmascaramiento
En la siguiente sección se muestra cómo desconectar y eliminar las políticas de enmascaramiento mediante la eliminación de todas las políticas de enmascaramiento de datos dinámicos de la tabla.
--reset session authorization to the default
RESET SESSION AUTHORIZATION;
--detach both masking policies from the credit_cards table
DETACH MASKING POLICY mask_credit_card_full
ON credit_cards(credit_card)
FROM PUBLIC;
DETACH MASKING POLICY mask_credit_card_partial
ON credit_cards(credit_card)
FROM ROLE analytics_role;
--drop both masking policies
DROP MASKING POLICY mask_credit_card_full;
DROP MASKING POLICY mask_credit_card_partial;