-- 컬럼 타입 변경
alter table TESTTB modify colA VARCHAR(1)

-- 컬럼 이름 변경 
sp_rename 'TESTTB.colA', 'colB'

=> colA 를 colB 로 변경

-- 컬럼 추가
ALTER TABLE sample.dbo.employee ADD new_col char(25) DEFAULT '22' NOT NULL

-- 특정 컬럼이름 찾기

select distinct object_name(id)
from syscolumns
where name = 'colA'
order by object_name(id)

* 테이블 생성

--drop table TESTTB

create table TESTTB (
colA CHAR(2) not null,
colB CHAR(4) not null,
colC CHAR(6) not null,
colD VARCHAR(20) not null
)
lock datarows
with dml_logging = full
on 'default'
go

Grant Insert on dbo.TESTTB to test_user_role Granted by dbo
go

* 특정계정 정보

sp_displaylogin userA

* 계정별 가진 권한 확인

use master
go

select t1.name, t3.name
from syslogins t1, sysloginroles t2, syssrvroles t3
where t2.suid = t1.suid and
t3.srid = t2.srid
order by 1,2
go

--------- -------------- 
userA      sa_role

* 특정 role을 누가 가지고 있는지 확인

use master
go

select sr.name, sl.name
from syssrvroles sr, syslogins sl, sysloginroles slr
where sl.suid = slr.suid
and sr.srid = slr.srid
and sr.name like "sa_role"

------- ---------
sa_role sa
 

* Role 부여

Syntax

sp_role {"grant" | "revoke"}, rolename, loginame

ex) sp_role 'grant', test_user_role, userB

      또는 grant create table to test_user_role

* role에 부여된 권한 확인

sp_helprotect test_user_role

1. 복제 설정

1.1 GRANT REPLICATION SLAVE

@all

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*

TO replicant@'%'

IDENTIFIED BY 'replicant';

1.2 my.cnf 편집

 @Master

 cd /var/log

 mkdir /var/log/mysql

 chown –R mysql.mysql ./mysql

 service mysqld stop

  vi /etc/my.cnf

log_bin = /var/log/mysql/mariadb-bin

[mysqld]

server_id = 101

relay_log = db01-relay-binlog

@Slave

service mysqld stop

vi /etc/my.cnf

[mysqld]

server_id = 102

read_only
 

@Master, Slave

service mysqld start

1.3 복제 설정

@Master

Ifconfig

mysql -u root -p

show master status;

@Slave

CHANGE MASTER TO MASTER_HOST='10.10.10.51',

MASTER_USER = 'replicant',

MASTER_PASSWORD = 'replicant',

MASTER_PORT=33060,

MASTER_LOG_FILE = 'mariadb-bin.000002',

MASTER_LOG_POS = 330;

START SLAVE; 

2. 복제 동작 확인

2.1show slave status 로 확인

@Slave

SHOW SLAVE STATUS;

Slave_IO_Running | Slave_SQL_Running  => yes 인가?

2.2 global_status 에서 확인

use mysql

SELECT variable_value

FROM information_schema.global_status

WHERE variable_name='SLAVE_RUNNING';

3. 복제 테스트

3.1 기본 설정

@Master

CREATE DATABASE IF NOT EXISTS temp;

USE temp;

CREATE TABLE doctors(

id int NOT NULL AUTO_INCREMENT PRIMARY KEY,

given_names varchar(255),

surname varchar(255),

birthdate date);

INSERT INTO doctors VALUES(1,'William','Hartnell','1908-01-08');

INSERT INTO doctors VALUES(2,'Patrick','Troughton','1920-03-25');

INSERT INTO doctors VALUES(3,'John','Pertwee','1919-07-07');

INSERT INTO doctors VALUES(4,'Tom','Baker','1934-01-20');

@Slave

show databases;

use temp

show tables;

select * from doctors;

3.2 Replicate_ignore_db

-     stop slave, 확인

MariaDB [(none)]> stop slave;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT variable_value

               -> FROM information_schema.global_status

               -> WHERE variable_name='SLAVE_RUNNING';

+----------------+

| variable_value |

+----------------+

| OFF            |

+----------------+

1 row in set (0.00 sec)

MariaDB [(none)]> exit

Bye

