Apagar/Dropar Usuário Online: Mantendo o Banco estéreo

Imagine que você está organizando uma festa em sua casa. Cada convidado representa um usuário no banco de dados Oracle. No final da festa, é essencial garantir que todos os pertences dos convidados sejam retirados e que a casa fique organizada. No mundo do Oracle, esse processo de “limpeza” é realizado através do comando DROP USER.

O comando DROP USER permite que administradores de banco de dados (DBAs) removam completamente um usuário e todos os seus objetos associados do banco de dados. Contudo, quando lidamos com usuários que possuem schemas — conjuntos de objetos como tabelas, índices e procedimentos — a tarefa se torna mais complexa. Um simples DROP USER não é suficiente para eliminar esses elementos associados.

Se o usuário estiver online e conectado ao banco, a remoção é ainda mais desafiadora. É aqui que o comando DROP USER … CASCADE se torna crucial. Ele garante que sua “festa” no banco de dados continue organizada e segura, mesmo após a saída dos “convidados” e seus “pertences”, independentemente de estarem online ou não.

Agora, vamos para a prática. Como usuário SYS, é importante preparar o ambiente adequadamente para realizar o exercício de forma eficaz.

Para começar, vamos configurar o ambiente no Oracle Database, criando um novo tablespace e um usuário, e concedendo os privilégios necessários. Siga os passos abaixo para garantir que tudo esteja pronto para o exercício de remoção de usuários online.

-- Criar Tablespace
CREATE TABLESPACE TBS_USUARIO_X;

-- Criando usuário
CREATE USER USR_ONLINE
IDENTIFIED BY senha
DEFAULT TABLESPACE TBS_USUARIO_X
QUOTA 500M ON TBS_USUARIO_X;

-- Grants
GRANT CREATE SESSION TO USR_ONLINE;
GRANT CREATE TABLE TO USR_ONLINE;
GRANT ALTER ANY TABLE TO USR_ONLINE;
GRANT SELECT ANY TABLE TO USR_ONLINE;
GRANT DELETE ANY TABLE, EXECUTE ANY PROCEDURE TO USR_ONLINE;
GRANT SELECT ANY DICTIONARY TO USR_ONLINE;
GRANT CONNECT TO USR_ONLINE;
GRANT RESOURCE TO USR_ONLINE;

Pois bem usuário criado e com privilégios nos quais podemos visualizar com a view: DBA_SYS_PRIVS

Invertendo o papel e conectando como o usuário USR_ONLINE me conecto ao banco:

Como prova que este é um usuário do cotidiano que tem os privilégios DML vamos aproveitar para criar uma tabela e inserir dados a ela.

CREATE TABLE FUTEBOL (
    cliente_id NUMBER,
    nome VARCHAR2(50),
    email VARCHAR2(50)
);

INSERT INTO FUTEBOL (cliente_id, nome, email)
VALUES (1, 'Cristiano Ronaldo', 'cr7@example.com');

Provado que é um usuário de respeito com todos os seus direitos e privilégios.

Agora como usuário SYS vamos dropar o usuário USR_ONLINE

DROP USER USR_ONLINE;

DROP USER USR_ONLINE CASCADE;

Como assim! O SYS não consegue apagar o usuário, e percebam que mesmo que inclua o parâmetro CASCADE ao comando é inútil  a sua ação.

ALTER USER USR_ONLINE ACCOUNT LOCK;

Nem tudo está perdido, o usuário SYS pode agir da seguinte maneira a primeira ação é proibir o acesso ao usuário USR_ONLINE em novas sessões com, ACCOUNT LOCK na conta do usuário, isso impede que o usuário USR_ONLINE não consiga logar ao banco, lembrando que o usuário  continuará logado.

A ação de ACCOUNT  LOCK não derruba a conexão e o usuário USR_ONLINE pode continuar conectado e ativo, prova disso é:

INSERT INTO FUTEBOL (cliente_id, nome, email)
VALUES (2, 'Lionel Messi', 'lm10@example.com');

