DataBase 수업 기록 (1) 기초, 사용자 생성 및 권한

▶ 데이터베이스란? 

- 여러 사람(응용프로그램)에 의해 공유되어 사용될 목적으로 통합, 구조화 되어, 저장, 관리 되는 데이터들의 집합

 

* 데이터베이스가 필요한 이유

 - 기존 파일 시스템의 처리방식은 하나라도 놓치면 데이터 불일치 현상이 발생 되기 때문.

 

* 데이터베이스 관리 시스템(DBMS)

- 데이터베이스를 저장하고 관리하는 역할을 하는 소프트웨어

응용프로그램

↕

데이터베이스

↕

데이터베이스

1. NO-SQL

 (DB가 JSON으로 되어있다.)

 - Mongo DB

2. Relation DB(관리형)

 - Qracle

 - MySQL

 - MS-SQL

 

* 오라클 설치 홈페이지

https://www.oracle.com/database/technologies/xe-prior-release-downloads.html

XE Prior Release Archive

 

▶ 사용자 생성 및 권한 설정 

* 데이터베이스 권한

 - 데이터베이스 보완

    1) 모든 사용자가 데이터베이스에 접근하거나 수정이 가능하다면 보안상 큰 문제가 발생

    2) 각 사용자마다 최소한의 권한을 주어 데이터베이스의 보안을 유지

DB 연결하는 3가지 방법. Structured Query Language

- DB는 사용자 로그인을 해야 사용이 가능하다. (DB는 접속이 1번이다.)

 

* 데이터베이스 사용자

오라클 관리자 계정
SYS	SYSTEM
SYSDBA 권한을 가짐(Oracle 시스템 유지 관리 계정)	SYSOPER 권한을 가짐(Oracle 데이터베이스 유지 관리 계정)
Data Dictionary의 소유자(Owner) 계정	어떤 데이터베이스를 운영하기 위한 계정
새로운 데이터베이스의 생성이나 기존 데이터베이스의 삭제 가능	새로운 데이터베이스의 생성이나 기존 데이터베이스의 삭제는 불가능

* 사용자 생성

 - 오라클 관리자 계정으로 작업하지 않고, 새로운 사용자 계정을 만들어서 사용해야 함

 - 관리자(DBA) 권한을 가진 사용자는 새로운 사용자를 생성할 수 있음

 - CREATE USER 쿼리문 이용

CREATE USER SCOTT IDENTIFIED BY TIGER;

* 사용자 삭제

DROP USER SCOTT;
DROP USER SCOTT CASCADE;	사용자가 데이터베이스 객체(테이블 등)를 가지고 있는 경우 CASCADE 옵션 필요

 

▶ 데이터베이스 사용자 목록 

* 데이터 사전(Data Dictionary)

 - 데이터베이스에 저장된 정보를 요약한 정보(메타 데이터)

 - 데이터베이스 사용자, 테이블 ,인덱스 등의 정보를 테이블 및 뷰의 형태로 제공

 - 데이터베이스 사용자 목록을 확인할 수 있는 데이터 사전이 존재함

 - 사용자 유형별로 별도로 관리함

 

* 데이터 사전의 종류

 - DAB_ : 데이터베이스 전체에 포함된 모든 객체에 대한 정보

 - USER_ : 자신이 생성한 모든 객체에 대한 정보

 - ALL_ : 자신이 생성한 모든 객체 + 다른 사용자가 생성한 객체 정보 중 볼 수 있는 정보

 - X$_ : 데이터베이스 성능 분석 및 통계 정보

 - V$_ : X$의 뷰(View)

 

* 사용자 목록 확인

- DBA_USRES : 데이터베이스 전체 사용자 정보

- USER_USERS : 자신이 생성한 사용자 정보

- ALL_USERS : 자신이 생성한 사용자 + 다른 사용자가 생성한 사용자

DBA_USERS 테이블 구조 확인	DBA_USERS 테이블의 USERNAME 칼럼 조회
DESC DBA_USERS;	SELECT USERNAME FROM DBA_USERS;
DESCRIBE 명령과 동일함	SELECT 칼럼 FROM 테이블

 

▶ 데이터베이스 접근 권한 

데이터베이스 접근 권한
사용자 권한 기본 제공 ROLE	권한 부여/취소
1) CONNECT : 데이터베이스 접속 권한	1) 관리자(DBA) 권한을 가진 사용자는 다른 사용자의 권한을 조정할 수 있음
2) RESOURCE : 기본적인 데이터베이스 객체(테이블, 뷰, 인덱스 등)를 생성, 변경, 삭제할 수 있는 권한	2) GRANT : 권한 부여
3) DBA : 데이터베이스 관리자 권한	3) REVOKE : 권한 취소

 

* 권한 주기

- 접속 실패시 로그인이 풀림.

