Administração Oracle com Tablespaces, Datafiles e Gerenciamento de usuários e Privilégios

Um Administrador de Banco de Dados ou também chamado por DBA (acrônimo para Database Administrator) tem diversas atribuições que compreendem desde a avaliação do hardware do servidor que hospedará o Banco de Dados até o seu monitoramento contínuo.

Um administrador de banco de dados precisa entender os conceitos dos bancos de dados como: seus estados possíveis, características gerais do banco de dados, funcionalidades e também realizar diversas tarefas que fazem parte da sua responsabilidade e do seu dia-a-dia como criação de objetos, estruturas físicas e lógicas, criação de usuários, concessão e revogação de privilégios aos usuários, inicialização e interrupção dos serviços do banco de dados, backups, restauração, entre diversas outras.

No restante do artigo veremos o que são tablespaces e datafiles e como eles se associam. Também veremos como podemos criar e excluir tablespaces e datafiles, além de estudarmos como podemos gerenciar usuários e conceder/revogar privilégios no banco de dados.

Tablespaces e Datafiles

O Oracle armazena objetos como as tabelas e índices em espaços alocados dentro do banco de dados. Esses espaços são os tablespaces, o qual está associado a um ou mais arquivos físicos, que são os datafiles. Cada datafile está associado a somente uma tablespace. Um datafile pode ser adicionado, removido, movido ou redimensionado, de acordo com as necessidades do sistema. Assim, dizemos que as informações em um banco de dados são armazenadas logicamente nos tablespaces e fisicamente nos datafiles.

Através de uma consulta bem simples podemos verificar os arquivos físicos associados ao seu nome lógico da tablespace, como mostra o comando a seguir:

SQL> SELECT tab.name, dat.name FROM v$tablespace tab, v$datafile dat WHERE tab.ts# = dat.ts#;

Como resultado teremos o nome do tablespace SYSTEM seguido pela localização do arquivo físico que se encontra em “C:\APP\ORACLE\ORADATA\TAXI\SYSTEM01.DBF”, SYSAUX seguido pela localização do arquivo físico que se encontra em “C:\APP\ORACLE\ORADATA\TAXI\SYSAUX01.DBF”, UNDO seguido pela localização do arquivo físico que se encontra em “C:\APP\ORACLE\ORADATA\TAXI\UNDO01.BDF” e USERS seguido pela localização do arquivo físico que se encontra em “C:\APP\ORACLE\ORADATA\TAXI\USERS01.BDF”.

Uma tablespace é composta por vários segmentos lógicos que podem estar distribuídos em vários datafiles ou em apenas um datafile, mas um datafile sempre está associado a uma tablespace. O termo segmento refere-se a qualquer estrutura que contenha dados, como, por exemplo, uma tabela que é chamada de segmento de dados, ou um índice que é chamado de segmento de índice. Dessa forma, existem diferentes tipos de segmentos em um banco de dados, como: tabela (ou dados), índice, cluster, rollback ou undo, rollback deferido, temporários, logsebment, lobindex, entre outros.

Criando Tablespaces e Datafiles

Algumas tablespaces são criadas automaticamente durante o processo de criação do banco de dados, algumas dessas tablespaces já vimos anteriormente na listagem realizada. A tablespace SYSTEM contém o dicionário de dados e todas as informações necessárias para o gerenciamento do banco de dados, é uma tablespace essencial para o bom funcionamento do banco de dados; a tablespace SYSAUX contém tabelas utilizadas por ferramentas do próprio banco de dados; a tablespace UNDO contém informações sobre a recuperação de transações, assim, caso uma transação precise ser desfeita o banco de dados usa as informações dessa tablespace para recuperar a informação; a tablespace TEMP contém os dados temporários que são criados durante a execução de instruções SQL; por fim, a tablespace USERS é a tablespace default (ou padrão) do usuário, onde todo objeto criado pelo usuário estará nesta tablespace.

Segue na Listagem 1 a sintaxe geral para criação de tablespaces.

Listagem 1. Sintaxe para criação de tablespaces.