SELECT * FROM FUTEBOL;

A imagem não deixa dúvidas que o usuário USR_ONLINE está na ativa continua fazendo os seus DML normalmente, como se nada tivesse acontecendo.

Feito as contestações voltamos ao usuário SYS, a possibilidade de impedir o progresso do usuário USR_ONLINE é matarmos a sessão em que ele está conectado, para isso precisamos de 2 informações importantes que são: SID, SERIAL# com estes dois dados podemos matar a sessão do usuário.

Para extrairmos estas informações podemos usar a view de performance dinâmica que é: V$SESSION.
Vejamos esta view em ação:

SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME = 'USR_ONLINE';

Assim conseguimos visualizar:  SID = 518, SERIAL# 21078, e é com estes dados que vamos matar a sessão do usuário.

***Lembrando que estes números são dinâmicos e só serão válidos para esta máquina este momento.

ALTER SYSTEM KILL SESSION 'SID, SERIAL';
ALTER SYSTEM KILL SESSION '518, 21078';

Um comando silencioso e simples mas com grandes resultados, tão grandes que podemos ver que o usuário USR_ONLINE não pode mais ter ação nenhuma.

Assim conectado o usuário USR_ONLINE ao tentar fazer uma inserção a ação é negada, com uma advertencia bem clara de que sua sessão foi morta, o usuário assim não tem privilégio nenhum sobre o banco.

Nem mesmo um simples SELECT em um SCHEMA que é dele.

Agora sim o usuário SYS tem permissão para fazer o DROP do usuário USR_ONLINE.

Sim sobre o banco pois antes de matarmos a sua sessão a sua conta foi bloqueada com ‘ACCOUNT LOCK’ aqui por sí só o usuário está morto, não consegue executar nenhum DML e nem acessar o banco de dados.

SELECT USERNAME, ACCOUNT_STATUS
FROM DBA_USERS
WHERE USERNAME = 'USR_ONLINE';

Sendo assim o usuário SYS tem o poder de executar o DROP e nenhum erro será retornado. Porem é preciso alertar que se o DROP do usuário acontecer tudo que pertence ao seu SCHEMA será apagado junto com ele, suas tabelas indices e tudo feito o alerta vamos a execução do comando.

Assim como o anfitrião de uma festa que precisa garantir que cada convidado leve seus pertences ao sair, um DBA deve assegurar que ao remover um usuário, todos os seus dados e objetos sejam adequadamente eliminados, mantendo o banco de dados organizado e eficiente. No entanto, a tarefa de “dropar” um usuário no Oracle Database não é sempre simples, especialmente quando se trata de usuários com schemas complexos e sessões ativas.

Neste artigo, exploramos as nuances de apagar um usuário online, destacando os passos necessários para garantir que nenhum vestígio permaneça. Desde a criação e concessão de privilégios ao usuário, até o bloqueio de sua conta e a derrubada de suas sessões, cada etapa foi cuidadosamente detalhada para mostrar a importância de uma gestão precisa e responsável dos recursos do banco de dados.

A lição fundamental aqui é que, mesmo quando parece que o usuário SYS não pode imediatamente remover um usuário ativo, há sempre uma estratégia eficiente para alcançar esse objetivo. Bloquear a conta, matar a sessão ativa e, finalmente, executar o comando DROP USER com sucesso garantem que a “festa” no banco de dados continue organizada e que o espaço em disco seja liberado de maneira segura e controlada.

Com esses conhecimentos em mãos, você está pronto para enfrentar os desafios de gerenciar usuários no Oracle Database, assegurando que seu ambiente de banco de dados permaneça otimizado e seguro. Lembre-se, a maestria está nos detalhes, e com prática e atenção, você se tornará um anfitrião excepcional no mundo dos DBAs.

Agora que dominamos a teoria, é hora de aplicar esses conceitos em um ambiente real. Siga os passos, teste os comandos e veja como o gerenciamento de usuários pode ser eficiente e preciso. Boa sorte e sucesso na sua jornada como DBA!