Provisionamento de usuários e concessão automatizada de privilégios no Oracle Database
Introdução
Em ambientes corporativos é comum a necessidade de provisionar múltiplos usuários com as mesmas permissões dentro do banco de dados.
Realizar a concessão manual de privilégios em centenas ou milhares de objetos pode ser um processo demorado, sujeito a erros e difícil de auditar.
Neste artigo apresento uma abordagem estruturada para:
• Criar usuários no Oracle Database
• Centralizar privilégios em uma ROLE
• Automatizar a concessão de privilégios em tabelas e objetos PL/SQL
• Executar um dry-run para validação antes de aplicar os GRANTs
• Registrar todas as operações em log
Essa abordagem é especialmente útil em ambientes com grande volume de objetos, onde a manutenção manual se torna inviável.
1. Criando uma ROLE para centralizar os privilégios
A primeira boa prática é evitar conceder privilégios diretamente aos usuários.
Em vez disso, criamos uma ROLE que centraliza os privilégios.
CREATE ROLE app_dml_role;
2. Criação dos usuários no banco de dados
Agora criamos os usuários que terão acesso ao banco.
CREATE USER user_1 IDENTIFIED BY "aDo04j28L"
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS;
CREATE USER user_2 IDENTIFIED BY "8178R2uxW"
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS;
CREATE USER user_3 IDENTIFIED BY "O7F7xI0Y5"
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS;
CREATE USER user_4 IDENTIFIED BY "CRF1190Kh"
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS;
CREATE USER user_5 IDENTIFIED BY "N8cHd9q1A"
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS;
3. Concedendo privilégio de conexão
Todos os usuários precisam do privilégio básico de conexão.
GRANT CREATE SESSION TO user_1;
GRANT CREATE SESSION TO user_2;
GRANT CREATE SESSION TO user_3;
GRANT CREATE SESSION TO user_4;
GRANT CREATE SESSION TO user_5;
4. Associando a ROLE aos usuários
Agora vinculamos os usuários à role criada anteriormente.
GRANT app_dml_role TO user_1;
GRANT app_dml_role TO user_2;
GRANT app_dml_role TO user_3;
GRANT app_dml_role TO user_4;
GRANT app_dml_role TO user_5;
5. Tabela de log das operações
Antes de executar os GRANTs, criamos uma tabela para registrar todas as operações realizadas.
CREATE TABLE sec_grant_log (
log_id NUMBER GENERATED ALWAYS AS IDENTITY,
log_ts TIMESTAMP DEFAULT SYSTIMESTAMP,
role_name VARCHAR2(128),
owner_name VARCHAR2(128),
obj_type VARCHAR2(30),
obj_name VARCHAR2(128),
stmt CLOB,
status VARCHAR2(10),
err_msg VARCHAR2(4000)
);
6. Package para execução automatizada dos GRANTs
Specification
CREATE OR REPLACE PACKAGE sec_grant_mgr AS
PROCEDURE grant_role_for_owners(
p_role IN VARCHAR2,
p_owners IN SYS.ODCIVARCHAR2LIST,
p_dry_run IN BOOLEAN DEFAULT FALSE
);
END sec_grant_mgr;
/
Package Body
CREATE OR REPLACE PACKAGE BODY sec_grant_mgr AS
PROCEDURE log_stmt(
p_role VARCHAR2,
p_owner VARCHAR2,
p_type VARCHAR2,
p_name VARCHAR2,
p_stmt CLOB,
p_status VARCHAR2,
p_err VARCHAR2
) IS
BEGIN
INSERT INTO sec_grant_log(role_name, owner_name, obj_type, obj_name, stmt, status, err_msg)
VALUES (UPPER(p_role), UPPER(p_owner), p_type, p_name, p_stmt, p_status, p_err);
END;
PROCEDURE exec_sql(
p_role VARCHAR2,
p_owner VARCHAR2,
p_type VARCHAR2,
p_name VARCHAR2,
p_sql CLOB,
p_dry_run BOOLEAN
) IS
BEGIN
IF NOT p_dry_run THEN
EXECUTE IMMEDIATE p_sql;
END IF;
log_stmt(p_role, p_owner, p_type, p_name, p_sql, 'OK', NULL);
EXCEPTION
WHEN OTHERS THEN
log_stmt(p_role, p_owner, p_type, p_name, p_sql, 'ERROR', SQLERRM);
END;
PROCEDURE grant_role_for_owners(
p_role IN VARCHAR2,
p_owners IN SYS.ODCIVARCHAR2LIST,
p_dry_run IN BOOLEAN DEFAULT FALSE
) IS
BEGIN
FOR t IN (
SELECT owner, table_name
FROM dba_tables
WHERE owner IN (SELECT UPPER(column_value) FROM TABLE(p_owners))
) LOOP
exec_sql(
p_role, t.owner, 'TABLE', t.table_name,
'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || t.owner || '.' || t.table_name || ' TO ' || p_role,
p_dry_run
);
END LOOP;
FOR o IN (
SELECT owner, object_name, object_type
FROM dba_objects
WHERE owner IN (SELECT UPPER(column_value) FROM TABLE(p_owners))
AND object_type IN ('PROCEDURE','FUNCTION','PACKAGE')
) LOOP
exec_sql(
p_role, o.owner, o.object_type, o.object_name,
'GRANT EXECUTE ON ' || o.owner || '.' || o.object_name || ' TO ' || p_role,
p_dry_run
);
END LOOP;
COMMIT;
END;
END sec_grant_mgr;
/
7. Executando um DRY-RUN
Antes de aplicar os GRANTs efetivamente, podemos simular a execução.
SQL
BEGIN
sec_grant_mgr.grant_role_for_owners(
p_role => 'APP_DML_ROLE',
p_owners => SYS.ODCIVARCHAR2LIST('RM'),
p_dry_run => TRUE
);
END;
/
8. Validando os resultados da simulação
SELECT status, obj_type, COUNT(*) qtd
FROM sec_grant_log
WHERE role_name = 'APP_DML_ROLE'
GROUP BY status, obj_type
ORDER BY status, obj_type;
STATUS OBJ_TYPE QTD
---------- ------------------------------ ----------
OK FUNCTION 45
OK PROCEDURE 53
OK TABLE 8544
SELECT stmt
FROM sec_grant_log
WHERE role_name = 'APP_DML_ROLE'
AND status = 'OK'
ORDER BY log_id;
9. Validando os grants gerados e armazenados na tabela, após simulação.
SELECT stmt
FROM sec_grant_log
WHERE role_name = 'APP_DML_ROLE'
AND status = 'OK'
ORDER BY log_id;
STMT
--------------------------------------------------------------------------------
GRANT EXECUTE ON RM.VERIFICAGLOSAGG TO APP_DML_ROLE
GRANT EXECUTE ON RM.SP_AJUSTACLIENTEDUPLICADO TO APP_DML_ROLE
GRANT EXECUTE ON RM.P_AUDITLOG TO APP_DML_ROLE
GRANT EXECUTE ON RM.P_AUDITGETDIF TO APP_DML_ROLE
GRANT EXECUTE ON RM.SAUGETGRUPOGASTOTISS TO APP_DML_ROLE
9. Limpando o log antes da execução real, para garantir que não haja duplicidade.
TRUNCATE TABLE sec_grant_log;
10. Executando os GRANTs efetivamente
BEGIN
sec_grant_mgr.grant_role_for_owners(
p_role => 'APP_DML_ROLE',
p_owners => SYS.ODCIVARCHAR2LIST('RM'),
p_dry_run => FALSE
);
END;
/
11. Validando os privilégios que foram concedidos aos usuários efetivamente.
SELECT owner, table_name, privilege, grantee
FROM dba_tab_privs
WHERE grantee = 'APP_DML_ROLE'
AND owner = 'RM';
OWNER TABLE_NAME PRIVILEGE GRANTEE
---------- ---------------------------------------- --------------- ---------------
RM P_AUDITGETDIF EXECUTE APP_DML_ROLE
RM SAUGETGRUPOGASTOTISS EXECUTE APP_DML_ROLE
Conclusão
Essa abordagem traz várias vantagens para ambientes Oracle:
• Centralização de permissões via ROLE
• Provisionamento consistente de usuários
• Automação de GRANTs em milhares de objetos
• Execução segura via dry-run
• Auditoria completa das operações
Esse tipo de automação é extremamente útil em ambientes corporativos com grande volume de objetos e múltiplos usuários de aplicação.
Fique à vontade em alterar a procedure e incluir outros grants necessários como DDL ou DCL.
