O que devemos conhecer sobre SQL/Oracle Parte 18: Controlando o acesso dos usuários

Diferenciando privilégios de sistema de privilégios de objetos

Existem três categorias gerais para agrupar privilégios, que são:

• Privilégios de sistema: habilidade de realizar tarefas especificas no banco de dados, como por exemplo, logar ou criar uma tabela;
• Privilégios de objeto: habilidade de realizar tarefas em um determinado objeto do banco de dados;
• Regra (role): coleção de um ou mais privilégios de sistema e/ou privilégios de objeto, e/ou outras regras;

O que devemos ter em mente é o seguinte, privilégios de sistema é o que precisamos para criar objetos do banco de dados, por exemplo, o direito de criar uma tabela é um privilégio de sistema e a habilidade de alterar registros dessa tabela é um privilégio de objeto.

A seguir uma tabela com alguns dos privilégios de sistema:

Privilégios de sistema
Privilégio	Descrição
CREATE SESSION	Conectar a base.
CREATE TABLE	Criar uma tabela na conta do usuário, incluindo a capacidade de alterar e remover a tabela.
CREATE VIEW	Criar visões na conta do usuário, incluindo a capacidade de alterar e remover.
CREATE SEQUENCE	Criar sequência na conta do usuário, incluindo a capacidade de alterar e remover.
CREATE SYNONYM	Criar sinônimos na conta do usuário, incluindo a capacidade de alterar e remover (apenas privados).
CREATE ROLE	Criar uma regra, incluindo a capacidade de alterar e remover.
CREATE PUBLIC SYNONYM	Criar sinônimos na conta PUBLIC.
DROP PUBLIC SYNONYM	Remover um sinônimo da conta PUBLIC.
CREATE ANY TABLE	Criar tabelas em qualquer conta.
ALTER ANY TABLE	Alterar tabelas de qualquer conta.
DELETE ANY TABLE	Remover registros de qualquer tabela de qualquer conta.
DROP ANY TABLE	Remover tabelas de qualquer conta.
INSERT ANY TABLE	Inserir registros de qualquer tabela de qualquer conta.
SELECT ANY TABLE	Selecionar registros de qualquer tabela de qualquer conta.
UPDATE ANY TABLE	Atualizar registros de qualquer tabela de qualquer conta.
CREATE ANY VIEW	Criar visões em qualquer conta.
DROP ANY VIEW	Remover visões de qualquer conta.
CREATE ANY INDEX	Criar índices em qualquer conta.
ALTER ANY INDEX	Alterar índices de qualquer conta.
DROP ANY INDEX	Remover índices de qualquer conta.
CREATE ANY SEQUENCE	Criar sequências em qualquer conta.
ALTER ANY SEQUENCE	Alterar sequências de qualquer conta.
DROP ANY SEQUENCE	Remover sequências de qualquer conta.
SELECT ANY SEQUENCE	Selecionar de uma sequência de qualquer conta.
CREATE ANY SYNONYM	Criar sinônimos em qualquer conta.
ALTER ANY SYNONYM	Alterar sinônimos de qualquer conta.
CREATE ANY DIRECTORY	Criar diretórios em qualquer conta.
DROP ANY DIRECTORY	Remover diretórios de qualquer conta.
ALTER ANY ROLE	Alterar regras de qualquer conta.
DROP ANY ROLE	Remover regras de qualquer conta.
GRANT ANY ROLE	Delega qualquer regra da base.
FLASHBACK ANY TABLE	Realiza operações de FLASHBACK em qualquer tabela de qualquer conta.
CREATE USER	Criar conta de usuário.
ALTER USER	Alterar conta de usuário.
DROP USER	Remover conta de usuário.
GRANT ANY PRIVILEGE	Delega qualquer privilégio de sistema para qualquer usuário do banco.
GRANT ANY OBJECT PRIVILEGE	Delega para qualquer conta qualquer privilegio de objeto cujo dono possua direito de delegar.

Vamos a alguns exemplos:

CONNECT SYSTEM/SENHASYS;

CREATE USER MARIA IDENTIFIED BY SENHA123;

GRANT CREATE SESSION, CREATE TABLE TO MARIA;

GRANT UNLIMITED TABLESPACE TO MARIA;

