FVKjdm3R

1delimiter ;
2drop database trabalhobd;
3create database IF NOT EXISTS trabalhobd;
4
5use trabalhobd;
6
7create table Cargos(
8id int unsigned not null auto_increment,
9nome varchar(20) not null,
10primary key(id))
11ENGINE = InnoDB;
12
13insert into Cargos (nome) values ("atendente"), ("vendedor");
14
15create table Usuarios(
16id int unsigned not null auto_increment,
17nome varchar(30) not null,
18login varchar(20) not null,
19senha varchar(20) not null,
20cargo_id int unsigned not null,
21primary key(id),
22foreign key(cargo_id) references Cargos(id))
23ENGINE = InnoDB;
24
25insert into Usuarios (nome,login,senha,cargo_id) values ("Gilson Atendente","gilson","1234",1);
26insert into Usuarios (nome,login,senha,cargo_id) values ("Gilson Vendedor","gilson2","1234",2);
27
28create table Clientes (
29id int unsigned not null auto_increment,
30cpf varchar(30) not null,
31nome varchar(30),
32endereco varchar(30),
33telefone varchar(20),
34idUsuario int unsigned,
35dataTransacao datetime,
36status varchar(20),
37primary key(id))
38ENGINE = InnoDB;
39
40create or replace view clientesIncluidos as
41select * from Clientes where status = "Incluido";
42
43DROP TRIGGER IF EXISTS insereDataTransacao;
44CREATE TRIGGER insereDataTransacao BEFORE INSERT ON Clientes
45	FOR EACH ROW SET NEW.status = 'Incluido', NEW.dataTransacao = NOW();
46  
47delimiter |
48  
49drop procedure if exists alteraCliente|
50CREATE PROCEDURE alteraCliente(idaux int, novoNome varchar(30), novoCpf varchar(30), novoEndereco varchar(30), novoTelefone varchar(20),idUsuarioAux int unsigned)
51begin
52	declare idAux2 int unsigned;
53	
54	select id into idAux2 from clientesIncluidos ci where ci.cpf = novoCpf and id != idaux;
55	
56	if (idAux2 IS NULL) then
57	update Clientes set status = "Alterado" where id = idaux;
58		insert into clientesIncluidos(nome,cpf,endereco,telefone,idUsuario) values (novoNome,novoCpf,novoEndereco,novoTelefone,idUsuarioAux);
59	end if;	
60end
61|
62
63drop procedure if exists auditarCliente|
64CREATE PROCEDURE auditarCliente(cpfAux int unsigned)
65begin
66	update clientes set status = "Auditado" where cpf = cpfAux;
67end
68|
69
70drop procedure if exists insereCliente|
71CREATE PROCEDURE insereCliente(nomeAux varchar(30), cpfAux varchar(30), enderecoAux varchar(30), telefoneAux varchar(20),idUsuarioAux int unsigned)
72begin
73	declare idAux int unsigned;
74	
75	select id into idAux from clientesIncluidos ci where ci.cpf = cpfAux;
76	
77	if (idAux IS NULL) then
78		insert into clientesIncluidos(nome,cpf,endereco,telefone,idUsuario) values (nomeAux,cpfAux,enderecoAux,telefoneAux,idUsuarioAux);
79	end if;
80end
81|
82
83delimiter ;
84
85DROP USER 'vendedorLoja'@'localhost';
86Create user 'vendedorLoja'@'localhost';
87GRANT SELECT ON trabalhobd.clientesIncluidos to 'vendedorLoja'@'localhost' IDENTIFIED BY 'senha';
88SHOW GRANTS FOR 'vendedorLoja'@'localhost';
89
90DROP USER 'dbaLoja'@'localhost';
91Create user 'dbaLoja'@'localhost' IDENTIFIED BY 'senha';
92GRANT ALL PRIVILEGES ON trabalhobd.* to 'dbaLoja'@'localhost';
93SHOW GRANTS FOR 'dbaLoja'@'localhost';
94
95
96DROP USER 'atendenteLoja'@'localhost';
97Create user 'atendenteLoja'@'localhost';
98GRANT INSERT, UPDATE, SELECT ON trabalhobd.clientesIncluidos to 'atendenteLoja'@'localhost' IDENTIFIED BY 'senha';
99GRANT EXECUTE on PROCEDURE trabalhobd.alteraCliente to 'atendenteLoja'@'localhost';
100GRANT EXECUTE on PROCEDURE trabalhobd.insereCliente to 'atendenteLoja'@'localhost';
101
102
103SELECT User, Host FROM mysql.user;