Schemas e Owners

Exemplo Prático

-- Criar usuário automaticamente cria o schema
CREATE USER app_vendas IDENTIFIED BY senha123
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;

GRANT CONNECT, RESOURCE TO app_vendas;

-- Conectar como app_vendas
CONNECT app_vendas/senha123

-- Criar objetos - eles pertencem ao schema APP_VENDAS
CREATE TABLE produtos (
    produto_id NUMBER PRIMARY KEY,
    nome VARCHAR2(100),
    preco NUMBER(10,2)
);

CREATE TABLE clientes (
    cliente_id NUMBER PRIMARY KEY,
    nome VARCHAR2(100),
    email VARCHAR2(100)
);

-- Verificar schema atual
SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM DUAL;
-- Retorna: APP_VENDAS

Resolução de Nomes (Name Resolution)

Quando você referencia um objeto sem schema, o Oracle segue esta ordem de busca:

-- Exemplo de resolução de nomes
-- Conectado como APP_VENDAS

-- Busca nesta ordem:
-- 1. APP_VENDAS.EMPLOYEES
-- 2. Sinônimo privado APP_VENDAS.EMPLOYEES
-- 3. Sinônimo público PUBLIC.EMPLOYEES
SELECT * FROM employees;

Verificar Privilégios Concedidos

-- Ver privilégios que VOCÊ concedeu a outros
SELECT grantee, table_name, privilege, grantable
FROM USER_TAB_PRIVS_MADE
ORDER BY grantee, table_name;

-- Ver privilégios que VOCÊ recebeu de outros
SELECT owner, table_name, privilege, grantable
FROM USER_TAB_PRIVS_RECD
ORDER BY owner, table_name;

-- Ver TODOS os privilégios de objetos (como DBA)
SELECT owner, table_name, grantee, privilege, grantable
FROM DBA_TAB_PRIVS
WHERE owner = 'HR'
ORDER BY table_name, grantee;

-- Revogar privilégios
REVOKE SELECT ON hr.employees FROM app_vendas;
REVOKE ALL ON hr.departments FROM app_vendas;

Casos de Uso Comuns

-- Conectar como DBA mas trabalhar no schema da aplicação
CONNECT dba_user/senha
ALTER SESSION SET CURRENT_SCHEMA = app_vendas;

-- Executar manutenção sem prefixar cada objeto
ANALYZE TABLE produtos COMPUTE STATISTICS;
REBUILD INDEX idx_produtos_nome ONLINE;
UPDATE produtos SET status = 'ATIVO' WHERE status IS NULL;
COMMIT;

-- Conectar com usuário administrativo
CONNECT migration_user/senha

-- Trabalhar em cada schema sem reconectar
ALTER SESSION SET CURRENT_SCHEMA = schema1;
-- importar dados do schema1

ALTER SESSION SET CURRENT_SCHEMA = schema2;
-- importar dados do schema2

ALTER SESSION SET CURRENT_SCHEMA = schema3;
-- importar dados do schema3

-- DBA investigando problema reportado por usuário
CONNECT dba/senha
ALTER SESSION SET CURRENT_SCHEMA = app_user;

-- Reproduzir queries do usuário exatamente como ele vê
SELECT * FROM minha_tabela WHERE status = 'ERRO';

-- Ver os mesmos objetos que o usuário vê
SELECT table_name FROM user_tables;

Privilégios para PUBLIC

-- Conceder SELECT a todos os usuários (usar com cautela!)
GRANT SELECT ON hr.feriados TO PUBLIC;

-- Agora QUALQUER usuário pode consultar
CONNECT qualquer_usuario/senha
SELECT * FROM hr.feriados;

-- Ver privilégios concedidos a PUBLIC
SELECT table_name, privilege
FROM DBA_TAB_PRIVS
WHERE grantee = 'PUBLIC'
  AND owner = 'HR';

-- Revogar privilégio de PUBLIC
REVOKE SELECT ON hr.feriados FROM PUBLIC;

-- Exemplo de sinônimo público + privilégio público
-- (padrão do Oracle para views do dicionário de dados)
CREATE PUBLIC SYNONYM v$session FOR sys.v_$session;
GRANT SELECT ON sys.v_$session TO PUBLIC;

-- Resultado: todos podem fazer
SELECT sid, username, status FROM v$session;

Explorar Objetos de um Schema

-- Ver estrutura de objetos no schema
SELECT object_type, COUNT(*) AS qty
FROM USER_OBJECTS
GROUP BY object_type
ORDER BY qty DESC;

-- Listar todas as tabelas do schema atual
SELECT table_name, tablespace_name, num_rows
FROM USER_TABLES
ORDER BY table_name;

-- Ver colunas de uma tabela específica
SELECT column_name, data_type, data_length, nullable, data_default
FROM USER_TAB_COLUMNS
WHERE table_name = 'PRODUTOS'
ORDER BY column_id;

