우분투 데이터베이스 패스워드 변경 직접 데이터 입력 파일을 통한 데이터 입력 암호화 데이터 만들기

A 호스트에 데이터베이스가 있을 때 B 호스트에 데이터베이스가 설치되어져 있으면 원격으로 연결할 수 있다.
이런 부분이 데이터베이스의 Access 규제이다.

 

패스워드 변경

콘솔에서는 mysqladmin -u root password 'rootoor'; 해주고 
mysql> update mysql.user (mysql 데이터베이스.user 테이블)
           set authentication_string=PASSWORD('rootoor')
           where user='root'; 해주면 된다. 

 사용자 생성과 권한 주기

mysql> create user 'paul'@'localhost' identified by 'paul';
mysql> grant all privileges(OR delete, select, .... 권한) on *.*(데이터베이스_명.테이블_명) to 'paul'@'localhost' 
           identified by 'paul'; <=로컬 접속
           grant all privileges on *.* to 'paul'@'%' identified by 'paul'; <=원격접속

  사용자 삭제

mysql> drop user 'paul'@'localhost'; 삭제 
mysql> delete from mysql.user where host='192.168.100.100' and user='paul'; <=192.168.100.100 호스트의 paul
           delete from mysql.user where host='192.168.100.%' and user='paul'; <=192.168.100.0 네트워크 상의 paul

 

systemctl start mysql

systemctl enable mysql.service

ufw allow 3306/tcp

netstat -nltp |grep 3306

mysqladmin -u root password 'rootoor123'으로 지정했더니 mysql -u root -p에서 rootoor123을 입력해서 들어갔다.

 

select * from mysql.user;

 

패스워드 변경을 해보겠다.

 

이미 password policy를 low로 변경하고 캡쳐했다.

(set global validate_password_policy=LOW;)

유저 생성

=>여기서 '패스워드가 정책과 안 맞는다'고 오류를 보이면(4자리 패스워드도 그대로 사용한다면)
a) show variables like 'validata_password%'; 해서 설정을 보고 
b) set global validate_password_policy=LOW;
c) set global validate_password_length=4; 해주고 
d) flush privileges; 해서 권한을 갱신한 다음,
e) exit; 하고, 
f) 다시 콘솔에서 mysql –u root –prootoor 해서 root 사용자로 들어가 본다.

 

 

 show databases; 해서 데이터베이스들을 보고 
create database ubuntu_db; 해서 ubuntu_db라는 데이터베이스를 만들고 
use ubuntu_db; 해서 ubuntu_db를 사용한다고 하고 
show tables; 해서 이 ubuntu_db 안에 테이블이 있는지 보고 

create table ubuntu_tbl ( <= ubuntu_tbl이라는 테이블_명 지정
id int not null auto_increment,  <= id 항목(열_명), int(데이터_타입), not null(제약조건), 자동증가
first_name varchar(20),                  <= f_name, varchar(20)은 가변 문자열이고 20비트 크기
last_name varchar(20) not null,
city varchar(20),
phone varchar(15),
primary key (id)  <= 이 테이블에서 '유일하고 값이 있는' 주키가 id이다. 
        ); 해서 ubuntu_tbl이라는 테이블을 만든다.

desc ubuntu_tbl; 해서 테이블의 속성을 본다. <= 이 명령어를 실행해서 열_명을 본다. 
   select 열_명1, 열_명2, ... from 테이블_명
   where 조건;  이 SQL의 기본 구문이므로 열_명을 모르면 쿼리가 불가하다.
   ===>빅데이터 분석 등에서 계산 수행 시 모든 x, y와 같은 변수가 바로 이 열_명이다. 
           “20세 이상에서 월수 500이상........”이라는 조건이 있다면 열_명 age가 변수가 되어서 age >= 20식으로 수식이            가능하다.

데이터베이스 쿼리의 기본은 
문자, 년도는 ' ' 안에 둔다.
대소문자 구별은 없다.
=>DataDictionary안의 데이터는 대문자로 해준다.
    데이터는 대소문자를 구별한다.