-     my.cnf 에 replicate_ignore_db 추가

[mysql@localhost ~]$ vi /etc/my.cnf

...

[mysqld]

replicate_ignore_db='information_schema'

replicate_ignore_db='performance_schema'

replicate_ignore_db='mysql'

...

-    mysqld 재기동, slave 재시작

[mysql@localhost ~]$ service mysqld restart

[mysql@localhost ~]$ sql

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 13

Server version: 10.2.7-MariaDB-log MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> start slave;

Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [(none)]> stop slave;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> start slave;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status;

-     복제 테스트 ( ignore 확인)

@Master

MariaDB [temp]> use mysql

Database changed

MariaDB [mysql]> show tables;

+---------------------------+

| Tables_in_mysql           |

+---------------------------+

| column_stats              |

| columns_priv              |

| db                        |

| event                     |

| func                      |

| general_log               |

| gtid_slave_pos            |

| help_category             |

| help_keyword              |

| help_relation             |

| help_topic                |

| host                      |

| index_stats               |

| innodb_index_stats        |

| innodb_table_stats        |

| plugin                    |

| proc                      |

| procs_priv                |

| proxies_priv              |

| reptest                   |

| roles_mapping             |

| servers                   |

| slow_log                  |

| table_stats               |

| tables_priv               |

| time_zone                 |

| time_zone_leap_second     |

| time_zone_name            |

| time_zone_transition      |

| time_zone_transition_type |

| user                      |

+---------------------------+

31 rows in set (0.00 sec)

MariaDB [mysql]> create table aaaa(a int);

Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]> use mysql

Database changed

MariaDB [mysql]> show tables;

+---------------------------+

| Tables_in_mysql           |

+---------------------------+

| column_stats              |

| columns_priv              |

| db                        |

| event                     |

| func                      |

| general_log               |

| gtid_slave_pos            |

| help_category             |

| help_keyword              |

| help_relation             |

| help_topic                |

| host                      |

| index_stats               |

| innodb_index_stats        |

| innodb_table_stats        |

| plugin                    |

| proc                      |

| procs_priv                |

| proxies_priv              |

| reptest                   |

| roles_mapping             |

| servers                   |

| slow_log                  |

| table_stats               |

| tables_priv               |

| time_zone                 |

| time_zone_leap_second     |

| time_zone_name            |

| time_zone_transition      |

| time_zone_transition_type |

| user                      |

+---------------------------+

31 rows in set (0.00 sec)

-    복제 테스트 ( 나머지는 되는가 확인)

MariaDB [mysql]> use temp

Database changed

MariaDB [temp]> show tables;

+----------------+

| Tables_in_temp |

+----------------+

| a              |

| doctors        |

+----------------+

2 rows in set (0.00 sec)

MariaDB [temp]> create table aaa(a int);

Query OK, 0 rows affected (0.02 sec)

MariaDB [mysql]> use temp;

Database changed

MariaDB [temp]> show tables;

+----------------+

| Tables_in_temp |

+----------------+

| a              |

| aaa            |

| doctors        |

+----------------+

3 rows in set (0.00 sec)

- 1. mysql client 접속 

mysql -u root -p

use mysql
select host, user, password from user;

 - 2.1 create user 구문으로 유저생성

create user 'test'@'%' identified by 'test';

CREATE USER 'test'@'10.10.10.10' IDENTIFIED WITH 'mysql_native_password' AS
  #authentication_string# REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
=> IP추가 시 비밀번호 직접 넣지 않고 동일하게 생성

 - 2.2 insert 구문으로 유저생성

insert into user (Host, User, Password, ssl_cipher, x509_issuer, x509_subject, authentication_string)
values ('localhost','test', password('test'),'','','','');

insert into user (Host, User, Password, ssl_cipher, x509_issuer, x509_subject, authentication_string)
values ('%','test', password('test'),'','','','');

flush privileges;     -- * insert 구문으로 유저생성시엔 반드시 flush 해줘야 적용됨.

 - 3. 권한 grant

grant all privileges on temp.* to test@'localhost';
grant all privileges on temp.* to test@'%';

grant all privileges on *.* to test@'%';

  - 4. 권한 확인

show grants for test@'%';

mariadb 설치하는 방법은 여러가지가 있고, 장단점이 있다. 

