cubrid 설치 및 DB LINK 설정

20220713 cubrid 테스트

 

1. 큐브리드 설치

rpm –q ncurses & libgcrypt & libstdc++

 

./CUBRID-11.2-latest-Linux.x86_64.sh

 

CUBRID=/cubrid/cub_engine

CUBRID_DATABASES=$CUBRID/databases

 

 

2. db 생성

 

KO16MSWIN949

 

cubrid createdb --db-volume-size=512M -F /data/test1 --log-volume-size=200M -L /data/log testdb1 ko_KR.utf8

cubrid createdb --db-volume-size=512M -F /data/test2 --log-volume-size=200M -L /data/log testdb2 ko_KR.utf8

cubrid createdb --db-volume-size=512M -F /data/test3 --log-volume-size=200M -L /data/log testdb3 ko_KR.utf8

java_stored_procedure=yes

cubrid javasp start

Failed to get 'JVM_PATH' environment variable

Failed to get 'JAVA_HOME' environment variable. (profile 설정)

 

cubrid addvoldb -C -p index -F /data/test1 -n testdb1_index_x001 --db-volume-size=100M testdb1

cubrid addvoldb -C -p temp -F /data/test1 -n testdb1_temp_x001 --db-volume-size=100M testdb1

 

cubrid addvoldb -C -p index -F /data/test2 -n testdb2_index_x001 --db-volume-size=100M testdb2

cubrid addvoldb -C -p temp -F /data/test2 -n testdb2_temp_x001 --db-volume-size=100M testdb2

 

cubrid addvoldb -C -p index -F /data/test3 -n testdb3_index_x001 --db-volume-size=100M testdb3

cubrid addvoldb -C -p temp -F /data/test3 -n testdb3_temp_x001 --db-volume-size=100M testdb3

 

create user test_user;

alter user test_user password 'test';

grant select on termcc to test_user;

csql> show tables;

=== <Result of SELECT Command in Line 2> ===

  Tables_in_testdb3

======================

  'termcc'

 

 

db_user 계정을 생성하고 ‘user#pass’ 비밀번호로 신규계정 생성하기

- CREATE USER db_user PASSWORD ‘’;

// 맴버로 db_member 계정을 생성하기 → db_member가 생성한 모든 테이블 권한을 db_user와 공유

- CREATE USER db_member PASSWORD ‘pass' MEMBERS db_user;

// DBA 맴버로 dba_group 계정을 생성하기 → dba권한을 가진 유저 생성

- CREATE USER test dba_group PASSWORD 'test’ GROUPS dba;

 

 

======================================================================================

csql -utest_user -ptest testdb3

create table testt (id varchar(10));

insert into testt values('test');

commit;

 

csql -udba -pdba testdb1

create user test_user password 'test';

select * from dblink ('192.168.18.129:33000:testdb3:test_user:test:', 'select id from testt') as t(id varchar(10));

 

or

 

CREATE SERVER testdb3_svr ( HOST='192.168.18.129', PORT=33000, DBNAME=testdb3, USER=test_user, PASSWORD='test');

SELECT * FROM DBLINK (testdb3_svr, 'SELECT id FROM testt') as t(id varchar(10));

 

========================================================================================

termcc가 dba계정의 table이므로 dba.으로 계정을 붙혀서 사용해야하고

SELECT * FROM DBLINK ('192.168.18.129:33000:testdb3:test_user:test:','SELECT node_code, proj_code FROM dba.termcc') as t(node_code VARCHAR(4), proj_code VARCHAR(12));

 

번거로움과 계정명을 붙히는걸 피하려면 동의어사용.

create synonym termcc for dba.termcc;

csql> SELECT * FROM DBLINK ('192.168.18.129:33000:testdb3:test_user:test:','SELECT node_code, proj_code FROM termcc') as t(node_code VARCHAR(4), proj_code VARCHAR(12));

=== <Result of SELECT Command in Line 2> ===

  node_code             proj_code

============================================

  '1001'                NULL

  '1001'                NULL

 

CREATE SERVER testdb3_svr ( HOST='192.168.18.129', PORT=33000, DBNAME=testdb3, USER=test_user, PASSWORD='test');

SELECT * FROM DBLINK (testdb3_svr, 'SELECT node_code, proj_code FROM termcc') as t(node_code VARCHAR(4), proj_code VARCHAR(12));

======================================================================================

 

 

SELECT * FROM DBLINK ('192.168.18.129:33000:testdb3:test_user:test:','SELECT node_code, proj_code FROM dba.termcc') as t(node_code VARCHAR(4), proj_code VARCHAR(12));

SELECT * FROM DBLINK ('192.168.18.129:33000:testdb3:dba:dba:','SELECT node_code, proj_code FROM termcc') as t(node_code VARCHAR(4), proj_code VARCHAR(12));

두번째, 위의 DBLINK Query를 보면 타 데이터베이스에 접속하기 위한 정보는 가장 기본적인 정보다. 그래서 Query를 작성할 때 마다 매번 작성해야 하는 번거로움과 사용자 정보(id, password) 가 외부로 노출될 우려가 있다. 이런 번거로움과 정보 보호를 위해 CREATE SERVER문을 이용하면, Query문 보다 간단하고, 사용자 정보 보호에 도움이 된다.

CREATE SERVER testdb3_svr_dba ( HOST='192.168.18.129', PORT=33000, DBNAME=testdb3, USER=dba, PASSWORD='dba');

SELECT * FROM DBLINK (testdb3_svr_dba, 'SELECT node_code, proj_code FROM termcc') AS t(node_code VARCHAR(4), proj_code VARCHAR(12));

CREATE SERVER remote_srv1 ( HOST='192.168.0.1', PORT=53000, DBNAME=demodb, USER=user, PASSWORD='password');

SELECT * FROM DBLINK (remote_srv1, 'SELECT col1 FROM remote_t') AS t(col1 int);