CREATE [TEMPORARY/UNDO] TABLESPACE tablespace_name DATAFILE/TEMPFILE '<nome_caminho_arq_datafile01>' SIZE <integer M> [', <nome_caminho_arq_datafile02>' SIZE <integer M>, <nome_caminho_arq_datafileN>' SIZE <integer M>'] BLOCKSIZE <DB_BLOCL_SIZE parameter /2k/4k/8k/16k/32k> AUTOEXTEND { [OFF/ON (NEXT <INTEGER K/M> MAXSIZE<INTEGER K/M>) / UNLIMITED ] } LOGGING | NOLOGGING ONLINE/OFFLINE (Online default) EXTENT MANAGEMENT {[DICTIONARY]/[LOCAL default (AUTOLLOCATE/UNIFORM<integer K/M>)] } PERMANENT / TEMPORARY (PERMANENT default) MINIMUM EXTENT DEFAULT STORAGE { [INITIAL <interger K/M>] [NEXT <interger K/M>] [PCTINCREASE <interger K/M>] [MINEXTENTS <interger>] [MAXEXTENTS <interger>/UNLIMITED] [FREELISTS <interger>] [FREELISTS GROUPS <interger>] [OPTIMAL <interger>/NULL] [BUFFER_POOL <DEFAULT/KEEP/RECYCLE>] } CHUNK <interger K/M> NOCACHE;

Alguns dos elementos importantes na sintaxe de criação da tablespace é o CREATE que indica a criação de objetos no banco de dados, TEMPORARY/UNDO que indica a criação de uma tablespace temporária ou undo que serão melhores detalhados posteriormente, TABLESPACE que define que o objeto a ser criado é uma tablespace, tablespace_name define o nome da tablespace, DATAFILE/TEMPFILE define se o arquivo de dados criado conterá dados permanentes ou temporários, SIZE define o tamanho do arquivo de dados, BLOCKSIZE define o tamanho da blocagem usada pela tablespace, AUTOEXTEND define se o tamanho do arquivo aumentará automaticamente, OFF/ON indica se a opção AUTOEXTEND está ativa ou não sendo que ela está desativada por padrão, NEXT define o tamanho da próxima extensão a ser criada, MAXSIZE/UNLIMITED define se o tamanho do arquivo de dados criado terá tamanho máximo ou é ilimitado, LOGGING/NOLOGGING define se as operações efetuadas serão registradas ou não sendo que ela é desativada por padrão, AUTOALLOCATE/UNIFORM indica se a alocação de espaço para o arquivo de dados será em alocação de espaço de tamanho uniforme ou automática, PERMANENT/TEMPORARY indica se a tablespace será usada para dados permanentes ou temporários, MINIMUM EXTENT define o tamanho mínimo para o crescimento dos arquivos de dados associados à tablespace, INITIAL indica o tamanho inicial das extensões dos arquivos de dados, NEXT indica o tamanho da próxima extensão a ser incluída no arquivo de dados, PCTINCRASE define a taxa de crescimento das próximas extensões a serem incluídas no arquivo de dados, MINEXTENTS e MAXEXTENTS definem a quantidade mínima e máxima de extensões de um arquivo de dados, BUFFER_POOL define o tipo de buffer pool usado pelos objetos da tablespace e CHUNK define o tamanho do chunk de memória.

Segue na Listagem 2 um exemplo de como podemos criar uma tablespace.

Listagem 2. Criando uma tablespace.

CREATE TABLESPACE exemplo_tablespace LOGGING DATAFILE ‘C:\ORADATA\exemplo_tablespace.dbf’ SIZE 100m AUTOEXTEND ON NEXT 100m EXTENT MANAGEMENT LOCAL;

Também devemos vincular um usuário no Oracle a esta tablespace conforme exemplo da Listagem 3.

Listagem 3. Vinculando um usuário à tablespace.

CREATE USER exemplo_usuario IDENTIFIED BY senha_do_usuario DEFAULT TABLESPACE exemplo_tablespace QUOTA UNLIMITED ON exemplo_tablespace;