- source 설치 : 소스를 다운받아 컴파일하는 방식. 홈페이지에서 소스를 다운받았는데 컴파일오류가 나면 당황스럽다. 
- binary 설치 : 이미 컴파일된 바이너리를 다운받아 설치. 설정도 바꿀수 있고, 컴파일 에러도 피해갈수 있다. 
- yum 설치 : 가장쉽고 실패할 일이 거의 없다. 단점은 설정을 맘대로 바꾸기 힘들다는것 (ex, 설치계정, datadir..)

 binary 설치가 나의 경우엔 가장 적합했다. 

1.설치 준비

- 라이브러리

yum -y install gcc gcc-c++ libtermcap-devel gdbm-devel zlib* libxml* freetype* libpng* libjpeg* iconv flex gmp ncurses-devel cmake.x86_64 libaio

cd /usr/local/
wget -N https://downloads.mariadb.org/interstitial/mariadb-10.2.7/bintar-linux-x86_64/mariadb-10.2.7-linux-x86_64.tar.gz
tar zxvf mariadb-10.2.7-linux-x86_64.tar.gz

ln -s /usr/local/mariadb-10.2.7-linux-x86_64 /usr/local/mysql

# mariadb 사용자 및 그룹 추가
groupadd mysql
useradd -g mysql mysql
passwd mysql

# 권한설정
chown mysql.mysql -R mariadb-10.2.7-linux-x86_64
chmod 750 -R /usr/local/mariadb-10.2.7-linux-x86_64
chown mysql.mysql -R ./mariadb
chown mysql.mysql -R ./mariadb-10.2.7-linux-x86_64

2. 설치 및 설정값 편집 

# my.cnf 복사,편집  (아래 내용은 인터넷에서 가져온거라 잘은 모르겠음)
cp /usr/local/mysql/support-files/my-huge.cnf /etc/my.cnf

- 시스템 메모리가 4G이상일 때 : my-innodb-heavy-4G.cnf
- 시스템 메모리가 1G~2G일 때 : my-huge.cnf
- 시스템 메모리가 512MB정도 일 때 : my-large.cnf
- 시스템 메모리가 32MB~64MB정도 일 때 : my-medium.cnf
- 시스템 메모리가 64MB이하일 때 : my-small.cnf

vi /etc/my.cnf  ( default port 를 변경하고자 한다면.. default = 3306 ) 
[client]
port = 33060
[mysqld]
port = 33060

# mysql(mariadb) 실행데몬 복사

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chown mysql.mysql /etc/init.d/mysqld
chown mysql.mysql /etc/my.cnf

chmod 750 /etc/init.d/mysqld

# mysql 실행데몬 수정
vi /etc/init.d/mysqld

# DB디렉토리 지정 하고 저장 

basedir=/usr/local/mysql
datadir=/DATA01/mysql

chown mysql.mysql -R /DATA01

3. DB 작업 

# DB생성 (mysql 계정으로 로그인)

su - mysql
/usr/local/mariadb/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/DATA01/mysql

# mysql 구동 (mysql 계정으로 로그인)
service mysqld start

# PATH 설정 (mysql 계정으로 로그인)

cd
vi .bash_profile

맨 마지막 줄에
PATH=$PATH:/usr/local/mysql/bin

닫고 저장

source .bash_profile

mysqladmin -u root password 'root123!'

netstat -an |grep 3306

# drop database test

** mariadb basedir 경로 변경 -> mysql

MariaDB 를 default 로 설치하면 문자 셋이 latin1 로 되어있다. 한글을 사용하기 부적절하므로 character set 을 변경해줘야 함.

 ** character set 변경 

1. 현재 문자셋 확인 

  show variables like 'c%'

2. my.cnf 편집 

  vi /etc/my.cnf

#########################################
[client]
 
default-character-set=utf8

[mysqld]

init_connect="SET collation_connection=utf8_general_ci"
init_connect="SET NAMES utf8"
character-set-server=utf8
collation-server=utf8_general_ci

skip-character-set-client-handshake

[mysql]

default-character-set=utf8

#########################################

3. 재기동

service mysqld restart

4. 문자셋 확인 

  show variables like 'c%'