select fname, CITY, .... FROM ubuntu_tbl
where id=01; 식으로 해줄 수 있다. 
  Paul 사용자가 있을 때 
~~~~~
where fname='Paul'해주어야 한다. 'paul' 하면 오류
쿼리 문장이 끝나면 ; 을 찍어 준다.

 

직접 데이터 입력

테이블에 데이터를 넣어본다. 
  insert into 테이블_명 (항목들) values (값들); 식으로 데이터를 넣는다. 
여기서 입력되는 데이터 값의 수가 항목 수와 같을 때에는 항목들을 생략할 수 있다. 
=>원래 항목 수가 10개일 때 10개의 데이터를 넣는다면 항목을 생략할 수 있지만 
=>7개의 데이터만 넣을 때에는 
▸항목에서 7개를 지정하고 7개의 데이터를 넣거나, 
▸항목를 생략하고 나머지 3개는 ' '이나 NULL 값으로 주어서 10개를 맞춰주어야 한다.
=>id가 auto-increment일 때에는 id라는 항목만 써주면 자동으로 숫자가 올라간다.

데이터 수정

파일을 통한 데이터 입력

  데이터가 쓰여 있는 파일을 통해서 테이블에 데이터를 입력해보자.  
  데이터가 들어 있는 파일 내의 데이터는 delimiter(구분자)를 엄격히 지켜져 저장되어져 있어야 한다. 
구분자는 ,  ;  :  Tab space 등이다. 억지로 모양을 따져서 Spacebar로 이동하면서 데이터를 만들어 두면 실제 테이블에 데이터가 안 들어갈 수 있다.
  또 첫 줄에 데이터가 아닌 항목_명(열_명)이 있으면 new line이라는 지시어로 첫 줄을 무시하고 로드시킬 수도 있다. 
a. 새 콘솔을 열고 nano name.txt 하고 입력창이 뜨면 
Tab 키로 엄격하게 구별해서(Tab, ; , , , '', ...등을 delimiter(구분자)라고 하는데 별도로 구분자를 지정해서 데이터를 로드한다.

  nano name.txt 하고 편집기를 열면 
내용을 입력한 뒤
Ctrl+o > Enter > Ctrl+x 해서 저장한다.

  Tab 키로 구분된 name.txt 파일과 ,로 구분된 name1.txt 파일이 있는데 두 파일의 구조는 
id, f_name, l_name, city, phone으로 5개의 항목이 각각 들어 있다. 
=>이 두 데이터를 각각의 테이블에 저장한다면 '기존의 테이블을 복사해서 새로운 테이블' 두 개로 만든 뒤 저장하면 된다. 테이블 복사는 
create table name_tbl like ubuntu_tbl;과
create table name1_tbl like ubuntu_tbl; 해주면 기존의 ubuntu_tbl을 name_tbl과 name1_tbl 두개의 빈 테이블로 만들어 준다.  
  이어서 테이블에 파일로 부터 데이터를 입력할 때에는 
a) set global local_infile=1; 해주고 

b) load data local infile '/home/centos/name.txt' into table name_tbl
    fields terminated by '\t'          ## Tab 구별 
    lines terminated by '\n'; 와 

c) load data local infile '/home/centos/name.txt' into table name1_tbl
   fields terminated by ','             ## , 구별 
   lines terminated by '\n'; 해서 올리고
OR DB를 지정해서 로그온하지 않았다면 다음처럼 해도 된다. 
load data local infile '/home/centos/name.txt' into table ubuntu_db.ubuntu_tbl 
fields terminated by '\t'           ## Tab 구별  
lines terminated by '\n; 해서 DB_명.Table_명식으로 지정해도 된다. 

