# 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. ```sql 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. ```sql 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. ```sql 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. ```sql 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. ```sql 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 ```sql 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** ```sql 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 ```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** ```sql 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.** ```sql 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.** ```sql TRUNCATE TABLE sec_grant_log; ``` **10\. Executando os GRANTs efetivamente** ```sql 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.** ```sql 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.