Conforme verificamos anteriormente podemos criar diferentes tablespaces, como uma tablespace permanente. A tablespace permanente é utilizada quando desejamos armazenar dados e os objetos criados pelo usuário como tabelas, índices e views.

Segue na Listagem 4 a criação de uma tablespace permanente.

Listagem 4. Criando uma tablespace permanente.

CREATE TABLESPACE exemplo_tablespace_permanente DATAFILE 'C:\ORADATA\testetablespaceperm01.dbf' SIZE 100M;

No comando acima criamos uma tablespace com o nome exemplo_tablespace_permanente e a associação dela com o arquivo de dados “testetablespaceperm01.dbf” com o tamanho de 100 MB. Outra forma de criar essa tablespace permanente seria definindo AUTOEXTEND ON MAXSIZE 300M após a opção SIZE 100M. Assim, indicaríamos que a tablespace teria um tamanho inicial de 100 MB com crescimento automático do arquivo de dados de até 300 MB.

Uma tablespace temporária é usada para armazenar dados em transição como dados de tabelas temporárias globais ou o resultado da classificação de dados. Segue na Listagem 5 um exemplo de como poderíamos criar uma tablespace temporária.

Listagem 5. Criando uma tablespace temporária.

CREATE TEMPORARY TABLESPACE exemplo_tablespace_temporaria TEMPFILE 'C:\ORADATA\testetablespacetemp01.dbf' SIZE 100M;

Por fim, ainda podemos criar uma tablespace undo que é utilizada para restaurar a condição original dos dados de uma tabela após uma falha de uma transação ou da execução da instrução rollback. Na Listagem 6 temos um exemplo de criação da tablespace UNDO.

Listagem 6. Criando uma tablespace undo

CREATE UNDO TABLESPACE exemplo_tablespace_undo DATAFILE 'C:\ORADATA\testetablespaceundo01.dbf' SIZE 20M;

Excluindo Tablespaces e Datafiles

A exclusão de tablespaces é realizada através da sintaxe da Listagem 7.

Listagem 7. Sintaxe para exclusão de tablespaces.

DROP TABLESPACE <tablespace_name> [INCLUDING CONTENTS [AND DATAFILES]]

Dados os elementos na sintaxe de deleção do tablespace temos o comando DROP TABLESPACE <tablespace_name> que exclui a tablespace caso nenhum objeto esteja associado a ela, eliminando assim a referência lógica e mantendo a estrutura física (datafile); o comando DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS exclui a tablespace e os objetos associados a ela eliminando a referência lógica e mantendo a estrutura física (datafile); por fim, o comando DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS AND DATAFILES exclui o tablespace e os objetos associados a ela eliminando a referência lógica e a estrutura física (datafile).

Gerenciando Usuários

Todo DBA tem a tarefa de criar e gerenciar os usuários. Os usuários são criados com acesso restrito e com privilégio mínimo. Quando criamos o usuário também criamos um schema (esquema) que trata-se de um conjunto de objetos de banco de dados que pertence a um usuário específico. Entre os objetos de um esquema temos as tabelas, views, sequências, sinônimos, procedimentos, pacotes, tipos de dados definidos pelo usuário, índices, clusters, triggers, entre outros.

Uma conta de usuário tem diversos atributos que podem ser definidos no momento de sua criação, ou altera-los posteriormente. Porém, no momento da criação de uma conta de usuário devemos definir obrigatoriamente o nome do usuário e o método de autenticação, sendo que os demais assumem valores default. Entre os atributos opcionais temos a tablespace padrão, quota de tablespace, tablespace temporária, perfil de usuário e status da conta.

Segue na Listagem 8 a sintaxe para criação de usuários.

Listagem 8. Sintaxe para criação de usuários.