앞에서 Tab으로 구분된 name.txt와 ,로 구분된 name1.txt 두 개의 파일이 있으므로 기존의 테이블에서 구조가 동일한 테이블을 복사한다면
a. use centos_db; 해주고 
b. 기존 테이블에서 테이블을 복사해오는 몇 가지 방식을 알아보자.
a) 테이블 구조만 복사
create table name_tbl LIKE centos_tbl; 와 
create table name1_tbl LIKE centos_tbl; 해서 두 개의 빈 테이블을 만들어 둔다.
b) 기존 테이블에서 내용도 모두 복사해서 새 테이블을 생성하면서 넣는다면 
create table (if not exists) name2_tbl SELECT * FROM CENTOS_TBL;

c) 기존 테이블에서 내용중 일부만 복사해서 새 테이블에 넣는다면 
create table name4_tbl LIKE centos_tbl; 해서 빈 테이블을 생성한 뒤 
insert into name4_tbl (col1, col2, ..) SELECT COL1, COL2, .. FROM CENTOS_TBL; 해주면 된다.
=>create table name4_tbl SELECT COL1, COL2 FROM CENTOS_TBL; 해서 한 번에 처리해도 된다.
   빅 데이터분석에서 매우 많은 항목이 있는 데이터시트에서 분석에 '필요한 항목만 추려서 새로운 테이블로 만든 뒤' 활용할 수 있다.

  암호화 데이터 만들기

  테이블에 입력된 데이터를 암호화해서 숨기고 복호화해서 읽을 수 있다. 암호화는 데이터를 숨기는 것이고 해시는 데이터의 무결성을 보는 것이다.
  우선 암호화는 말 그대로 어떤 TEXT 데이터를 알아볼 수 없게 암호처럼 만드는 것으로 예를 들어 'password'라는 누구나 알아볼 수 있는 <평문>을 암호화하면 ‘0D15222D3D8E40ECCE7AEDC3B8C4280D’식으로 변하게 되고, 복호화는 이것을 다시 'password'라는 <평문>으로 되돌리는 것을 말한다. 
  암호화에는 단방향과 양방향이 있는데, 

∎ 단방향 암호화는 주로 DB 사용자 비밀번호가 저장되어 있는 것처럼 저장 이후에 다시는 <평문>으로 볼 수 없는 경우로써 일반적으로 MySQL 내에서 보면 대표적으로 md5(32 HEX) 또는 sha1(40 HEX) 등의 함수를 사용할 수 있다.
select md5(‘password’);나 select sha1(‘password’);식으로 사용하면 평문장 password의 해시된 값을 확인할 수 있다.  

∎ 양방향 암호화는 이메일 주소나 전화번호, 전자서명 등과 같이 다시 사용할 가능성이 있어 암호화 이후 다시 복호화하여 <평문>으로 볼 필요가 있는 데이터를 저장할 때 사용한다. 양방향 암호화는 AES_ENCRYPT(암호화)와 AES_DECRYPT(복호화) 함수를 이용해서 진행하는데 128비트 길이로 인코딩 되고, 수정하면 256비트까지 확장할 수 있다. 

InnoDB랑 MyISAM을 가장 많이 쓴다고 

 

 

=>password를 AES_ENCRYPT() 함수를 사용해서 hex(AES_ENCRYPT('암호화_할_문자열', '암호화_키')) 형식으로 암호화해서 저장하고, 

    복호화하는데 역시 AES_DECRYPT() 함수를 사용해서 AES_DECRYPT(unhex(암호화된_값), '암호화_키') 형식으로 해준다.

  drop database DB_name; 과 drop table TBL_name; 해서 데이터베이스와 테이블을 없앨 수 있다.

  테이블 내의 데이터 삭제
▪ DELETE : 전체 데이터나 일부 데이터를 삭제하고 테이블은 그대로 놔 둠, 삭제한 데이터 복원 가능 
▪ TRUNCATE : 전체 데이터를 삭제하고 테이블은 그대로 놔둠, 인텍스 등도 삭제, 삭제한 내용 복원 불가