linguagem SQL: subconjuntos DCL, DDL e DML

BANCO DE DADOS WEB
AULA 4
linguagem SQL: subconjuntos DCL, DDL e DML
professor Luciano Roberto Rocha
www.lrocha.com
O que é SQL?
Structured Query Language, ou Linguagem de Consulta
Estruturada ou SQL, é uma linguagem de pesquisa declarativa
para banco de dados relacional.
Muitas das características originais do SQL foram inspiradas na
álgebra relacional
Foi desenvolvido originalmente no início dos anos 70 nos
laboratórios da IBM.
Tinha por objetivo demonstrar a viabilidade da implementação
do modelo relacional proposto por Edgar Codd.
Seu nome original era SEQUEL, acrônimo para "Structured
English Query Language"
2
SQL (principais características)
A linguagem SQL é um grande padrão de banco de dados.
Por ser uma linguagem declarativa (não procedural), uma consulta
SQL especifica a forma do resultado e não o caminho para chegar a
ele.
Apesar de ser originalmente criada pela IBM, muitos
desenvolvedores foram criando "dialetos" para ela. Essa expansão
levou à necessidade de ser criado e adaptado um padrão para a
linguagem.
Em 1986/87 a linguagem SQL foi padronizada pela ANSI e ISO
sendo revisada nos anos de 1992, 1999 e 2003.
Normalmente a linguagem pode ser aportada de plataforma
para plataforma sem mudanças significativas em sua estrutura.
3
Estrutura da linguagem SQL
A linguagem SQL é dividida em subconjuntos de acordo com as
operações que se deseja efetuar sobre um banco de dados. Os
principais subconjuntos são:
DDL - Data Definition Language (Linguagem de Definição de Dados)
Principais comandos: CREATE, ALTER e DROP
DML - Data Manipulation Language (Linguagem de Manipulação de Dados)
Principais comandos: SELECT, INSERT, UPDATE, DELETE, TRUNCATE e outros.
DCL - Data Control Language (Linguagem de Controle de Dados)
Principais comandos: GRANT, REVOKE e SET.
4
DDL Linguagem de Definição de Dados
O conjunto de comandos da linguagem DDL é usado para a
definição das estruturas de dados, fornecendo as instruções
que permitem a criação, modificação e remoção de objetos
de banco de dados (base de dados, esquemas, tabelas,
índices etc.).
A maioria dos bancos de dados comerciais tem extensões
proprietárias no DDL.
Os comandos básicos da DDL são:
■ CREATE: cria um objeto (uma Tabela, por exemplo) dentro da base de dados.
■ DROP: apaga um objeto do banco de dados.
■ ALTER: permite ao usuário alterar um objeto, por exemplo, adicionando uma coluna a
uma tabela existente.
5
DML Linguagem de Manipulação de Dados
É o grupo de comandos dentro da linguagem SQL utilizado para a
recuperação, inclusão, remoção e modificação de informações
em bancos de dados.
Os comandos básicos da DML são:
■ SELECT: permite ao usuário especificar uma consulta ("query") como uma descrição do
resultado desejado.
■ INSERT é usada para inserir um registro (formalmente uma tupla) a uma tabela
existente.
■ UPDATE para mudar os valores de dados em uma ou mais linhas da tabela existente.
■ DELETE permite remover linhas existentes de uma tabela.
■ TRUNCATE: remove rapidamente todas as linhas da tabela, esvaziando-a.
■ COMMIT: efetiva a transação atualmente executada.
■ ROLLBACK:desfaz a transação corrente, fazendo com que todas as modificações
realizadas pela transação sejam rejeitadas.
6
DCL Linguagem de Controle de Dados
É o grupo de comandos que permitem ao administrador de banco
de dados gerenciar os aspectos de autorização de dados e
licenças de usuários para controlar quem tem acesso para ver ou
manipular dados dentro do banco de dados.
Alguns exemplos de comandos DCL são:
■ GRANT: concede privilégios a um ou mais usuários para acessar ou realizar determinadas
operações em um objetos de dados.
■ REVOKE: revoga (remove) ou restringe a capacidade de um usuário de executar
operações.
■ SET: Define parâmetros em tempo de execução, como por exemplo, o tipo de
codificação do cliente e o estilo de representação de data e hora.
■ LOCK: Bloqueia explicitamente uma tabela fazendo o controle de acessos concorrente.
7
BANCO DE DADOS WEB
AULA 4
criação de papéis (Roles): usuários e grupos
professor Luciano Roberto Rocha
www.lrocha.com
Criação de Usuários
CREATE USER: cria uma conta de usuário do banco de dados
Descrição
Adiciona um novo usuário ao agrupamento de bancos de dados do PostgreSQL.
Apenas os superusuários do banco de dados podem usar este comando.
Sinopse
CREATE USER nome [ [ WITH ] opção [ ... ] ]
onde opção pode ser:
|
|
|
|
|
CREATEDB | NOCREATEDB
CREATEUSER | NOCREATEUSER
IN GROUP nome_do_grupo [, ...]
[ ENCRYPTED | UNENCRYPTED ] PASSWORD 'senha'
VALID UNTIL 'data_e_hora'
9
Criação de Usuários
Parâmetros
nome
O nome do usuário.
CREATEDB - NOCREATEDB
Estas cláusulas definem a permissão para o usuário criar banco de dados. Se for
especificado CREATEDB, o usuário sendo definido terá permissão para criar seus
próprios bancos de dados. Se for especificado NOCREATEDB, nega-se ao usuário a
permissão para criar banco de dados. Se nenhuma destas cláusulas for especificada, o
padrão é NOCREATEDB.
CREATEUSER - NOCREATEUSER
Estas cláusulas determinam se o usuário pode ou não criar novos usuários.
CREATEUSER também torna o usuário um superusuário, o qual pode passar por cima
de todas as restrições de acesso. Se nenhuma destas cláusulas for especificada, o
padrão é NOCREATEUSER.
10
Criação de Usuários
Parâmetros
nome_do_grupo
O nome de um grupo existente onde o usuário será incluído como um novo membro.
Podem ser especificados nomes de vários grupos.
senha
Define a senha do usuário. Esta opção pode ser omitida, mas o usuário não poderá
mais se conectar se for decidido mudar para autenticação por senha. A senha poderá
ser definida ou mudada posteriormente através do comando ALTER USER.
ENCRYPTED - UNENCRYPTED
Estas cláusulas controlam se a senha será armazenada criptografada, ou não, nos
catálogos do sistema; Se não for especificada nenhuma destas cláusulas, o
comportamento padrão será determinado pelo parâmetro de configuração
password_encryption.
data_e_hora
A cláusula VALID UNTIL define uma data e hora após a qual a senha do usuário não é
mais válida. Se esta cláusula for omitida, a conta será válida para sempre.
11
Criação de Usuários
Exemplos
Criar um usuário sem senha:
CREATE USER jonas;
Criar um usuário com senha no grupo desenvolvedores:
CREATE USER manuel PASSWORD 'jw8s0F4' IN GROUP desenvolvedores;
Criar um usuário com uma senha válida até o fim de 2009. Após o primeiro segundo de
2010 a senha não será mais válida.
CREATE USER miriam PASSWORD 'jw8s0F4' VALID UNTIL '2010-01-01';
Criar uma conta onde o usuário pode criar bancos de dados:
CREATE USER manuel PASSWORD 'jw8s0F4' CREATEDB;
Criar um superusuário:
CREATE USER “Altair” CREATEUSER CREATEDB PASSWORD 'jw8s0F4';
12
Alteração de Usuários
ALTER USER: altera uma conta de usuário do banco de dados
Descrição
Altera os atributos de uma conta de usuário do PostgreSQL. Os atributos não
mencionados no comando permanecem com suas definições anteriores.
Somente um superusuário do banco de dados pode alterar qualquer uma destas
configurações para qualquer usuário. Os usuários comuns podem apenas alterar suas
próprias senhas.
Sinopse
ALTER USER nome [ [ WITH ] opção [ ... ] ]
ALTER USER nome RENAME TO novo_nome
ALTER USER nome SET parâmetro { TO | = } { valor | DEFAULT }
ALTER USER nome RESET parâmetro
13
Alteração de Usuários
Exemplos
Mudar a senha do usuário:
ALTER USER marcos PASSWORD 'hu8jmn3';
Mudar a data de expiração da senha do usuário:
ALTER USER manuel VALID UNTIL 'Jan 31 2030';
Mudar a data de expiração da senha, especificando que a senha expira ao meio dia de 4
de maio de 2010:
ALTER USER cristiane VALID UNTIL 'May 4 2010 12:00:00';
Tornar o usuário válido para sempre:
ALTER USER andrea VALID UNTIL 'infinity';
Dar ao usuário permissão para criar outros usuários e novos bancos de dados:
ALTER USER luizete CREATEUSER CREATEDB;
14
Exclusão de Usuários
DROP USER: remove uma conta de usuário do banco de dados
Descrição
O comando DROP USER remove o usuário especificado. Não remove as tabelas, visões
ou outros objetos pertencentes ao usuário. Se o usuário possuir algum banco de dados
uma mensagem de erro é gerada.
Sinopse
DROP USER nome
Parâmetro
nome
O nome do usuário a ser removido.
15
Exclusão de Usuários
Observações
O PostgreSQL inclui o aplicativo dropuser que possui a mesma funcionalidade deste
comando (na verdade, chama este comando), mas que pode ser executada a partir da
linha de comandos.
Para remover um usuário que possui um banco de dados, primeiro o banco de dados deve
ser removido ou mudado de dono.
Não se aconselha remover um usuário que possua algum objeto de banco de dados, ou
que tenha permissões concedidas para objetos. Atualmente isto só é verificado no caso
dos donos de bancos de dados, mas é provável que as versões futuras do PostgreSQL
verifiquem os outros casos.
Exemplos
Para remover uma conta de usuário:
DROP USER marcos;
16
Criação de Grupos
CREATE GROUP: cria um grupo de usuário do banco de dados
Descrição
O comando CREATE GROUP cria um grupo de usuários. É necessário ser um
superusuário do banco de dados para executar este comando.
Deve ser observado que tanto os usuários quanto os grupos são definidos no nível de
agrupamento de bancos de dados e, portanto, são válidos em todos os bancos de dados
do agrupamento.
Sinopse
CREATE GROUP nome [ [ WITH ] opção [ ... ] ]
onde opção pode ser:
| USER
nome_do_usuário [, ...]
17
Criação de Grupos
Parâmetros
nome
O nome do grupo.
nome_do_usuário
A lista dos usuários a serem incluídos no grupo. Os usuários devem existir.
Exemplos
Criar um grupo vazio:
CREATE GROUP colaboradores;
Criar um grupo com membros:
CREATE GROUP vendas WITH USER jonas, marcela;
obs: Não existe o comando CREATE GROUP no padrão SQL. O conceito de “papéis” (roles) é semelhante
ao de grupos.
18
Alteração de Grupos
ALTER GROUP: altera um grupo de usuários
Descrição
O comando ALTER GROUP altera os atributos de um grupo de usuários.
As duas primeiras variantes abaixo adicionam ou removem usuários de um grupo,
respectivamente. A terceira forma muda o nome do grupo.
Somente os superusuários do banco de dados podem utilizar este comando.
Para conhecer o conjunto de grupos existentes, deve ser consultada a
t a b e l a d o s i s t e m a “ p g _ g r o u p ” c o m o s e g u i n t e c o m a n d o : SELECT groname FROM pg_group; Sinopse
ALTER GROUP nome_do_grupo ADD USER nome_do_usuário [, ... ]
ALTER GROUP nome_do_grupo DROP USER nome_do_usuário [, ... ]
ALTER GROUP nome_do_grupo RENAME TO novo_nome
19
Alteração de Grupos
Parâmetros
nome_do_grupo
O nome do grupo a ser modificado.
nome_do_usuário
Os usuários a serem adicionados ou removidos do grupo. Os usuários devem existir; o
comando ALTER GROUP não cria nem remove usuários.
novo_nome
O novo nome do grupo.
Exemplos
Adicionar usuários a um grupo:
ALTER GROUP arquitetura ADD USER joana, alberto;
Remover um usuário de um grupo:
ALTER GROUP engenharia DROP USER margarida;
20
Exclusão de Grupos
DROP GROUP: remove um grupo de usuários
Descrição
O comando DROP GROUP remove um grupo de usuários especificado.
Os usuários que fazem parte do grupo não são removidos.
Somente os superusuários do banco de dados podem utilizar este comando.
Sinopse
DROP GROUP nome
21
Exclusão de Grupos
Parâmetros
nome_do_grupo
O nome do grupo a ser removido.
Exemplos
Para remover um grupo:
DROP GROUP arquitetura;
Obs.: Não se aconselha remover um grupo que tenha permissões concedidas para
objetos. Atualmente isto não é exigido, mas é provável que as versões futuras do
PostgreSQL verifiquem este erro.
22