Rocha 2010 23571e

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 de banco de dados gerenciar os aspectos de autorização de dados e licenças de usuários para controlar quem tem o 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 ar 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 os 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 : 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 nome [ [ WITH ] opção [ ... ] ] onde opção pode ser: | | | | |

CREATEDB | NOCREATEDB CREATE | NOCREATE IN GROUP nome_do_grupo [, ...] [ ENCRYPTED | UNENCRYPTED ] '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. CREATE - NOCREATE Estas cláusulas determinam se o usuário pode ou não criar novos usuários. CREATE também torna o usuário um superusuário, o qual pode ar por cima de todas as restrições de o. Se nenhuma destas cláusulas for especificada, o padrão é NOCREATE. 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 . 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 _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 jonas; Criar um usuário com senha no grupo desenvolvedores: CREATE manuel '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 miriam 'jw8s0F4' VALID UNTIL '2010-01-01'; Criar uma conta onde o usuário pode criar bancos de dados: CREATE manuel 'jw8s0F4' CREATEDB; Criar um superusuário: CREATE “Altair” CREATE CREATEDB 'jw8s0F4'; 12

Alteração de Usuários ALTER : 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 nome [ [ WITH ] opção [ ... ] ] ALTER nome RENAME TO novo_nome ALTER nome SET parâmetro { TO | = } { valor | DEFAULT } ALTER nome RESET parâmetro 13

Alteração de Usuários Exemplos Mudar a senha do usuário: ALTER marcos 'hu8jmn3'; Mudar a data de expiração da senha do usuário: ALTER 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 cristiane VALID UNTIL 'May 4 2010 12:00:00'; Tornar o usuário válido para sempre: ALTER andrea VALID UNTIL 'infinity'; Dar ao usuário permissão para criar outros usuários e novos bancos de dados: ALTER luizete CREATE CREATEDB; 14

Exclusão de Usuários DROP : remove uma conta de usuário do banco de dados Descrição O comando DROP 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 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 drop 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 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: |

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 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 nome_do_usuário [, ... ] ALTER GROUP nome_do_grupo DROP 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 joana, alberto; Remover um usuário de um grupo: ALTER GROUP engenharia DROP 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