웹 개발 정보

1. 오라클(Oracle) LOCK 조회 및 해제

평소에는 아무런 문제가 없이 사용이 되다가 특정 기능을 실행 시킬때 처리가 안되고 지연되는 현상이 간혹 발생할 때 가 있습니다.

전체적으로 문제가 생긴게 아니라 특정 기능에 대해서 일시적으로 생긴 문제라 생각을 할 수 있겠지만, 혹시 DB 테이블에 LOCK이 걸려 있는게 있나 확인을 하는게 좋습니다.

LOCK이 걸린 테이블은 SELECT 조회는 가능합니다. 다만, insert,update,delete과정에서 처리가 안되고 있는거를 볼 수 있습니다.

LOCK을 확인을 하기 위해서는 일반 계정으로 오라클에 접속시는 확인이 불가합니다.

SYSTEM계정으로 접속 후 확인 및 해제를 하셔야 합니다.

2. LOCK 걸린 테이블 확인

select * from v$locked_object

간단한 LOCK이 있는 테이블이 있는지 확인

SELECT 
	T2.SID, 
	T2.SERIAL#, 
	T2.USERNAME, 
	T2.SADDR, 
	T2.OSUSER, 
	T2.PROGRAM, 
	T3.SQL_TEXT 
FROM 
	V$LOCKED_OBJECT T1 
	LEFT JOIN V$SESSION T2 ON T2.SID = T1.SESSION_ID 
   	LEFT JOIN V$SQLTEXT T3 ON T3.ADDRESS = T2.SADDR 
ORDER BY T2.SID

상세정보를 보기 위해서는 위에 쿼리를 조회 하시면 LOCK이 걸린 테이블 및 쿼리가 나옵니다.

정보를 확인을 하시고 해제를 시켜주면 되겠습니다.

3. LOCK 걸린 세션 해제

alter system kill session 'SID, SERIAL#';

LOCK이 걸려있는 세션 SID와 SERIAL값으로 해제를 합니다.

1. MySQL 검색 결과 행 번호 매기기(ROWNUM)

MySQL은 oracle과 다르게 검색 결과에 행 번호를 넣는 방법이 다릅니다. 

oracle은 ROWNUM을 통해서 검색된 결과에 자동적으로 순번이 들어가는데 MySQL은 그렇지 않습니다. 

그래서 행번호를 적용하는 방법이 아래와 같습니다. 

2. MySQL 페이징

검색결과에 행 번호를 추가를 했으면 페이징을 하는 방법입니다. 

1. MySQL 사용자 추가 

create user '사용자'@'IP' identified by '비밀번호';

사용자 : 사용자 아이디 입력

IP : 사용자가 접속될 IP 입력 예) localhost(로컬 접속), 1.2.3.4(1,2,3,4 IP만 허용), 1.2.3.%(1,2,3 번대 IP 허용), %(모든 IP 허용)

비밀번호 : 사용자 비밀번호 입력 

#db 접속 
mysql -u root -p

#db 사용자 추가 
create user 'test1'@'localhost' identified by 'pass1';
create user 'test2'@'1.2.3.%' identified by 'pass2';
create user 'test3'@'%' identified by 'pass3';

2. 등록된 사용자에게 권한 추가

grant all privileges on DB이름.Table이름 to '사용자'@'IP';

grant all privileges on DB이름.* to '사용자'@'IP'; -- DB에 대한 전체 테이블에 해당하는 권한을 추가시 

grant all privileges on *.* to '사용자'@'IP'; -- 전체 DB, 테이블에 해당하는 권한을 추가시 

#db 사용자 추가 
create user 'test1'@'localhost' identified by 'pass1';
create user 'test2'@'1.2.3.%' identified by 'pass2';
create user 'test3'@'%' identified by 'pass3';