Considerando que estamos executando os comandos no SQL Plus, a primeira linha conecta o usuário SYSTEM ao banco de dados, na segunda o usuário MARIA é criado, na terceira é concedido ao usuário recém-criado o direito de se conectar a base e criar tabelas, e por fim foi definido que esse usuário terá espaço ilimitado no TABLESPACE (o ultimo comando não deve ser replicado em ambiente de produção, sempre deve ser definido uma cota de TABLESPACE para os usuários).

CONNECT SYSTEM/SENHASYS;

CREATE USER JOAO IDENTIFIED BY SENHA123;

GRANT CREATE SESSION, CREATE ANY TABLE TO JOAO;

GRANT UNLIMITED TABLESPACE TO JOAO;

CONNECT JOAO/SENHA123;

CREATE TABLE MARIA.TESTE (CODIGO NUMBER(10));

No exemplo acima criamos mais um usuário nas primeiras linhas, JOAO, o diferença fica apenas por conta da palavra ANY incluída na delegação do privilégio de criação de tabelas, concedendo a JOAO o direito de criar uma tabela na conta de qualquer usuário, e foi justamente isso o que fizemos, primeiro nos conectamos a nova conta na linha cinco e então criamos uma tabela chamada TESTE na conta do usuário MARIA.

É possível em um comando de GRANT conceder o privilegio de replicar esse mesmo privilegio para outros usuários:

GRANT privilegio TO usuário WITH ADMIN OPTION;

No exemplo acima o usuário poderá conceder esse mesmo direito para outro usuário.

É possível também conceder todos os privilégios disponíveis de uma só vez:

GRANT ALL PRIVILEGES TO USER;

Resumindo:

• O direito de usar qualquer comando SQL e/ou executar uma tarefa no banco é um privilegio de sistema;

• O direito de usar um privilégio de sistema para realizar uma tarefa em um objeto em especifico do banco é um privilégio de objeto;

• Ambos são garantidos ou revogados de usuários do banco;

• Privilégios de sistema atribuídos com WITH ADMIN OPTION, garantem a habilidade ao usuário que recebeu o privilegio de repassar o mesmo a outro usuário;

• Quando um privilégio de sistema é revogado, a revogação não ocorre em cascata, ou seja, ela ocorre apenas para o usuário indicado no comando;

• ALL PRIVILEGES pode ser usado para atribuir ou revogar todos os privilégios de ou para um usuário;

Atribuindo privilégios a tabelas

Quando uma tabela é criada, o usuário passa a ter direitos de selecionar, incluir e atualizar dados nessa tabela, o mesmo não é valido para outros usuários, que sequer saberão que a tabela existe. Para que outros usuários possam ver a tabela o dono da mesma deve delegar direitos como GRANT SELECT, UPDATE. Ao receber o direito de acesso a uma tabela de outro usuário é necessário sempre indicar primeiro o nome do usuário e em seguida o nome da tabela, a menos que um sinônimo seja criado.

Os direitos de acesso e alteração uma vez concedidos podem ser revogados a qualquer momento com o comando REVOKE. É importante salientar que se uma tabela for removida da base todos os GRANTS concedidos a outros usuários também são eliminados, caso a tabela seja criada novamente esses privilégios devem ser criados novamente.

Resumindo:

• Privilégios de objetos correspondem a comandos DML, e para comandos DDL que são relevantes para a existência de objetos;
• Privilégios de objetos podem ser atribuídos com WITH GRANT OPTION, que provê a capacidade a quem recebeu o privilegio repassar o mesmo a outro usuário;
• Quando um privilégio de objeto é revogado ele ocorre em cascata, ou seja, todos os usuários que receberam esse privilégio também o perdem;
• Quando um usuário recebe o direito de acesso a um objeto, o nome do objeto deve possuir como prefixo o nome do esquema ao qual ele pertence;
• Um PUBLIC SYNONYM provê uma alternativa para evitar a necessidade do uso do esquema no nome dos objetos;
• ALL PRIVILEGES pode ser usado para atribuir ou revogar todos os privilégios de ou para um usuário;

Visualizando privilégios no dicionário de dados

O dicionário de dados possui algumas tabelas para que possamos ver quais os privilégios temos sobre os objetos do banco, vejamos alguns:

