[MYSQL / Database] proceduer/view/role 실습2

연습문제1 조건

create database db01;
use db01;

create table persons(
	personID int primary key,
    lastname varchar(30),
    firstname varchar(30),
    age int);
    desc persons;
    
    select * from information_schema.table_constraints where table_name='persons';
    
alter table persons drop primary key;
alter table persons add constraint personID_pk primary key(personID);

insert into persons values(1,'Hansen','ola',30),(2,'Svedson','Tove',23),(3,'Pettersen','Kari',20);

create table orders(
orderid int,
orderNumber int unique,
personid int,
constraint personid_p_o_fk foreign key(personid) references persons(personid));

insert into orders values ( 1,77895,3),(2,44678,3),(3,22456,2),(4,24562,1);

select * from persons;
select * from orders;

create role v1;
create role p1;

create view v1 as select personid from persons;

 delimiter $$
 create procedure p1()
 begin
	select orderid from orders;
end $$
delimiter ;

call p1();
select * from v1;

grant select on db01.v1 to v1;
grant execute on procedure db01.p1 to p1;

create user user1@'%' identified by '1234';
create user user2@'%' identified by '1234';

grant v1 to user1@'%';
grant p1 to user2@'%';