#db 사용자 전체권한 추가 
grant all privileges on DB이름.Table이름 to 'test1'@'localhost';
grant all privileges on DB이름.* to 'test2'@'1.2.3.%'; -- DB에 대한 전체 테이블에 해당하는 권한을 추가시 
grant all privileges on *.* to 'test3'@'%'; -- 전체 DB, 테이블에 해당하는 권한을 추가시

권한에 대한 상세한 내용 : https://jang2r.tistory.com/41?category=794926

3. MySQL 사용자 삭제

drop user '사용자'@'localhost';

#db 사용자 삭제 
drop user 'test1'@'pass1';

1. MySQL 사용자 권한 확인

1-1. 기본권한 조회

# mysql 접속
mysql -u root -p
# mysql DB 접근
mysql> use mysql 
# 기본 권한 조회
mysql> select * from user where user = [사용자ID]

1-2. 사용자 권한 조회

# 사용자별 권한 확인
mysql> SHOW GRANTS FOR '사용자계정'@'호스트';
# 접속된 계정 권한 확인
mysql> SHOW GRANTS FOR CURRENT_USER;

2. MySQL 사용자 권한 추가

예)

GRANT ALL PRIVILEGES ON DB이름.테이블이름 TO 아이디@호스트 IDENTIFIED BY '비밀번호' with grant option;

# 권한 종류

- ALL PRIVILEGES : 모든 권한 추가 

- SELECT, INSERT, UPDATE, DELETE, ... : 권한을 일부분을 추가 

# 적용될 DB 및 테이블 

- DB이름.테이블이름 : *.* 시 모든 DB, 모든 Table 권한 부여 

# with grant option 옵션

- with grant option : GRANT를 사용할 수 있는 권한 추가 

1. DB 덤프(dump) 및 복구

# 전체 DB dump 및 복구
./mysqldump -u [계정] -p  --all-databases > [생성dump파일 이름].sql
./mysql -u [계정] -p < [생성dump파일 이름].sql

# 특정 DB dump 및 복구
./mysqldump -u [계정] -p [DB 이름] > [생성dump파일 이름].sql
./mysql -u [계정] -p [DB 이름] < [생성dump파일 이름].sql

2. 테이블 덤프(dump) 및 복구

# 테이블 dump 및 복구
./mysqldump -u [계정] -p [DB 이름] [Table 이름1] [Table 이름2] .. > [생성dump파일 이름].sql
./mysql -u [계정] -p [DB 이름] < [생성dump파일 이름].sql 

3. 데이터 덤프(dump) 및 복구

# DB 데이터 dump 및 복구
# 테이블 전체 데이터 dump
- ./mysqldump -u [계정] -p -t [DB 이름] [Table 이름1] > [생성dump파일 이름].sql
# 조건에 해당하는 데이터 dump
- ./mysqldump -u [계정] -p [-w '조건절'] [DB 이름] [Table 이름1] > [생성dump파일 이름].sql

- ./mysql -u [계정] -p [DB 이름] [Table 이름1] < [생성dump파일 이름].sql

MySQL 원격 접속 허용

MySQL을 설치를 진행하게 되면 기본적으로는 로컬에서만 접속이 가능하도록 설치가 됩니다. 

이런 설정을 외부에서 DB를 접속 할 수 있게 변경을 해줘야 합니다. 

1. MySQL 접속 IP 확인

5.7 이상 버전 : SELECT Host,User,plugin,authentication_string FROM mysql.user;

5.6 이하 버전 : SELECT Host,User,plugin,password FROM mysql.user;

위에 있는 명령어로 확인이 가능합니다. 

mysql> SELECT Host,User,plugin,authentication_string FROM mysql.user;

+-----------+---------------+-----------------------+-------------------------------------------+
| Host      | User          | plugin                | authentication_string                     |
+-----------+---------------+-----------------------+-------------------------------------------+
| localhost | root          | mysql_native_password | *97925E5A498F7FDBC94DAC363A9753359A6E14D9 |
| localhost | mysql.session | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+---------------+-----------------------+-------------------------------------------+