Visões do dicionário de dados que dizem respeito a privilégios
Visões	Descrição
USER_SYS_PRIVS	Privilégios de sistema atribuídos para o usuário atual.
DBA_SYS_PRIVS	Privilégios de sistema atribuídos para usuários e regras.
USER_TAB_PRIVS	Privilégios sobre objetos para cada usuário que concedeu, recebeu ou cujo ele seja o dono.
ALL_TAB_PRIVS	Privilégios sobre objetos para cada usuário que concedeu, recebeu ou cujo ele seja o dono, ou uma regra ativa ou publica.
DBA_TAB_PRIVS	Privilégios de todos os objetos da base.
ALL_TAB_PRIVS_RECD	Privilégios de objetos para cada usuário, publico, ou regra ativa.
SESSION_PRIVS	Privilégios do usuário atual.

Resumindo:

• Existem varias visões que permitem a consulta sobre os privilégios de sistema e objetos;
• Usuários podem visualizar privilégios garantidos a si mesmos, ou a outros usuários por meio dessas visões;

Atribuindo regras (ROLES)

Uma regra é um objeto onde é possível indicar privilégios de sistema ou de objeto, desse modo podemos atribuir uma série de privilégio a um único objeto (regra) e então realizar GRANTs apenas da regra para os usuários. Isso não só garante uma maior agilidade durante as atribuições como também das revogações, já que basta revogar da regra para que todos os usuários ligados a ela sejam afetados. Veja um exemplo:

CREATE ROLE DIREITOS_INICIAIS;

GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE TO DIREITOS_INICIAIS;

GRANT DIREITOS_INICIAIS TO JOSE;

No exemplo acima criamos uma regra, em seguida atribuímos alguns privilégios para ela e em seguida atribuímos a regra para um usuário, logo todos os privilégios contidos na regra ficam disponíveis para o usuário.

Também é possível atribuir regras para regras, e até mesmo atribuir regras com WITH ADMIN OPTION, o que garante para o usuário o direito de repassar a regra para outros usuários, perceba que se o usuário A recebeu uma regra e a repassou para o usuário B, esse ultimo não perde os direitos da regra quando a mesma é revogada do usuário A, para isso a regra deve ser revogada explicitamente do usuário B.

Uma regra criada fica armazenada em um namespace a parte, fora da área do usuário. É importante lembrar também que um usuário pode receber privilégios de varias regras ao mesmo tempo. Para obter informações sobre as regras podemos consultar as seguintes visões:

Visões do dicionário de dados que dizem respeito a regras (roles)
Visões	Descrição
DBA_ROLES	Todas as regras que existem na base.
DBA_ROLE_PRIVS	Regras atribuídas aos usuários e outras regras.
DBA_SYS_PRIVS	Privilégios de sistema atribuídos a usuários e regras.
DBA_TAB_PRIVS	Todos os privilégios em objetos para usuários e regras.
ROLE_ROLE_PRIVS	Regras atribuídas a regras.
ROLE_SYS_PRIVS	Privilégios de sistemas atribuídos a regras.
ROLE_TAB_PRIVS	Privilégios de tabela atribuídos a regras.
SESSION_ROLES	Regras disponíveis para o usuário atual.

Resumindo:

• Uma regra é criada com o comando CREATE ROLE;
• Regras podem ser atribuídas com GRANT ADMIN OPTION, permitindo que a mesma seja repassada a outros usuários;
• Regras existem fora do NAMESPACE do usuário;
• Uma regra é uma coleção de privilégios e outras regras;
• Uma regra pode ser atribuída a outras regras;

Distinguindo privilégios e regras

Regras não representam um privilégio necessariamente, elas podem inclusive existir sem possuir um privilégio associado, ela é uma coleção de privilégios. Outro fato importante é que privilégios presentes em uma regra não substituem regras que um usuário tenha, logo se um usuário recebe o privilegio de criar uma tabela, por exemplo, sendo que ele já possuía esse privilegio ao remover a regra o direito de criar tabelas não é removido, para tal o privilegio concedido anteriormente à regra deve ser revogado de forma explicita.

Resumindo:

• Privilégios atribuídos diretamente a um usuário existem independentes de um privilégio atribuído por uma regra;
• Se um privilégio for revogado diretamente de um usuário que já possua uma regra que contenha esse privilégio em questão, a regra permanece igual e o usuário continua com o privilégio;
• O oposto da situação descrita acima também ocorre, se uma regra for revogada, privilégios atribuídos de forma individual permanecem válidos;