23.03.21MySQL 사용자, 뷰

목차

1. MySQL 사용자

• 사용자 추가하기 
• 사용자 권한 부여하기
• 사용자 삭제하기
• 사용자 목록 조회
• 사용자 권한 조회 
• 사용자 권한 삭제하기

2. MySQL 뷰

• 뷰
• 뷰를 만드는 이유
• 뷰 생성법
• 뷰 수정하기
• 뷰 대체하기
• 뷰 삭제하기
• 뷰에서 테이블안의 데이터 수정 해보기

---

파일명 : day4.sql

1. MySQL 사용자

1-1.사용자 추가하기  

 

• MySQL 8.0 Command Line Client 'root'계정으로 로그인
• 로컬에서 접속 가능한 사용자 추가하기

사용 문법 : create user '사용자명'@'localhost' identified by '사용자 비밀번호';

#create user '사용자명'@'localhost' identified by '사용자 비밀번호';
create user 'apple'@'localhost' identified by '1111'

1-2.사용자 권한 부여하기

사용 문법 : grant all privileges on *.* to '사용자명'@'localhost'; #모든 DB에 접근 가능한 권한 주기

grant all privileges on *.* to '사용자명'@'localhost'; #모든 DB에 접근 가능한 권한 주기 
grant all privileges on 데이터베이스명.* to '사용자명'@'localhost'; #데이터베이스명의 모든 권한 부여
flush privileges; # 새로운 셋팅을 적용하겠다.(즉시적용 !)

• ✔할당 권한 상세 옵션
  • create, drop, alter: 테이블에 대한 생성,삭제,변경 권한
  • select, insert, update, delete :테이블의 데이터를 조회, 삽입, 변경, 삭제에 대한 권한
  • all(*) :모든권한
  • usage: 권한을 부여하지 않고 계정만 생성 (디폴트값)
    • 예) grant select on 데이터베이스명.테이블명 to '사용자명'@'localhost';

          # 다른 권한을 주고 싶을때는 ,(쉼표)로 이어붙여넣어주면 가능하다.

---

• ✔IP 권한 상세 옵션
  • % : 모든 IP에서 접근이 가능
  • 127.0.0.1 : localhost에서 접근이 가능 (자기 컴퓨터에서만 접근 가능)
  • 예)grant select on 데이터베이스명.테이블명 to '사용자명'@'%'; #모든 IP에서 접근 가능
  • grant select on 데이터베이스명.테이블명 to '사용자명'@'특정 ip주소'; #특정 ip에서 접근 가능

1-3.사용자 삭제하기

사용 문법 :drop user 계정명;  ✔추천 !!
                   delete from user where user = 계정명;

 

1-4.사용자 목록 조회

사용 문법 : select user, host from user;

select user, host from user;

1-5.사용자 권한 조회 

사용 문법 : show grant for '계정명'@'localhost';

show grants for 'apple'@'localhost';
show grants for 'orange'@'localhost';

1-6.사용자 권한 삭제하기

사용 문법 :  revoke 권한명 privileges on 데이터베이스명.테이블명 from '계정명'@'localhost';

revoke all privileges on apple.* from 'apple'@'localhost';

---

2. MySQL 뷰

2-1.뷰 (View)

• 가상의 테이블을 생성합니다.
• 실제 테이블처럼 행과 열을 가지고 있지만, 데이터를 직접 저장하고 있지는 않음
• 테이블의 형태는 가지고 있어야한다.

2-2.뷰를 만드는 이유

• 자주 사용하는 SQL 코드를 간결하게 만들기 위해 
• 삽입,삭제,수정 작업에 제한 사항을 가지게 해줍니다.
• 내부 데이터를 전체 공개하고 싶지 않을 때 사용

2-3.뷰 생성법

사용 문법 : create view 뷰이름 as 쿼리들 작성 .....

#현재 내가 가지고 있는 테이블을 우선 확인 해줍니다. 
select * from member;

#내용(userid, username, hp, gender)만 member테이블에서 출력 해주고 싶은 뷰 만들어보기
select userid, username, hp, gender from member;

#실질적인 뷰(view)생성 뷰의 이름은 vw_member
create view vw_member as select userid, username, hp, gender from member;
# 생성된 뷰로 내용보기 
select *from vw_member;

문제 )

member의 userid, username, hp와 profile의 mbti를 출력하는 뷰(vw_memberprofile)를 만들고 select만 할 수 있는 melon 계정 생성. (member테이블과 profile테이블은 이미 만들어져 있는 상태 입니다.)

<위 member테이블> ,<아래 profile테이블>

###view 만들기 ###
#view생성전 원하는 쿼리 생성 
select m.userid, m.username, m.hp, p.mbti from member as m inner join profile as p on m.userid = p.userid;

# 위에서 만든 원하는 쿼리를 그대로 뒤에 붙혀 넣어준다. 
create view vw_memberprofile as select m.userid, m.username, m.hp, p.mbti from member as m inner join profile as p on m.userid = p.userid;
#view가 잘만들어졌는지 확인
select * from vw_memberprofile;

###melon아이디 생성후 권한 부여 ###
# melon아이디 생성
create user 'melon'@'localhost' identified by '1234';
# melon아이디 생성 확인
use mysql;
select user, host from user;

#멜론 권한 확인하기 
show grants for 'melon'@'localhost'; 

#멜론한테 권한주기
grant select on kdt.vw_memberprofile to 'melon'@'localhost';  
flush privileges;# 셋팅값 즉시적용

2-4.뷰 수정하기

사용 문법 : alter view 뷰이름 as 쿼리 .... 

2-5.뷰 대체하기

사용 문법 :  create or replace view 뷰이름 as 쿼리 .... 

2-6.뷰 삭제하기

사용 문법:drop view 뷰이름;