[211004] SYNONYM 복습과 계정의 분류

SYNONYM 

[211004 수업 복습]

-- 시스템에 설치된 오라클 DB 사용할 것이기 때문에 (SCOTT.XEPDB1) --반드시 시스템 계정으로 접속하여 유저를 생성, 권한 부여해준다.  CREATE USER SCOTT IDENTIFIED BY Database4321; --권한 부여 GRANT CONNECT, RESOURCE     , INSERT ANY TABLE, UPDATE ANY TABLE     , DELETE ANY TABLE, CREATE VIEW     TO SCOTT; --테이블스페이스(USERS) 사용 권한 부여      50메가바이트에 대한 공간을 테이블이 사용할 수 있게 부여  ALTER USER SCOTT quota 50M ON USERS; -- restricted session 권한 부여 (DB 연동시 권한 오류 나서) grant restricted session to SCOTT;  -- 계정 잠금 해제 alter user SCOTT ACCOUNT unlock; DROP USER SCOTT; -- 시너넘(동의어) -- 비공개 동의어 생성, SCOTT 계정으로 들어가서 생성하면 권한 없어서 안 된다. CREATE SYNONYM ACCS FOR ACCOUNTS;  DROP SYNONYM ACCS; -- 시스템 계정으로 접속해서 권한 먼저 부여 (선행작업) GRANT CREATE SYNONYM TO SCOTT; GRANT CREATE PUBLIC SYNONYM TO SCOTT;  -- 공개 동의어 생성 권한 GRANT DROP PUBLIC SYNONYM TO SCOTT; -- 권한삭제 REVOKE CREATE PUBLIC SYNONYM FROM SCOTT; --조회 SELECT * FROM ACCS; --  에러 synonym translation is no longer valid, ACCOUNTS 테이블 생성도 안 하고 시너넘 만든 상태기 때문 SELECT * FROM USER_SYNONYMS;  -- 이걸로나마 볼 수 있음 -- 타계정 조회시 비공개 시너넘이라서 볼 수 없다.  -- 예시로 DEVELOP1 계정 부적절, 수업시간에 생성한 ACCS 정상적으로 보여줄 것이기 때문 SELECT * FROM ACCS;  -- 공개 동의어 생성 (유저계정으로 접속해서 생성 불가 시스템 계정으로 공개 동의어 생성 권한 부여해도 X) -- SYS 사용자로부터 DBA권한을 받은 사용자(SYSTEM 관리자 계정:SYSTEM.XEPDB1 또는 ADMIN.testdb) 만이 생성 가능 CREATE PUBLIC SYNONYM ACCS2 FOR ACCOUNTS; -- ACCOUNTS 테이블 시스템 계정에 만들기 전엔 조회(사용) 불가 DROP PUBLIC SYNONYM ACCS2; SELECT * FROM ACCS2; CREATE PUBLIC SYNONYM ATCS FOR USER1.ACCTYPES; -- SYSTEM 계정으로 접속해서 일반 사용자 계정인 USER1의 테이블 시너넘을 만들면 조회(사용) 불가능 하다.                                                -- ACCTYPES 테이블이 애초에 USER1 계정으로 생성 되었고 오라클 클라우드 DB 서버에 저장되어 있기 때문이다.                                                -- 즉, 로컬 DB 서버에 저장된 테이블이 아니므로 로컬 계정으로 시너넘 생성하면 그걸 넘어서 조회할 수 없는 것이다.  -- 따라서 ADMIN 계정(오라클 클라우드의 관리자 계정)으로 접속하여 시너넘 만들면 정상 조회가 된다. -- 다른 계정인(물론 오라클 클라우드 계정이어야 한다.) DEVELOP1 으로 접속해도 사용 가능 - 공개 동의어 CREATE PUBLIC SYNONYM ATCS FOR USER1.ACCTYPES; DROP PUBLIC SYNONYM ATCS; SELECT * FROM ATCS; -- 참고용 --ACCOUNTS 테이블 생성 DROP TABLE ACCOUNTS; CREATE TABLE ACCOUNTS (        ID        NUMBER      , AMOUNT    NUMBER      , INOUT     CHAR(1)      , ACC_DATE  DATE      , ACC_TYPE  CHAR(3) ); COMMENT ON COLUMN ACCOUNTS.ID IS 'ACCOUNTS 테이블의 행 데이터를 식별하기 위한 값이 저장되는 컬럼이다.'; COMMENT ON COLUMN ACCOUNTS.AMOUNT IS '입/출 금액을 저장하기 위한 컬럼이다.'; COMMENT ON COLUMN ACCOUNTS.INOUT IS '입금(I), 출금(O) 을 구분하기 위한 컬럼으로 저장 값은 I, O 로 제한한다.'; COMMENT ON COLUMN ACCOUNTS.ACC_DATE IS '입/출 금액이 발생한 일자를 저장 하기 위한 컬럼이다.'; COMMENT ON COLUMN ACCOUNTS.ACC_TYPE IS '입/출 금액의 용도를 별도로 구분하기 위한 컬럼으로 참조 테이블(ACCTYPES)     의 ID 컬럼의 값을 참조한다.'; ALTER TABLE ACCOUNTS ADD CONSTRAINT ACCOUNTS_ID_PK PRIMARY KEY(ID); ALTER TABLE ACCOUNTS MODIFY AMOUNT CONSTRAINT ACCOUNTS_AMOUNT_NN NOT NULL; ALTER TABLE ACCOUNTS ADD CONSTRAINT ACCOUNTS_INOUT_CK CHECK(INOUT IN('I', 'O')); -- ALTER TABLE ACCOUNTS MODIFY INOUT CHECK(INOUT IN(NULL));  제약조건 수정, NULL값으로 CHECK하기도 가능하다! ALTER TABLE ACCOUNTS MODIFY ACC_DATE CONSTRAINT ACCOUNTS_ACC_DATE_NN NOT NULL; ALTER TABLE ACCOUNTS ADD CONSTRAINT ACCOUNTS_ACC_TYPE_FK FOREIGN KEY(ACC_TYPE) REFERENCES ACCTYPES(ID);  --  ㄴ외래키로 쓸 테이블(ACCTYPES) 생성하고 나서야 외래키 설정 가능하다. -- 기본값 설정 -- 뷰 인서트 작업 수월하게 하기 위해 디폴트값 넣어 준다. ALTER TABLE ACCOUNTS MODIFY INOUT DEFAULT('O'); -- CHECK도 조건설정한 값만 허용하고 NULL값은 허용 안하므로 기본값 설정해둔다. ALTER TABLE ACCOUNTS MODIFY ACC_DATE DEFAULT(SYSDATE); -- ACCTYPES 테이블 생성 DROP TABLE ACCTYPES; CREATE TABLE ACCTYPES (       ID        CHAR(3)     , TYPE_NAME VARCHAR2(150) ); COMMENT ON COLUMN ACCTYPES.ID IS 'ACCTYPES 테이블의 행 데이터를 식별하기 위한 값이 저장되는 컬럼이다.'; COMMENT ON COLUMN ACCTYPES.TYPE_NAME IS '입/출 금액의 용도가 저장되는 컬럼이다.'; -- ALTER TABLE ACCTYPES ADD CONSTRAINT ACCTYPES_ID_PK PRIMARY KEY(ID); -- ALTER TABLE ACCTYPES MODIFY TYPE_NAME CONSTRAINT ACCTYPES_TYPE_NAME_NN NOT NULL; INSERT INTO ACCTYPES VALUES('I01','급여'); INSERT INTO ACCTYPES VALUES('O02','교통비'); INSERT INTO ACCTYPES VALUES('O04','식비'); INSERT INTO ACCTYPES VALUES('I02','복권당첨'); INSERT INTO ACCTYPES VALUES('O01','통신비'); INSERT INTO ACCTYPES VALUES('O03','주유비'); INSERT INTO ACCTYPES VALUES('O05','여가비'); INSERT INTO ACCTYPES VALUES('O06','가전제품'); SELECT * FROM ACCTYPES; -- 공개 시너넘인데 조회 시 권한 없음으로 나올 경우, 조회 권한 부여 후 재조회 grant select any table to DEVELOP1; grant select any table to USER1; GRANT SELECT ON SCOTT.ACCTYPES TO DEVELOP1; GRANT SELECT ON SCOTT.ACCTYPES TO USER1; -- cf. 권한 부여하는 새로운 방식  CREATE ROLE C_ROLE; GRANT CONNECT, RESOURCE TO C_ROLE; GRANT C_ROLE TO DEVELOP1; GRANT C_ROLE TO USER1;

 

---

 

계정의 분류

 

- 사용자 계정 : 논리적인 의미

  

  - SYS계정

      : 데이터베이스내의 모든 권한을 갖는 최상위 레벨의 사용자

      

  - SYSTEM 계정

      : SYS 사용자로부터 "DBA권한"을 받은 사용자

      . 새로운 사용자를 추가, 변경, 삭제할 수 있다.

      . 사용자별 공간할당, 패스워드관리, 세션관리 등을 할 수 있다.

      . 데이터베이스 오브젝트(테이블, 뷰, 트리거...)는 "사용자별로" 생성된다.

      . 데이터베이스 오브젝트를 생성한 사용자를 그 오브젝트의 소유자(owner)라 한다.

 

계정의 분류 출처
https://rongscodinghistory.tistory.com/114