## 여기까지 되면 보통은 거의 해결.

 추가적으로..

 1. character set 변경전에 생성된 테이블들은 따로 변경해주거나 재생성

 2. Toad 같은 tool 에서는 한글 입,출력이 되는데 terminal 에서 error 가 난다면 

      터미널 설정을 확인 

   (ex. secureCRT 경우 Session Options 에서 설정 변경)

** 오픈소스 DB 를 본격적으로 써본게 처음이라 아직까지는 불신이 있었다. 

문자셋 변경을 해도 터미널에서 한글 insert 가 안되서 오픈소스 탓을 했는데,

내가 사용한 터미널과 툴의 문제였다. 조금더 MariaDB 에 믿음을 가져보자 

Top ten Database Security Threats

1. Excessive Privilege Abuse (과도한 권한에 의한 남용)
필요하지 않은 권한까지 부여, 남용되는 사례
ex) 학생 신상 정보만 업데이트 하면 되는 DB작업자에게 성적 수정까지 할 수 있는
과도한 권한 부여

2. Legitimate Privilege Abuse (정당한 권한에 의한 남용)
정당한 권한은 맞지만 남용될 수 있는 사례
ex) 병원 관계자가 환자의 개인 기록을 언제든지 조회할 수 있는 상황

3. Privilege Elevation (권한 상승)
해커가 침입시 정보 유출 작업을 더욱 손쉽게 하려고 제일 먼저 최대한
높은 권한으로의 상승을 시도함.

4. Platform Vulnerabilities (플랫폼 취약성)
데이터베이스 서버가 설치된 운영체제의 취약성으로 말미암아 위협받는 상황

5. SQL Injection

6. Weak Audit Trail
감사 정책을 약하게 가져가면서 문제 상황을 놓칠 수 있는 상황

7. Denial of Service (Dos)
대표적인 해킹 공격 유형

8. Database Communication Protocol Vulnerabilities (데이터베이스 프로토콜 오류)
제품 버그

9. Weak Authentication (약한 인증)
편리함 때문에 인증 절차를 간소화, 축소하면서 발생할 수 있는 위협

10. Backup Data Exposure (백업 데이터 노출)
암호화되지 않은 채 백업 데이터가 외부로 유출되는 경우

내용출처: '오라클 데이터베이스 보안 / 당신의 데이터는 안녕하십니까' (김규백 저)  의 내용을 참고하여 정리하였습니다.

autotrace 옵션들

1) set autotrace on 
  -> 실제 수행 + 결과 + 실행계획 + 실행통계

2) set autotrace on explain
  -> 실제 수행 + 결과 + 실행계획
 
3) set autotrace on statistics
  -> 실제 수행 + 결과                 + 실행통계
 
4) set autotrace trace explain
  ->                          실행계획
 
5) set autotrace traceonly 
  -> 실제 수행          + 실행계획 + 실행통계

    impdp 할때 ora error

      실제 파일이 존재함에도 발생.

      구글링으로 parallel 구문 없애고 다시 실행하니 됨.

   ** 추가

     paralle 실패한 이유 : RAC 환경에서  디렉토리 접근권한이 node1 에만 있다면 parallel 안먹힘.

     impdp 시에 index 가 있으면 undo 사용량이 많아진다. index 를 unusuable 시키면 impdp 가 에러난다.

     (alter index ... unusable 은 sqlplus 에서만 되네... 희안하네..)

     -> index drop 후 재생성

    - expdp parallel :

    expdp id/pwd directory=dba_dir tables=A.B:partition dumpfile=dudump%U.dmp parallel=4 logfile=expdplog CONTENT=DATA_ONLY

• For Data Pump Export, the PARALLEL parameter value should be less than or equal to the number of output dump files.
• For Data Pump Import, the PARALLEL parameter value should not be much larger than the number of files in the dump file set.

Data Pump Impdp/expdp optimal parallel tips

Data Pump Impdp/expdp optimal parallel tips Oracle Database Tips by Donald Burleson December 17 , 2015 Question: What is the o...

www.dba-oracle.com

Data Pump Impdp/expdp optimal parallel tips

Data Pump Impdp/expdp optimal parallel tips Oracle Database Tips by Donald Burleson December 17 , 2015 Question: What is the o...

www.dba-oracle.com