User 생성, User 삭제 실습

데이터베이스 스키마

오라클에서 스키마 = 유저 MYSQL에서 스키마 = 데이터베이스 유저(st01) 에서 로그인 해서 테이블을 만드는 것 토탈해서 st01 스미카라고 부름

 

User 생성 실습

사용 명령어 SQL> CREATE USER <user 명>    2  IDENTIFIED BY <암호>   3  DEFAULT TABLESPACE <tablespace 명> * User의 기본 tablespace   4  TEMPORARY TABLESPACE <tablespace 명> * 별도 지정 안해도 됨   5  QUOTA <크기> ON <tablespace 명>, ...... * tablespace별 허용된 저장량   6  [ACCOUNT {LOCK / UNLOCK}] 계정 잠금 설정   7  [PROFILE {<profile명> / DEFAULT}] * 적용될 PROFILE 지정 SQL> SELECT username, tablespace_name, max_bytes, max_blocks FROM dba_ts_quotas; * 각 사용자의 tablespace별 quota량을 조회한다 * MAX_BYTES : 할당된 quota량 (byte단위) * MAX_BLOCKS : 할당된 quota량 (block개수) * quota가 unlimited로 지정되어 제한하지 않는 경우 -1로 표시된다

[조회 명령어] SELECT tablespace_name, bytes, file_name FROM dba_data_files; * dba_data_files 뷰에서 테이블스페이스 이름, 파일 크기, 파일 경로를 조회하는 명령어 [테이블 스페이스 생성 명령어] - te 이전에 te01.dbf / 10MB 가 있던 상태라 ALTER DATABASE DATAFILE '/app/ora19c/oradata/disk1/te01.dbf' RESIZE 30M; * te01.dbf 크기 변경 ALTER TABLESPACE te  ADD DATAFILE '/app/ora19c/oradata/disk2/te02.dbf' SIZE 30M; * te0.2dbf 추가 [테이블 스페이스 생성 명령어] - st 이전에 st01, st02.dbf가 10MB 씩 있던 상태라 ALTER DATABASE DATAFILE '/app/ora19c/oradata/disk1/st01.dbf' RESIZE 30M; ALTER DATABASE DATAFILE '/app/ora19c/oradata/disk2/st02.dbf' RESIZE 30M; * 두 파일 사이즈 30M로 변경함 [테이블 스페이스 생성 명령어] - st 이전에 indx01,02.dbf가 10MB 씩 있던 상태라  ALTER DATABASE DATAFILE '/app/ora19c/oradata/disk1/indx01.dbf' RESIZE 20M; ALTER DATABASE DATAFILE '/app/ora19c/oradata/disk2/indx02.dbf' RESIZE 20M; * 두 파일 사이즈 20M로 변경함 SELECT tablespace_name, bytes, file_name FROM dba_data_files; [명령어로 세 파일 확인] SELECT tablespace_name, bytes, file_name FROM dba_data_files; SELECT tablespace_name, file_name, bytes/1024/1024 MB FROM dba_data_files WHERE tablespace_name IN ('TE', 'ST', 'INDX'); * 세 파일의 MB가 정상적으로 수정되었음을 확인함 [테이블스페이스의 상태, 타입, 익스텐트/세그먼트 관리 방식을 조회하는 명령어]   SELECT tablespace_name, status, contents, extent_management, segment_space_management FROM dba_tablespaces; [테이블스페이스 생성 후 유저 생성 및 권한 할당] [te 유저 생성] CREATE USER te IDENTIFIED BY te DEFAULT TABLESPACE te QUOTA UNLIMITED ON te QUOTA 2M ON st QUOTA 2M ON indx; DEFAULT TABLESPACE : 기본 저장 공간 QUOTA : 각 테이블스페이스 사용 한도 [st0 유저 생성] CREATE USER st0 IDENTIFIED BY st0 DEFAULT TABLESPACE st; [st1 유저 생성] CREATE USER st1 IDENTIFIED BY st1 DEFAULT TABLESPACE st QUOTA UNLIMITED ON st QUOTA 2M ON indx; [st2 유저 생성] CREATE USER st2 IDENTIFIED BY st2 DEFAULT TABLESPACE st QUOTA UNLIMITED ON st QUOTA 2M ON indx ACCOUNT LOCK; QUOTA UNLIMITED ON st : ST 테이블스페이스 용량 무제한 사용 QUOTA 2M ON indx : INDX 테이블스페이스는 2MB까지만 사용 [접속 및 권한 부여] GRANT connect, resource TO te, st1, st2; [명령어로 확인] SELECT username, default_tablespace, temporary_tablespace, account_status, profile FROM dba_users ORDER BY username; [특정 유저들의 테이블스페이스 할당량 조회 명령어] SELECT username, tablespace_name, max_bytes, max_blocks FROM dba_ts_quotas WHERE username IN ('TE', 'ST0', 'ST1', 'ST2') ORDER BY 1; [특정 유저에게 부여된 롤(권한묶음)을 조회 명령어] SELECT grantee, granted_role FROM dba_role_privs WHERE grantee IN ('TE', 'ST0', 'ST1', 'ST2') ORDER BY 1; grantee : 권한을 받은 유저 granted_role : 부여된 롤 (CONNECT, RESOURCE 등) dba_role_privs : 유저별 롤 권한 정보 뷰 [특정 유저에게 부여된 시스템 권한을 조회하는 명령어] SQL> SELECT grantee, privilege FROM dba_sys_privs WHERE grantee IN ('TE', 'ST0', 'ST1', 'ST2') ORDER BY 1;  2    3    4 no rows selected * GRANT 때 connect, resource는 롤이지 시스템 권한이 아니라서 dba_sys_privs 에는 안나옴  [특정 유저에게 부여된 오브젝트 권한을 조회하는 명령어] SELECT grantee, owner, table_name, grantor, privilege FROM dba_tab_privs WHERE grantee IN ('TE', 'ST0', 'ST1', 'ST2') ORDER BY 1; * 오브젝트 권한을 부여한 적이 없어서 no rows 가 나옴 [st1 유저로 접속하는 명령어] CONN st1/st1 [st2 유저로 접속하는 명령어] SQL> CONN st2/st2 * st0는 lock이 설정되지는 않았지만 'CREATE session' 권한이 없어서 접속할 수 없다 [sysdba 권한으로 접속하는 명령어] CONN / AS sysdba [st0에 create session 권한을 할당] [st2 계정 잠금 해제] ALTER USER st2 ACCOUNT UNLOCK; [st0 유저로 접속하는 명령어] CONN st0/st0 [st2 유저로 다시 접속] CONN st2/st2

