[더 알아가기]MySQL관리를 위한 SQL이해

🦴 MYSQL 사용자 확인, 추가, 비밀번호 변경, 삭제

SQL DCL이란 무엇인가? Mysql의 사용자를 확인하거나 추가, 비밀번호 변경, 삭제하는것 처럼 mysql에 데이터를 명령들을 모아놓은 명령어이다. 그 중에서 DB는 여러 사람들이 접속해서 각 사용자별로 ID를 만들어놓는 경우도 있고, 비밀번호를 바꾸거나 이런 작업을 할 필요가 있을 때가 있는데 그런 작업을 어떻게 하는지 설명한다.

 

터미널로 프로그래밍 해보기 

※ 윈도우 검색 → mysql command line -unicode 검색 후 클릭

1. MYSQL 사용자 확인

mysql데이터베이스 프로그램 안에는 mysql이라는 이름을 가진 DB가 파일로 있다.

이렇게 많은 테이블 중에서 USER라는 테이블이 있는데,

이처럼 사용자 정보도 테이블로 관리하고 있다.

2. 사용자 추가

• MAC환경

CREATE USER [userid]@localhost    # 이렇게 하면 userid라는 사용자로 mysql프로그램에 접속을 할 때 

localhost를 하면 내 컴퓨터에서만 접속을 가능하게 하도록 한다. 내 컴퓨터에서 mysql프로그램에 접속을 할때

CREATE USER [userid]@localhost  identified by 'password' ;

 

CREATE USER [userid]@'%'   identified by 'password';   #어떤 컴퓨터에서나 userid로 접속을 허용하겠다고 하면 '%'를 쓰면 된다.

 

• WINDOW환경

제어판 > 시스템 > 고급 시스템 환경 설정 > 시스템 속성 > 고급 > 환경변수 

파일 탐색기 > Program File > MySQL > MySQL Server 8.0 > bin 으로 가서 경로복사해서 

이렇게 설정해주면 cmd에서도 mysql을 사용할 수 있다.

성공!!!

이제 root환경이 아니라 사용자 환경에서도 MYSQL터미널로 들어갈 수 있다. 

 

1) 로컬에서만 접속 가능한 userid 생성

CREAT USER 'userid'@localhost identified by '비밀번호';

 

2) 모든 호스트에서 접속 가능한 userid 생성

CREAT USER 'userid'@'%' identified by '비밀번호';

 

3. 사용자비밀번호 변경

SET PASSWORD FOR 'useri'@'%' = '신규비밀번호';

현재 신규비밀번호 "funcoding1"

 

4. 사용자 삭제

user mysql;

drop user 'userid' @ '%';

 

 

mysql에서 id는 → 'userid' @ '%' 여기까지가 id이다.

 

 

🦴 MySQL 접속 허용 관련 설정

권한을 설정하는 이유

여러가지 DB를 만드는데, 이 DB는 많은 사람들이 접속한다. 회사의 경우 다양한 부서들이 접속을 할텐데, 영업부가 DB에 어떤 데이터를 삭제 수정한다든지 또는 개발자가 수정한다든지  하면 큰일날 것이다. 그래서 DB에 접속하는여러 ID를 만들고 그 ID마다 어떤 DB는 삭제는 못하게 하고, 어떤 DB는 수정만 하게 한다든지 이런 권한 설정을 할 수 있다.

그 권한 설정도 SQL DCL의 일부이다.   * DCL(Data control language)

 

1. 현재 부여된 권한 확인하기

어떤 아이디로 어떤 권한이 들어가있는지 확인

이처럼 지금 부여된 권한을 표시하게 된다.

 

 

2. 로컬에서만 접속 허용

어떤 데이터베이스의 어떤 테이블에 모든 권한을 주겠다.

> GRANT ALL ON [DB명].[테이블] TO 'userid'@'%';

 

어떤 데이터베이스의 모든 테이블에 모든 권한을 주겠다.

> GRANT ALL ON [DB명].*;

 

모든 데이터베이스의 모든 테이블에 모든 권한을 주겠다.

> GRANT ALL  ON *.*;

권한부여

권한 설정 잘 되었는가 확인

GRANT, SELECCT, INSERT 처럼 명령어가 보이는데, 이처럼 명령 단위로 권한을 부여할 수 있다.  

GRANT ALL로 썻으니 모든 SQL명령들을 기재하게 된다.

이처럼 모든 DB 모든 권한을 줌을 표현했다.

 

3. 특정 호스트에서만 접속 허용

예를 들어서 DB를 검색, 갱신, 삽입, 삭제하는 권한만 davelee에게 주고싶다면???

GRANT SELECT, UPDATE, INSERT, DELETE ON *.* TO 'davelee'@'%';

 

예를 들어서 ecommerce데이터베이스에 검색하는 권한만 davelee에게 주고싶다면???

GRANT SELECT ON ecommerce .* TO 'daveklee'@'%';

 

4. 옵션 상세

ALL - 모든권한

SELECT, UPDATE - 조회, 수정 권한 등으로 권한 제한 가능

DATABASE.TABLE - 특정 데이터베이스에 특정 테이블에만 권한을 줄 수 있다.

*.* - 모든 데이터베이스에 모든 테이블 권한을 줄 수 있다.

root - 계정명

 

 

 

정리: 1. 사용자 만들기(로컬호스트에서만 접속가능하도록) CREATE USER 'userid'@localhost identified by 'password'; 2. 사용자 만들기(모든 환경에서 접속가능하도록) CREATE USER 'userid'@'%' identified by 'password'; 3. 윈도우에서 내 환경으로 MYSQL터미널 접속하기 제어판 > 고급 시스템 설정 > 시스템 속성 > 고급 > 환경변수  C: > Program File > MySQL > MySQLServer 8.0 > bin 경로 복사 후 환경변수 path에 추가 > 확인 cmd에 mysql -u userid -p 패스워드 입력 4. 패스워드 변경  SET PASSWORD FOR 'userid'@'%' = '새로운 패스워드'; + mysql의 id는 'userid'@'%'여기까지가 아이디이다. 5. 현재 부여된 권한 확인 SHOW GRANTS FOR 아이디; 6. 로컬에서만 접속을 허용 GRANT ALL ON 데이터베이스.테이블 TO 'root'@localhost; 7. 특정권한만 허용 GRANT SELECT, UPDATE ON 데이터베이스.테이블 TO 'userid'@localhost;