-- Ver dependências entre objetos
SELECT name, type, referenced_owner, referenced_name, referenced_type
FROM USER_DEPENDENCIES
WHERE referenced_owner = 'HR'
ORDER BY name;

-- Objetos inválidos no schema
SELECT object_name, object_type, status
FROM USER_OBJECTS
WHERE status = 'INVALID'
ORDER BY object_type, object_name;

Exemplo de Estrutura Organizada

-- 1. Criar schemas de dados (owners)
CREATE USER vendas_data IDENTIFIED BY senha_forte
DEFAULT TABLESPACE app_data
QUOTA UNLIMITED ON app_data;

GRANT CONNECT, RESOURCE TO vendas_data;

-- 2. Criar objetos no schema de dados
CONNECT vendas_data/senha_forte

CREATE TABLE clientes (
    cliente_id NUMBER PRIMARY KEY,
    nome VARCHAR2(100),
    email VARCHAR2(100)
);

CREATE TABLE pedidos (
    pedido_id NUMBER PRIMARY KEY,
    cliente_id NUMBER REFERENCES clientes,
    data_pedido DATE,
    total NUMBER(10,2)
);

-- 3. Criar schema de código
CREATE USER vendas_code IDENTIFIED BY senha_forte
DEFAULT TABLESPACE app_data
QUOTA UNLIMITED ON app_data;

GRANT CONNECT, RESOURCE TO vendas_code;
GRANT SELECT, INSERT, UPDATE, DELETE ON vendas_data.clientes TO vendas_code;
GRANT SELECT, INSERT, UPDATE, DELETE ON vendas_data.pedidos TO vendas_code;

-- 4. Criar procedures no schema de código
CONNECT vendas_code/senha_forte

CREATE OR REPLACE PACKAGE pkg_vendas AS
    PROCEDURE registrar_pedido(p_cliente_id NUMBER, p_total NUMBER);
END;
/

CREATE OR REPLACE PACKAGE BODY pkg_vendas AS
    PROCEDURE registrar_pedido(p_cliente_id NUMBER, p_total NUMBER) IS
    BEGIN
        INSERT INTO vendas_data.pedidos (pedido_id, cliente_id, data_pedido, total)
        VALUES (seq_pedidos.NEXTVAL, p_cliente_id, SYSDATE, p_total);
        COMMIT;
    END;
END;
/

-- 5. Criar schema de aplicação (apenas privilégios, sem objetos)
CREATE USER vendas_app IDENTIFIED BY senha_app;
GRANT CONNECT TO vendas_app;

-- Dar acesso apenas via procedures
GRANT EXECUTE ON vendas_code.pkg_vendas TO vendas_app;

-- Aplicação conecta como vendas_app e executa:
-- EXECUTE vendas_code.pkg_vendas.registrar_pedido(100, 1500.00);

Cenário 3: Diagnóstico e Auditoria

-- Auditoria completa de schemas de aplicação
CONNECT system/senha

-- 1. Listar schemas e seus tamanhos
SELECT
    u.username,
    u.account_status,
    u.created,
    u.default_tablespace,
    NVL(ROUND(SUM(s.bytes)/1024/1024, 2), 0) AS size_mb,
    NVL(COUNT(DISTINCT o.object_name), 0) AS num_objects
FROM dba_users u
LEFT JOIN dba_segments s ON u.username = s.owner
LEFT JOIN dba_objects o ON u.username = o.owner
WHERE u.oracle_maintained = 'N'
GROUP BY u.username, u.account_status, u.created, u.default_tablespace
ORDER BY size_mb DESC;

-- 2. Mapear todos os privilégios entre schemas
SELECT
    grantee AS quem_recebe,
    owner || '.' || table_name AS objeto,
    privilege AS privilegio,
    grantable AS pode_repassar
FROM dba_tab_privs
WHERE owner IN ('LOJA', 'RELATORIOS')
  AND grantee NOT IN ('SYS', 'SYSTEM')
ORDER BY grantee, owner, table_name;

-- 3. Ver objetos compartilhados (com grants para outros schemas)
SELECT
    owner,
    table_name,
    COUNT(DISTINCT grantee) AS num_grantees,
    LISTAGG(grantee || ':' || privilege, ', ')
        WITHIN GROUP (ORDER BY grantee) AS grants
FROM dba_tab_privs
WHERE owner = 'LOJA'
GROUP BY owner, table_name
HAVING COUNT(DISTINCT grantee) > 0;

-- 4. Identificar dependências entre schemas
SELECT DISTINCT
    d.owner AS schema_dependente,
    d.referenced_owner AS schema_usado,
    COUNT(*) AS num_dependencias
FROM dba_dependencies d
WHERE d.owner != d.referenced_owner
  AND d.owner IN ('LOJA', 'RELATORIOS')
GROUP BY d.owner, d.referenced_owner
ORDER BY num_dependencias DESC;