CREATE USER nome_do_usuario IDENTIFIED {BY senha | EXTERNALLY | GLOBALLY AS 'nome_externo'} [DEFAULT TABLESPACE nome_da_tablespace] [TEMPORARY TABLESPACE nome_da_tablespace] [QUOTA int {K | M} ON nome_da_tablespace] [QUOTA UNLIMITED ON nome_da_tablespace] [PROFILE nome_do_perfil] [PASSWORD EXPIRE] [ACCOUNT {LOCK|UNLOCK}]

Entre os elementos acima temos o CREATE que é a instrução para criação de objetos no banco de dados, USER que indica a criação de um usuário, nome_do_usuario indica o nome do objeto sendo criado, IDENTIFIED BY/EXTERNALLY/GLOBALLY define como o usuário será autenticado, DEFAULT TABLESPACE define o nome do tablespace padrão do usuário onde serão criados os objetos desse usuário, TEMPORARY TABLESCPACE define o nome da tablespace temporária padrão do usuário onde serão armazenados os dados das tabelas temporárias globais além dos objetos gerados durante operações de classificação (ORDER BY) e agrupamento (GROUP BY) além de outras instruções, QUOTA define a quantidade máxima de espaço que o usuário poderá utilizar em uma tablespace, QUOTA UNLIMITED define que não há limite de espaço usado em uma tablespace, PROFILE define o perfil do usuário, PASSWORD EXPIRE define a criação de um usuário com senha expirada, ACCOUNT LOCK/UNLOCK define se a conta do usuário é bloqueada ou não na criação.

Para criarmos um usuário com nome "devmedia" e uma senha "12345" poderíamos utilizar a instrução a seguir:

CREATE USER devmedia IDENTIFIED BY 12345;

Não podemos esquecer que o nome do usuário deve ser único no banco de dados e sempre deve iniciar por uma letra, ter no máximo 30 caracteres e podemos ter número, letras e os caracteres cifrão ($) e underline (_) após a primeira letra.

O perfil do usuário é quem controla a configuração de senha e permite o gerenciamento de alguns recursos. Um exemplo é o status da conta que se encontra na coluna account_status da view dba_users.

Entre os status da conta do usuário temos o OPEN que indica que a conta está disponível para uso, LOCKED indica que a conta está bloqueada pelo administrador, EXPIRED indica que a senha está expirada, EXPIRED & LOCKED indica que a senha está expirada e bloqueada, EXPIRED (GRACE) indica que a senha está expirada porém dentro do período de carência, LOCKED (TIMED) indica que a senha está bloqueada após informar algumas vezes ao usuário que a senha estava errada, EXPIRED & LOCKED (TIMED) indica que a senha está expirada e a conta bloqueada após informar algumas vezes ao usuário que a senha estava errada, EXPIRED (GRACE) & LOCKED indica senha expirada porém dentro do período de carência e bloqueada pelo administrador, e por fim EXPIRED (GRACE) & LOCKED (TIMED) que indica senha expirada porém dentro do período de carência e bloqueada após informar algumas vezes ao usuário que a senha estava errada.

Dessa forma, verificamos que o administrador pode, entre outras coisas, bloquear a conta do usuário e desbloquear a conta. Para bloquear uma conta podemos utilizar a instrução a seguir:

ALTER USER nome_do_usuario_a_ser_bloqueado ACCOUNT LOCK;

E para desbloquear a conta podemos utilizar a instrução abaixo:

ALTER USER nome_do_usuario_a_ser_desbloqueado ACCOUNT UNLOCK;

O DBA também pode alterar ou expirar a senha do usuário. Para alterar a senha do usuário poderíamos utilizar a instrução:

ALTER USER nome_do_usuario_para_alterar_senha IDENTIFIED BY nova_senha;

E para expirar a senha do usuário poderíamos utilizar a instrução abaixo:

ALTER USER nome_do_usuario_para_expirar_senha PASSWORD EXPIRE;

Algumas vezes é interessante expirarmos a senha do usuário quando a conta está bloqueada e a tentativa de desbloqueio não é realizada com sucesso ou quando o usuário esquece a senha.

Gerenciando Privilégios