[Oracle DB - SYS]
    |
    ├── [TE 테이블스페이스]
    │       ├── te01.dbf (30M)
    │       └── te02.dbf (30M)
    │
    ├── [ST 테이블스페이스]
    │       ├── st01.dbf (30M)
    │       └── st02.dbf (30M)
    │
    └── [INDX 테이블스페이스]
            ├── indx01.dbf (20M)
            └── indx02.dbf (20M)

[유저]
    ├── te   → DEFAULT TS: TE
    │           └── (테이블 생성 공간)
    ├── st0  → DEFAULT TS: ST
    │           └── st0 테이블
    ├── st1  → DEFAULT TS: ST
    └── st2  → DEFAULT TS: ST (UNLOCK)

User 삭제 실습

사용 명령어 User 삭제 SQL> DROP  USER <user명> [CASCADE] * User를 삭제하기 전 스키마의 모든 segment를 먼저 삭제 * st0에 테이블 생성 되어 있어야 함 CONN st0/st0 CREATE TABLE st0 (id NUMBER); [sysdba 권한으로 접속하는 명령어] CONN / AS sysdba [테이블 확인] SELECT owner, table_name FROM dba_tables WHERE owner = 'ST0'; [st0 DROP] DROP USER st0; * st0 스키마는 table이 존재함으로 삭제되지 않는다 [DROP TABLE st0.st0;] DROP TABLE st0.st0; * sysdba는 모든 권한이 있어서 st0.st0는 sysdba를 접속한 상태에서 유저명.테이블명 형식으로 삭제한 것임 [table 삭제하고, user 삭제] DROP USER st0; [cascade 옵션은 segment 존재 유무와는 상관없이 user를 삭제] [st0 삭제 확인] SELECT username, default_tablespace, temporary_tablespace, account_status, profile 2  FROM dba_users 3  ORDER BY 1;

참고사항(테이블 만들 때) sysdba로 접속 시  CREATE TABLE st0.st0 (id NUMBER); st0으로 접속 시  CREATE TABLE st0 (id NUMBER); [ST 테이블스페이스 도식화]     └── st01.dbf / st02.dbf             |         [st0 유저]             |         [st0 테이블]             |         ├── id         ├── name         └── ...  (컬럼들)                 |             [데이터 row]             ├── 1, 홍길동, ...             ├── 2, 김철수, ...             └── ...