[ORACLE] sys계정_사용자관리 / 테이블스페이스 / 사용자 계정 / data dictionary

※ 오라클 데이터베이스의 논리적 저장 구조

 

오라클 : 데이터를 저장하고 추출, 삭제, 변경하는 작업을 하며, 데이터는 파일에 저장됨

오라클 데이터베이스 : 데이터파일들을 가지고있으며, 이 파일들에 데이터가 저장됨

 

1) 데이터블럭(Data block) - 최소 저장단위
   데이터블럭의 default size : 8K

 

2) 익스텐트(Extent) - 8개의 데이터블럭이 모여서 하나의 익스텐트가 됨

3) 세그먼트(Segment) - 하나 이상의 익스텐트로 구성

4) 테이블스페이스(Tablespace) - 세그먼트들을 저장하는 논리적인 공간 이름

 - 세그먼트가 모여서 테이블스페이스가 됨. (테이블, 인덱스, 프로시저, 뷰 등 여러 오 라클 객체들이 저장됨)

 

5) 데이터베이스 - 테이블스페이스(Tablespace)들의 모임을 데이터베이스라고 함
하나의 테이블스페이스는 최소 1개의 데이터파일(물리적 파일)로 구성됨

 

 

---

 

※ 테이블스페이스 TABLE SPACE

 

▶ 테이블 스페이스 생성

   - sys나 system 계정으로 로그인해서 사용자 생성, 테이블스페이스 생성하기

create tablespace 테이블스페이스명
datafile '절대경로\파일명' size 크기
AUTOEXTEND ON NEXT 크기(M); --자동 증가옵션

create tablespace tb_test1
datafile 'C:\mydata\tb_test1.dbf'size 48m -- c드라이브 -> mydata 먼저 폴더만들고 경로 복붙.                                       
autoextend on next 10m;

C:\mydata\tb_test1.dbf = > 반드시 테이블스페이스가 들어갈 폴더를 먼저 만들어놓고 경로지정한다

                        tb_test1.dbf : 내가 정한 테이블스페이스 안의 파일명. 테이블스페이스 명과 동일하게 하는게 일반적

 

 

 

▷ 테이블스페이스 하나에 여러개의 파일만들수도있음

create tablespace tb_test2
datafile 'C:\mydata\tb_test2_01.dbf' size 48m
autoextend on maxsize 1000m,
'C:\mydata\tb_test2_02.dbf' size 48m
autoextend on maxsize 1000m,
'C:\mydata\tb_test2_03.dbf' size 48m
autoextend on maxsize 1000m;

 

 

▶ 생성된 테이블스페이스와 데이터파일 조회하기

select* from dba_data_files;

 

 

▶ 테이블 스페이스 제거

drop tablespace 테이블스페이스명;  // 논리적인 테이블스페이스만 삭제
drop tablespace 테이블스페이스명
        including contents and datafiles; // 물리적인 데이터파일까지 삭제 (파일안에있는거)

drop tablespace tb_test2
    including contents and datafiles;

 

---

※ 사용자 계정 USER

 

▶ 사용자 계정 생성

alter session set "_ORACLE_SCRIPT"=true;
    create user 사용자이름     
    identified by 비밀번호
    default tablespace 테이블스페이스명; // 어떤 테이블스페이스를 사용하는 유저인지

 alter session set "_ORACLE_SCRIPT"=true;            ! ! 반드시 얘 먼저 실행 후 계정생성하기

 : 오라클 12C부터 계정생성할때 C##이렇게 복잡하게 써야 함 복잡하게 사용하지 않기위해 세션을 변경해줌

 

alter session set "_ORACLE_SCRIPT"=true;
create user testuser1
identified by testuser123
default tablespace tb_test1;

 

 

 

▶ 사용자 계정이 가지고 있는 권한 조회 

  - ' '  안의 사용자이름은 대문자로 입력해야 조회되던데

 

