[Awk를 이용한 스크립트] - mysql 사용자 등록 관리를 위한 셀 스크립트 만들기

[Shell-Script기초 부터 고수되기] awk를 이용하여 mysql  사용자 등록 SQL 작성하기

- 셀스크립트로 Mysql 데이터베이스을  관리 하는 예제

 

 

Awk 의 기초

[Awk의 기초 문장 구조 ]
Awk의  문장 구조는  다음과 같은 4가지 영역으로 나누어 집니다.

  1) 글로벌 변수 또는 함수 를 선언 하는 영역
      - 프로그램 내부에서 필요한 변수와 함수를 정의 합니다.

  2) BEGIN {...}    : awk가 실행되면서 수행되는 영역 (데이터를 읽기전에 시작)
      - awk 는 파일을 읽으면서 한행씩 처리를 하는데 BEGIN은 읽기 전에 처리 되는 영역으로 변수 또는 초기에 필요한 내용을 작성합니다.

  3) 메인 구역 { ...} : 입력 되는 데이터를 읽으면서 반복되는 영역으로  입력 값의 패턴 매칭이 가능  
      - 파일이나 입출력으로 한 행 (Line)씩 입력되는값을 반복하면서 처리하는 영역으로  특정 값을 매칭하거나 포함 되지 않은 경우를 분리해서 처리가 가능합니다.      
      - Awk의 특징중에서 C언어 처럼 if, for, while 같은 제어 흐름도 구현이 가능하지만,  C언어와 달리 특정 영역을 패턴매칭으로 분리해서  개발이 가능하다는 점입니다.
    
  4) END { ...}  : 입력 데이터를 모두 읽었고 프로그램이 종료되기 전에 수행되는 영역
      - 입력되는 값이 모두 처리된 마지막에 처리할 영역으로 보통 summary 데이터를 만들어 결과로 출력할 내용을 작성하는 영역입니다.  

** mysql의 사용자 등록과 권한 부여는 create user, alter user, grant 와 revoke 등으로 사용 하면 더 쉽게 관리가 되는데 관리상 아래 와 같이 해야 하는 상황은 전체적인 사용자를 강제적으로 수정하여야 하는 상황에서 활용하기를 바랍니다.
본 예제는 셀스크립트는 반드시 실행만 하는 것이 아니라 필요한 명령을 파일로 만들어 다시 실행하는 기법도 참고 바랍니다.
그리고 Awk를 통해서 셀스크립트를 만들거나 시스템을 확인하는 예제로 보시면 좋겠습니다.


평소 mysql을 새로 설치 하거나 Upgrade 하는 경우  새로운 사용자를 등록할때..
각 버전별 필드의 내용이 달라서 새로 만드는 작업이 불편한점이 많았다.
이번에 AWK를 이용해서 사용자 등록을 위한 스크립트를 만들어 서버에서 실행하기 쉽도록 하였다.
사용자 관리나 전체 DB의 사용자 관리와 권한 회수 및 관리를 위해서 이런 파일 또는 DB를 활용 하는데 사용하면 좋을 것 같다.
시스템을 업그레이드 하거나 자주 사용자의 관리를 해야 하는 경우 그때마다 등록하는 수작업의 불편함을 줄였다.

1. awk를 이용할 Shell Script를 만든다.

아래 [user_add_SQL.sh] 는 user_add_SQL.dat 파일에 사용자가 접근하는 IP와 계정명 그리고 패스워드를 입력하고 Admin이면 Y 일반 유저이면 N이 되도록 기입을 하는 파일을 만들었다.
- ( IP, User_ID, User_Pw, Admin_YN) 와 같은 데이터 파일을 생성하고
- awk 명령을 수행하는 뼈대를 구성하였고 복잡한 awk 로직은 별도 파일로 만들고자 한다.

cat > user_add_SQL.dat  <<!

10.20.4.%      root      pwdbadmin   Y
127.0.0.1       root      pwdbadmin   Y
127.0.0.1       myuser  pwmyuser    N
10.20.4.%      myuser  pwmyuser    N
180.70.96.%   myuser  pwmyuser    N
127.0.0.1       dbadmin pwdbadmin   Y
10.20.4.%      dbadmin pwdbadmin   Y
180.70.96.%    dbadmin pwdbadmin  Y
!

#------------------------------------------------------

# 위의 파일을 실행하는 awk 스크립트를 만들어 둔다. 

#  awk의 실행부는  user_add_SQL.awk 파일로 별도 작성하였다.

 

if [ -f  "user_add_SQL.dat" ]

then
    awk -f user_add_SQL.awk user_add_SQL.dat

else

    echo " >> Error : user_add_SQL.dat Not Found !!! ";
fi

 

2. Awk Script의 실제 로직을 아래와 같이 만든다.

 아래 Awk의 구분은 다음과 같은 4가지 구역으로 나누어 진다.
  1) 글로벌 변수 또는 함수 구역
  2) BEGIN {...}    : awk가 실행되면서 수행되는 구역 (데이터를 읽기전에 시작)
  3) 메인 구역 { ...} : 입력 되는 데이터를 읽으면서 반복되는 구역 
  3) END { ...}  : 입력 데이터를 모두 읽었고 프로그램이 종료되기 전에 수행되는 구역