Quando um usuário é criado ele não tem privilégio para executar nenhuma tarefa até receber os privilégios necessários, a esse conceito é dado o nome de conjunto de privilégios mínimos.

A sintaxe para concedermos privilégios é dada a seguir:

GRANT privilegios ON [schema.]objeto TO usuarios_que_receberao_privilegios [WITH ADMIN OPTION | WITH GRANT OPTION];

Entre os elementos acima temos o GRANT que é a instrução que concede privilégios, privilegios são os privilégios que serão concedidos, ON indica o objeto que receberá os privilégios, TO indica o(s) usuário(s) que receberá os privilégios, WITH ADMIN OPTION indica que aquele que recebe o privilégio de sistema pode conceder o privilégio recebido, e por fim, WITH GRANT OPTION indica que aquele que recebe o privilégio de objeto pode conceder o privilégio recebido.

Além de conceder privilégios podemos também revogar privilégios.

A sintaxe para revogarmos privilégio é a seguinte:

REVOKE privilegios ON [schema.]objeto FROM usuarios;

Entre os elementos acima temos o REVOKE que é a instrução que revoga privilégios recebidos, privilegios são os privilégios que serão revogados, ON indica o objeto que terá privilégios revogados, e por fim, FROM indica o usuário que terá privilégios revogados.

Os privilégios dos usuários são divididos em duas categorias: privilégios de objetos e privilégios de sistema. Os privilégios de objetos permitem ações que afetam os dados de um objeto, sendo que esses objetos podem ser tabelas, views, sequences, procedures, funções ou pacotes. Os privilégios de sistema permitem que o usuário execute operações que afetem o dicionário de dados.

Dessa forma, podemos conceder privilégios de sistema como CREATE SESSION para logon do usuário no banco, ALTER SESSION para alterar as definições da sessão corrente, CREATE TABLE para criar tabelas, CREATE VIEW para criar views, CREATE SYNONYM para criar sinônimos, CREATE CLUSTER para criar clusters, CREATE DATABASE LINK para criar database links, CREATE SEQUENCE para criar sequências, CREATE TRIGGER para criar triggers, CREATE PROCEDURE para criar procedimentos, entre outros.

Segue na Listagem 9 um exemplo de como podemos conceder privilégios para um usuário criar tabelas, views, sequence, triggers e procedures no banco de dados.

Listagem 9. Concedendo privilégios a um usuário.

GRANT CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE TRIGGER, CREATE PROCEDURE TO usuario_exemplo;

Se adicionarmos a opção WITH ADMIN OPTION permitiríamos ao usuário transferir para outro usuário o privilégio de sistema recebido.

Entre os privilégios de objeto poderíamos permitir ao usuário a execução das instruções SELECT, INSERT, UPDATE e DELETE em tabelas e objetos do usuário ou de outros usuários.

Segue um exemplo abaixo onde o usuário recebe o privilégio de realizar consulta na tabela PRODUTOS de outro usuário chamado devmediauser1:

GRANT SELECT ON devmediauser1.produtos TO usuario_exemplo;

Para especificar uma coluna que o usuário pode realizar uma consulta poderíamos fazer como abaixo:

GRANT SELECT(nome) ON devmediauser1.produtos TO usuario_exemplo;

Para conceder todos os privilégios na tabela PRODUTOS poderíamos fazer como abaixo:

GRANT ALL ON devmediauser1.produtos TO usuario_exemplo;

É muito importante que o DBA sempre verifique antecipadamente o perfil do usuário que deverá receber um privilégio, a concessão de privilégios pode comprometer seriamente a segurança dos dados de uma organização.

Bibliografia

[1] P, Sandra; F. Edson; M. Goya. Banco de Dados Implementação em SQL, PL/SQL e Oracle 11g. Pearson, 2014.

[2] Introdução ao conceito de Tablespaces, Oracle Documentation. Disponível em http://www.oracle.com/technetwork/pt/articles/database-performance/introducao-conceito-de-tablespaces-495850-ptb.html

[3] Create Tablespace, Oracle Documentation. Disponível em http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7003.htm