출력된 결과를 보면 root인 사용자로 DB를 외부에서 접속시 localhost에만 접속이 가능하게 되어 있어 접속이 불가합니다. 

2. 외부 IP 추가

외부 IP접속이 추가하는 방법은 3가지 정도로 나눈다. 

2-1. 모든 ip에서 접속시 허용

GRANT ALL PRIVILEGES ON *.* TO '아이디'@'%' IDENTIFIED BY '패스워드';

FLUSH PRIVILEGES;

2-2. 특정 ip대역에 접속시 허용

GRANT ALL PRIVILEGES ON *.* TO '아이디'@'111.222.%' IDENTIFIED BY '패스워드'; 

FLUSH PRIVILEGES;

2-3. 특정 단일 ip에서 접속시 허용

GRANT ALL PRIVILEGES ON *.* TO '아이디'@'111.222.33.44' IDENTIFIED BY '패스워드';

FLUSH PRIVILEGES;

3. 등록 확인 후 적용

등록이 끝났다고 하면 SELECT Host,User,plugin,authentication_string FROM mysql.user; 명령어로 확인 후 외부에서 다시 접속을 시도해 봅시다. 

오라클(Oracle) ROLE 및 시스템권한 조회 및 부여

1. 유저 조회

실행 쿼리 - SELECT * FROM all_users;

2. 유저 ROLE 조회 및 부여

2-1. 기본적인 ROLE 목록

ROLE에는 이것 말고도 더 많은 ROLE이 존재를 한다.

2-2. 계정에 적용중인 ROLE 확인

실행쿼리 

 - SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'USER4' ;

 - SELECT * FROM USER_ROLE_PRIVS;

USER4 사용자에게 적용된 ROLE

 - CONNECT, RESOURCE 

2-3. 계정에 ROLE 부여

USER4 사용자에게 ROLE 추가

 - DBA

3. 유저 시스템권한 조회 및 부여

3-1 시스템 권한 목록

시스템 권한에는 이 것 말고도 더 많은 권한이 있다.

3-2. 계정에 적용중인 시스템 권한 확인

실행쿼리

 - SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'USER4';

 - SELECT * FROM USER_SYS_PRIVS;

USER4 적용된 시스템 권한

 - CREATE VIEW, UNLIMITED TABLESPACE

3-3. 계정에 시스템 권한 부여

USER4 사용자에게 시스템권한 추가

 - SYSOPER

오라클(Oracle) 테이블 스페이스, 유저 생성

1. 테이블스페이스 물리적 경로 확인

테이블 스페이스를 생성을 할려면 물리적 경로를 먼저 알아야 합니다.

물리적 경로를 확인하는 포스팅이 올라가져 있으니 참고해 주세요 -> 오라클(Oracle) 테이블스페이스 이름 및 물리적 경로 확인 

2. 테이블 스페이스 생성

실행 쿼리 - create tablespace SPACE_NAME4 datafile '/home/server/oracle/oradata/db_sid/spaceNameFile4.dbf‘ size 100m;

테이블 스페이스 이름 - SPACE_NAME4

테이블 스페이스 물리적 경로 - /home/server/oracle/oradata/db_sid/

테이블 스페이스 파일 이름 - spaceNameFile4.dbf

테이블 스페이스 용량 - 100m

3. 유저 생성(테이블 스페이스에 추가)

실행 쿼리 - create user USER4 identified by USER4PW default tablespace SPACE_NAME4;

생성되는 유저 아이디 - USER4

생성되는 유저 패스워드 - USER4PW

생성되는 유저가 들어간 테이블스페이스 - SPACE_NAME4;

4. 테이블스페이스에 들어있는 유저 목록(생성된 유저 확인)

실행 쿼리 - SELECT USERNAME, DEFAULT_TABLESPACE FROM dba_users;

유저 목록 - USER1, USER2, USER3 ...

테이블스페이스 목록 - SPACE_NAME1, SPACE_NAME2, SPACE_NAME3 ....