[user_add_SQL.awk] 는 미리 작성해둔 "user_add_SQL.dat " 파일을 읽어서 실행하는 로직이 구현되어 있다.
1) 함수 구역 
  -  우선 "user_add_SQL.dat " 파일의 구조가  접근하는 IP, 사용자, 패스워드, admin여부 에 대한 정보가있다.
  (1)  function print_adduer_ddl(v_host, v_user, v_passwd )  :입력 받은 정보를 가지고 SQL을 작성한다.
  (2)  function print_admin_ddl(v_host, v_user, v_passwd ) : admin 사용자 인경우 권한을 추가해주는 SQL을 작성한다.


2) BEGIN 
   -  awk로 데이터가 입력 되기 전에 실행되는 문장이다.
3) 메인 구역
   - 메인 구역은 앞에 아무것도 없는 {... } 로 구성된 구역으로 awk가 파일/입력 데이터를 한 행씩 읽으면서 반복 실행하는 문장이다.
   - user_add_SQL.dat을 한행씩 읽으면서 반복하는 구문으로 각 사용자의 생성과 권한을 업데이트 하는 쿼리를 생성한다. 
4)  END 구역
  - awk로 데이터가 입력이 완료되고 종료되기 전에 실행되는 문장이다.
  - mysql에서 실행할  "flush privileges; 와 select * from user; 문장을 출력했다.

 

#!/bin/awk # Editor by  @http://couplewith.tistory.com/ # description : using Awk, generate sql for user add; # # usage :  cat "ip user_id passwd Y|N" | awk -f user_add_SQL.awk #          parms : IP, User_ID, User_Pw, Admin_YN(Y|N) function print_adduer_ddl(v_host, v_user, v_passwd ) {    #-- General User    printf (" INSERT INTO mysql.user \                 ( Host, User , Password, Select_priv, Insert_priv, Update_priv, Delete_priv,         \                 File_priv, References_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, \                 Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv)         \         VALUES ('%s', '%s', password('%s'), 'Y', 'Y', 'Y', 'Y',                       \                 'Y', 'Y', 'Y', 'Y', 'Y',                                              \                 'Y', 'Y', 'Y', 'Y' );\n", v_host, v_user, v_passwd ); } function print_admin_ddl(v_host, v_user, v_passwd ) { #-- Admin User 일 경우 권한을 추가해 주는 영역 printf (" UPDATE mysql.user     \         SET                          \                 Password = %s,             \                 Create_priv = 'Y',            \                 Drop_priv = 'Y',               \                 Reload_priv = 'Y',            \                 Shutdown_priv = 'Y',         \                 Process_priv = 'Y',           \                 Grant_priv = 'Y',               \                 Index_priv = 'Y',               \                 Alter_priv = 'Y',                \                 Show_db_priv = 'Y',           \                 Super_priv   ='Y',              \                 Create_user_priv = 'Y',        \                 Event_priv = 'Y',                \                 Trigger_priv = 'Y',              \                 Create_tablespace_priv = 'Y'  \         WHERE Host = %s                       \            AND User = %s;\n",v_passwd, v_host, v_user); } ##### Awk Main Routine ################################ BEGIN {      # init mysql.user table      print "use mysql;";      print "truncate table mysql.user;"; } {     # $1 : hostip, $2 : user_id, $3 : Passwd  $4 : isadmin 'Y|N'     printf (" log> IN : [%d]  %s  %s  %s  %s \n", NR, $1, $2, $3, $4 );     print_adduer_ddl( $1, $2, $3 );     # 사용자 등록 SQL     if ( index ($4, "Y") > 0 ) {        print_admin_ddl( $1, $2, $3 );   # Admin 유저를 위한 권한 수정 SQL     } } END {      print "flush privileges;";      print "select * from user;"; }

------------------------------------------------------------------------------

 

3. user_add_SQL.sh 을 실행해본다.

  > sh user_add_SQL.sh

use mysql;

 truncate table mysql.user;

 INSERT INTO mysql.user    ( Host, User , Password, Select_priv, Insert_priv, Update_priv, Delete_priv,                            File_priv, References_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, 
     Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv)
  VALUES ( '180.70.96.%', 'dbadmin', password('dbadmin'), 'Y', 'Y', 'Y', 'Y',
                'Y', 'Y', 'Y', 'Y', 'Y',     'Y', 'Y', 'Y', 'Y' );

 

 UPDATE mysql.user   SET 

     Password = dbadmin,       Create_priv = 'Y',     Drop_priv = 'Y',        Reload_priv = 'Y',
      Shutdown_priv = 'Y',         Process_priv = 'Y',   Grant_priv = 'Y',       Index_priv = 'Y', 

      Alter_priv = 'Y',                Show_db_priv = 'Y',   Super_priv   ='Y',     Create_user_priv = 'Y',                       

      Event_priv = 'Y',               Trigger_priv = 'Y',      Create_tablespace_priv = 'Y'   

  WHERE Host = 180.70.96.% 

     AND User = dbadmin;

 

flush privileges;

select * from user;

 

 

위와 같이 쿼리를 작성하는 awk 를 만들어 보았다.

이제 mysql은  user 테이블을 수정하여 계정을 등록하는 것이 일반 적인 것이 아니다.

실제 사용자 등록과 권한 입력은 create user, alter user 명령을 사용하기를 권고 합니다.  아래 링크를 참조 바랍니다.

1. [MYSQL-계정-생성-권한부여-권한-삭제] https://couplewith.tistory.com/entry/MYSQL-%EA%B3%84%EC%A0%95-%EC%83%9D%EC%84%B1-%EA%B6%8C%ED%95%9C%EB%B6%80%EC%97%AC-%EA%B6%8C%ED%95%9C-%EC%82%AD%EC%A0%9C