(7)MySQL_기본문법

MySQL 사용자

1. 사용자 추가하기

• MySQL 8.0 Command Line Client 'root' 계정으로 로그인
• 로컬에서 접속 가능한 사용자 추가하기
• create user '사용자명'@'localhost' identified by '사용자 비밀번호';
• create user 'apple'@'localhost' identified by '1111';
• DB 권한 부여하기
• grant all privilenges on *.* to '사용자'@'localhost'; #모든 DB에 접근 가능
• grant all privilenges on 데이터베이스명.* to '사용자'@'localhost';
• flush privileges; # 새로운 세팅을 적용함

✔ 할당 권한 상세 옵션

• create, drop, alter : 테이블에 대한 생성, 삭제, 변경, 권한
• select, insert, update, delete: 테이블의 데이터를 조회, 삽입, 변경, 삭제에 대한 권한
• all: 모든 권한
• usage: 권한을 부여하지 않고 계정만 생성
• 예) grant select on 데이터베이스명.테이블명 to '사용자'@'localhost';
  다른 권한도 주고싶다면 select 옆에 , 로 이어서 쓰면된다.

✔ IP 권한 상세 옵션

• %: 모든 IP에서 접근이 가능
• 127.0.0.1: localhost에서 접근이 가능member
• 예) grant select on 데이터베이스명.테이블명 to '사용자'@'%'
• 예) grant select on 데이터베이스명.테이블명 to '사용자'@'특정 IP주소'

2. 사용자 계정 삭제하기

• drop user '사용자명' @ 'localhost';

orange 계정을 만들어 apple 데이터베이스에 test.member 테이블을 복사하고 해당 테이블의 select 권한만 가능한 orange 계정을 만들어보자.

create table apple.member like test.member; # 테이터베이스 만들기
insert into apple.member select * from test.member; # 데이터베이스 복사
create user 'orange'@'localhost' identified by '1111'; # orange 계정 만들기
grant select on apple.member to 'orange'@'localhost'; # 권한부여
flush privileges; # 완료

 

orange로 로그인 후 테스트

use apple;
select * from member;

update member set point = 500 where userid = 'berry'; # update는 권한을 주지 않아 오류가 나온다.

 

사용자 목록 조회

use mysql;
select user, host from user;

사용자 권한 조회하기

use mysql;
select user, host from user;

사용자 제거

drop user 계정명; # 추천!
delete from user where user = 계정명;

사용자 권한 조회하기

• show grants for '계정명'@'localhost'

show grants for 'apple'@'localhost';

show grants for 'orange'@'localhost';

• apple의 member table에 select 권한을 주어서 추가된 것을 볼 수 있다.

사용자 권한 제거하기

• revoke 권한명 privileges on 데이터베이스명.테이블명 from 계정명 '계정명'@'localhost';

revoke all privileges on apple.member from 'orange'@'localhost'; # orange계정의 member에 대한 권한 제거

뷰(view)

• 가상의 테이블을 생성
• 실제 테이블처럼 행과 열을 가지고 있지만, 데이터를 직접 저장하고 있지는 않음

뷰를 만드는 이유

• SQL 코드를 간결하기 만들기 위함
• 삽입, 삭제, 수정, 작업에 제한 사항을 가짐
• 내부 데이터를 전체 공개하고 싶지 않을 때

create view 뷰이름 as 쿼리 ...

select * from member;
select userid, username, hp , gender from member;
create view vw_member as select userid, username, hp, gender from member;
select * from vw_member;

select * from profile;

member의 userid, username, hp와 profile의 mbti를 출력하는 뷰(vw_memberprifile)를 만들고 select만 할 수 있는 melon 계정을 생성

select m.userid, m.username, m.hp, p.mbti
 from member m join profile p on m.userid = p.userid;

create view vw_memberprifile as select m.userid, m.username, m.hp, p.mbti
 from member m join profile p on m.userid = p.userid;
select * from vw_memberprifile;

create user 'melon'@'localhost' identified by '1111';
grant select on test.vw_memberprifile to 'melon'@'localhost';
flush privileges;
show grants for 'melon'@'localhost';

melon으로 접속 후 테스트

select * from member; # 에러
select * from vw_memberprifile;

뷰 수정하기

• alter view 뷰이름 as 쿼리...

뷰 대체

• create or replace view 뷰이름 as 쿼리 ...

create or replace view vw_memberprifile as select m.userid, m.username, m.hp, p.mbti
 from member m join profile p on m.userid = p.userid;

뷰 삭제하기

drop view vw_member;
select * from vw_member; # 삭제를 해서 오류가 나온다.

뷰 수정하기

• update를 이용해 수정
• 뷰의 내용을 수정하면 본 테이블의 내용도 같이 수정됨
• 본 테이블에 제약조건에 맞지 않으면 수정이 불가능 하다.