▷ 권한조회

SELECT * from dba_role_privs
where grantee = 'TESTUSER1';

▷ 롤조회

select * from dba_sys_privs 
where grantee = 'TESTUSER1';

 

 

▶ 사용자에게 권한 부여하기

grant 권한 to 사용자명;

▷ create session 권한 부여(접속 권한)

grant create session to testuser1;

 

▷ 테이블생성 권한 부여

grant create table to testuser1;

▷ 뷰생성 권한 부여

grant create view to tuser1;

 

 

▷ 다른 계정의 테이블 조회하는 권한부여

grant select on hr.employees to testuser3;

 

▷ 다른 계정의 테이블을 수정하는 권한부여

grant update on hr.employees to testuser3;

 

▷ 사용자에게 resource, connect 롤 부여하기

grant resource, connect to testuser3;

 ◈ 롤(Role) 

 - 권한의 그룹 (여러가지 권한을 묶어놓은 집합) 

 -  롤 속에 여러 가지 권한을 넣어 두고 사용자에게 롤 하나를 주면 그 안에 있는 모든 권한을 다 받게 되는 것

 - 롤을 직접 생성해서 사용자에게 부여할 수도 있음.

    1. 롤 생성  // create role testrole;

    2. 롤에 권한부여 // grant create session to testrole;

    3. 사용자계정에 생성한 롤을 부여 // grant testrole to testuser4; 

    => TESTUSER4 사용자에게 testrole 롤을 부여했다 = testuser4 은 create session 권한을 부여받음

 

 

▶ 사용자에게 권한박탈하기

revoke 권한 from 사용자명;

revoke create table from testuser1;

 

▷ 다른 계정의 테이블을 수정하는 권한박탈

revoke update on hr.employees from testuser3;

 

▶ 권한 위임

  - 권한을 위임하는 기능

  - 또 다른 사용자에게 권한을 할당해 줄 수 있게 됨

  - object 권한에서 사용

with grant option

 

▶ 사용자 삭제

DROP USER 사용자이름 ;
DROP USER 사용자이름 CASCADE;

CASCADE : 해당 사용자가 생성한 object (sequence, index, table, function) 들도 같이 삭제해줌

   - 사용자가 생성한 Object(index,table) 가 있을땐 drop 시 cascade 사용

 

 

 

▶ 계정 변경

 

▷ 계정 상태확인

select * from dba_users
where username like'%TESTUSER4%';

 

▷ 잠긴 계정 열기

alter user 사용자ID account unlock;

▷ 계정 잠그기

alter user 사용자ID account lock;

▷ 기존 계정의 암호 변경하기

alter user 사용자ID identified by 새로운암호;

 

---

※ data dictionary

   - 데이터베이스 내에 저장된 모든 객체의 정보를 제공해 주는 테이블

 

▶ Data Dictionary의 종류
   

   1) DBA_XXX : 데이터베이스 관리를 위한 정보를 제공
    2) User_XXX : 자신이 생성한 object 정보를 제공
                  현재 데이터베이스에 접속한 사용자가 소유한 객체의 정보를 제공
    3) ALL_XXX : 자신이 생성한 object와 다른 사용자가 생성한 object 중에 자신이 볼 수 있는 정보를 제공
                 - 사용자가 접근 가능한 모든 스키마의 정보를 제공
                 - 권한을 부여받음으로써 가능

 

 

▷ user_xxx

select * from user_objects; --user 가 만든 모든 objects
select * from user_tables; -- `` tables
select * from user_constraints;
select * from user_indexes;
select * from user_sequences;
select * from user_views;
select * from user_source;

 

▷ all_xxx

select owner, table_name 
from all_tables
where table_name = 'EMPLOYEES'

 

▷ DBA_XXX

select * from dba_users;
select * from dba_data_files;
select * from dba_tablespaces;
select * from dba_roles;
select * from dba_sys_privs;
select * from dba_role_privs;