접속 권한 부여(Query 문)	접속 권한 회수(Query 문)
GRANT CONNECT TO USER1;	REVOKE CONNECT FROM USER1;
GRANT CONNECT, RESOURCE TO USER1 (일반적으로 이러한 권한을 가지고 있음) (CONNECTION, RESOURCE 권한 부여)	REVOKE CONNECT, RESOURCE FROM USER1;

 

TABLE 생성

CREATE TABLE A_TABLE(NO NUMBER)

 

* 사용자 삭제

 - 관리자(DBA) 권한을 가진 사용자는 기존 사용자를 삭제할 수 있음

 - 접속한 사용자 자신을 삭제할 수는 없음

 - DROP USER 쿼리문 이용

사용자 삭제	TABLE 삭제
DROP USER USER1;
DROP USER USER1 CASCADE; (가지고 있는 모든 파일 삭제)	DROP TABLE A_TBL;

 

관리자 권한 주기

GRANT DBA TO USER [DBA(DataBase Administrator)]

* 테이블 보기

SELECT TABLE_NAME FROM DBA_TABLES;

 

* SQL DEVELOPER 다운로드 페이지https://www.oracle.com/tools/downloads/sqldev-downloads-2041.html

Oracle SQL Developer Downloads 20.4.1

 

사용자 권한 기본 제공 role

- CONNECT : 데이터베이스 접속 권한

- RESOURCE : 기본적인 데이터베이스

 

▶ 관리자 계정

sys안에 system이 있다고 생각하면 된다.

sys	/ as sysdba
system	/ as sysoper

- db연습은 관리자 계정이 아닌 사용자 계정으로 연습해야 한다.(user1)

 

칼럼을 확인할 때는, describe 명령어를 쓸 수 있다.

describe dba_users; (= desc dba_users;)

- db에서는 '열'을 '칼럼'이라고 한다.

 

dba_users 테이블로부터 user name을 선택할수 있게 보여달라

 

 

 

▶ SCOTT 계정 

- Oracle 데이터베이스를 배포할 때 미리 만들어 둔 계정

 - 연습할 때 필요한 샘플 데이터가 포함되어 있음

 - 잠김(LOCK) 상태이므로 잠금을 해제해야 함.

SYS 계정 접속

SYS / AS SYSDBA

 

SCOOTT 계정 잠금 해제

ALTER USER SCOTT IDENTIFIED BY ACCOUNT UNLOCK

* scott.sql

 - SCOTT 계정의 활성화를 위한 쿼리문이 존재하는 파일

 - SCOTT 계정의 샘플 데이터를 생성하기 위한 쿼리문도 함께 존재함

 - 파일 경로

  C\oraclexe\app\oracle\product\11.2.0\server\rdnms\admin\scott.sql

 

* scott.sql 파일 실행

 - sqlplus 실행하고 아래 명령 입력

   @C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\scott.sql

 

* SCOTT 계정으로 접속 되는지 확인

 - sqlplus 실행하고 아래 명령 입력

  CONN SCOTT/TIGER

 

▶ HR계정 (주로 사용 예정) 

 - Oracle 데이터베이스를 배포할 때 미리 만들어 둔 교육용 계정

 - 연습할 때 필요한 샘플 데이터가 포함되어 있음

 - 잠김(LOCK) 상태이므로 잠금을 해제하여야 함

 - SCOTT 계정과 달리 특정 .sqp 파일을 실행할 필요가 없음

 - SCOTT 계정보다 더 많은 샘플 데이터 포함

 

* HR 계정에 포함된 테이블

 - EMPLOYEES

 - DEPARTMENTS

 - LOCATIONS

 - COUNTRIES

 - REGIONS

 - JOBS

 - JOB_HISTORY

관리자 계정 접속

CONN SYS / AS SYSDBA

HR계정 잠금 해제

ALTER USER HR ACCOUNT UNLOCK;

HR 계정 비밀번호 변경

ALTER USER HR IDENTIFIED BY 1111;

HR 계정 접속 확인

CONN HR / 1111

 

**

Run SQL Command Line 명령창에서 실행

1. 접속
 - CONN SYSTEM/1111

2. 계정 생성
 - GRANT CONNECT TO 이름; (이름 = HR, SCOTT)

3. 권한주기
 - GRANT CONNECT, RESOURCE TO 이름; (이름 = HR, SCOTT)

4. 비밀번호 생성
 - ALTER USER 이름 IDENTIFIED BY 비밀번호;

5. SYS계정 접속 후, 잠금 해제
 - CONN SYSTEM/1111
 - ALTER USER 이름 IDENTIFIED BY ACCOUNT UNLOCK

(위 명령은 Oracle SQL Developer - SYS계정 또는 SYSTEM계정 에서도 사용 가능함)

 

* LOCK 풀기

 - ALTER USER 이름 ACOOUNT UNLOCK;