SQL-DCL ( linguagem de Controle de Dados)

LABORATÓRIO DE BANCO DE DADOS
DCL – LINGUAGEM DE CONTROLE DE DADOS
A DCL – Linguagem de controle de Dados é a sub-linguagem da SQL que disponibiliza
comandos para controlar o acesso a dados em um ambiente de SGBD.
Nas descrições abaixo as palavras entre colchetes representam parâmetros opcionais
1 - CREATE USER
Este comando permite criar um usuário para que este tenha acesso ao SGBD. No postgres
este comando tem o mesmo efeito de create role ( comando que será vista abaixo) nas versões
recentes de SGBD não existe mais o "user" , somente o "role" . Ao executar o comando "create
user" o postegres cria um "role".
Sintaxe:
Create user nom_usuario [ WITH opções]
As opções variam de SGBD para SGBD
Exemplo no postgres:
CREATE USER sidney WITH PASSWORD 'abcd'
Cria o usuario sidney com senha abcd
CREATE USER user_sidney WITH LOGIN PASSWORD 'abbcd' VALID UNTIL '2008-01-01'
Cria o usuario sidney que possui permissão de efetuar login com senha abcd , valido até
'2008-01-01'
CREATE USER sidney WITH PASSWORD 'abcd' CREATEDB
Cria o usuario sidney com senha abcd , que tem permissão para criar base de dados
CREATE USER sidney WITH PASSWORD 'abcd' CREATEUSER
Cria o usuario sidney com senha abcd , que tem permissão para criar usuários
CREATE USER sidney WITH PASSWORD 'abcd' SUPERUSER
Cria o usuario sidney com senha abcd , que tem poderes de superusuario
2 - CREATE VIEW
Este comando permite a criação de uma visão que envolve dados de uma ou mais tabelas
do banco de dados. A visão passa a ser tratado, pelo usuário final como uma tabela, porém ela
não retem dados ela obtem os dados no momento da consulta com o select descrito na criação da
visão
Sintaxe:
CREATE VIEW nome_visao [( nome_do_campo [, ...] ) ]
Nota de Aula
Prof. Sidney Vieira
1
AS SELECT Tabi.campo1, Tabk.campo2, ... Tabj.campon
FROM Tabi, Tabk , Tabj
[WHERE <condições>];
Obs: Os nomes dos campos na visão são opcionais caso queira que os campos da visão tenha m
os mesmos nomes das respectivas tabelas basta omitir a declaração.
exemplo:
Criação de visão sem definição de campos
CREATE VIEW func_dep
AS SELECT f.nome_func, d.nome
FROM funcionario f, departamento d
WHERE f.cod_depto = d.cod_depto;
Criação de visão com definição de campos
CREATE VIEW func_dep(nome, departamento)
AS SELECT f.nome_func, d.nome
FROM funcionario f, departamento d
WHERE f.cod_depto = d.cod_depto;
Para usar visão basta fazer o select tratando a visão como uma tabela
select na visão sem definição de campos
select na visão com definição de campos
select * from func_dep
select * from func_dep
select f.nome_fun
from func_dep
select nome
from func_dep
3 - GRANT
O comando GRANT f tem por objetivo permite que o proprietário( usuário que cria a base
de dados por meio do comando CREATE DATABASE) de uma base de dados dê acesso a base
a outros usuários.
Tipos de concessão de acessos:
• manipulação de tabelas e visões
• execução de trigger e stored procedure
Manipulação de tabelas e visões
Sintaxe:
GRANT privilegio1, …, privilegion on tablename1 , …, tablenamen to user1 , user2…, usern
Os privilégios são: select, insert, delete, update [col,col..], references e all
Update é o privilégio para atualizar uma tabela que pode, opcionalmente, ser restringido para
uma ou mais colunas da tabela.
Nota de Aula
Prof. Sidney Vieira
2
references é o privilégio que permite definir numa outra tabela, uma chave estrangeira para
a Chave Primária da tabela em questão.
all – representa todos os 5 privilégios acima
Os users descrevem um ou usuários do SGBD. Se é usado public significa: todos os usuários
do sistema SGBD.
Exemplos:
grant all on funcionario to public
(todos os usuarios cadastrados no SGBD possuem os 5 privilégios)
grant delete on funcionario to user23
(o usuário user23 possui privilegio para eliminar linhas da tabela projeto)
grant update nome_fun on funcionario to Sidney
(o usuário Sidney pode atualizar a coluna nome_fun da tabela funcionario)
grant select, insert, update, delete on departamento user12;
grant references on funcionario to user22
(O usuário user22 pode definir Chave Estrangeira em sua(s) tabelas(s) referenciando a
Chave Primária de Funcionario)
Execução de procedure e trigger
Para que usuários possam executar uma stored procedure o seu criador deve conceder o
privilégio de execução dessa stored procedure a esses usuarios.deevndo para isto dar a
instrução:
grant execute on procedure1, ... , proceduren to user1 , user2.,..., usern
Para que outras stored procedures ou triggers possam chamar a stored procedure procname o
criador de procname deve dar a instrução:
grant execute on procname to otherprocname(s) | triggername(s)
Para permitir que uma stored procedure ou trigger possa executar operações de
acesso/atualização sobre uma tabela o criador da tabela ou o criador da stored procedure, deve
dar a instrução:
grant privilegio1, … , privilegion on tablename to procedure nome_procedure
4 - ROLE
O comando ROLE faz parte da DCL - DATA CONTROL LANGUAGE, uma sub-linguagem
da DML da SQL.
Role representa um papel que usuários podem assumir no acesso ao SGBD. Este papel é
criado atribuindo um conjunto de privilégio(s) para uma ou mais tabelas ou visões e deve ser
concedido,via comando Grant, a um ou mais usuários.
Etapas para criação de um ROLE:
1.Create role nome_role
2.Grant privilegio(s) on tabela(s) to nome_role
3.Grant nome_role(s) to user(s)
Nota de Aula
Prof. Sidney Vieira
3
Obs:
Usuário ao se conectar a uma base de dados pode especificar o rolename desejado
EX:
Create role gerente
Grant select, insert on funcionario, departamento to gerente
Grant gerente to sidney, joao, pedro
5 - REVOKE
o comando Revogação de privilégios, revoga privilégios fornecidos por meio do comando Grant
e feita pelo comando revoke.
Sintaxe:
REVOKE privilegio1, …, privilegion on tablename1 , …, tablenamen from user1 , user2…,
usern
Exemplo:
revoke delete on projeto from user23
Obs:
neste caso o comando: Grant delete on projeto to user23 deve ter sido dado
anteriormente.
Restrição sobre revogação de privilégios:
•
Apenas o usuário que concede privilégio(s) (via grant ) pode revogá-lo(s) de um ou
mais usuários.
Nota de Aula
Prof. Sidney Vieira
4