[SQL] Day 5 - 권한

윤수인·2023년 12월 20일
0

📒국비학원 [DB]

목록 보기
7/14
post-thumbnail

ROLE

제약조건 (CONSTRAINT)

  • 추가 (ALTER) / 삭제 (DROP)만 가능
  • why? 무결성때문에 ( 데이터의 안정성 척도 )
  • 이것들을 고려하고 만드는게 '데이터베이스 모델링'
    • PRIMARY KEY
    • UNIQUE KEY
    • FOREIGN KEY
    • NOT NULL KEY
    • CHECK KEY
    • DEFAULT KEY (오라클에선 인정 X 그러나 내부적으로는 처리 가능)

💻 작업

1. ROLE 권한

  • ROLE
    : 그릇에 (system create - 관리자 / object - 특정 USER ) 권한을 집어넣는것

  • 오라클에서 나갔다가 들어와야 권한 정상적으로 실행가능

USER SQL

CREATE ROLE MANAGER; --그래서 충분한 권한이 아니라고 에러뜸


SELECT * FROM USER_ROLE_PRIVS; --KIM이라는 사용자가 가지고있는 ROLE 확인(CONNECT / RESOURCE) 하는 명령어

SELECT * FROM ROLE_SYS_PRIVS; -- 그 롤안에는 어떤 권한들이 있는지 확인하는 명령어

CREATE ROLE KIMOBJ; --KIM은 롤 만들 수 있는 권한 없음 - SYSTEM권한 없음 - SYSTEM한테서 DBA를 받아야함 - 받았음
--시스템 권한은 바로 반영 - 그래서 바로 반영됨 / ROLE에 담아서 권한 주면 한번 나갔다 들어와야함 

GRANT SELECT, INSERT, DELETE ON DIVISION TO KIMOBJ; -- KIMOBJ 그릇에 (SELECT, INSERT, DELETE) 권한들 부여 

GRANT KIMOBJ TO edam;

REVOKE KIMOBJ FROM edam;

SYSTEM SQL

CREATE ROLE MANAGER; -- ROLE 그릇 만들기

SELECT * FROM DBA_ROLES; -- ROLE (CONNECT / RESOURCE / DBA ...)이 잘 만들어졌는지 확인

--여러 권한 써도됨
GRANT CREATE USER TO MANAGER; --MANAGER안에 CREATE USER 권한 부여

SELECT * FROM ROLE_SYS_PRIVS --ROLE안에  권한 확인
WHERE ROLE = 'MANAGER'; -- MANAGER안에 CREATE USER 권한 부여됐는지 확인


--특정사용자에게 ROLE권한 넘기는 법
GRANT MANAGER TO INNA; -- INNA한테 MANAGER ROLE (CREATE USER) 권한 쓸 수 있게 줌 

--권한뺏기 
REVOKE MANAGER FROM INNA;


SELECT * FROM DBA_USERS; -- 계정확인법

GRANT CONNECT, MANAGER, UNLIMITED TABLESPACE TO edam;

SELECT * FROM USER_ROLE_PRIVS WHERE ROLE = 'CONNECT';


SELECT * FROM USER_ROLE_PRIVS; -- DBA를 받아서 관리자가 된거임
SELECT * FROM ROLE_SYS_PRIVS; 

GRANT DBA TO KIM; -- 이렇게 주면 KIM이 관리자가 되는거임
GRANT CREATE ROLE TO KIM; --SYSTEM이 KIM한테 권한을 주면 됨

2. 제약조건(CONSTRAINT)

  • (PK : PRIMARY KEY )

TABLE 만드는 법 (1)

CREATE TABLE CUSTOMER
(ID NUMBER(4) CONSTRAINT CUSTOMER_ID_PK PRIMARY KEY,
NAME VARCHAR2(10),
NO NUMBER(4));

TABLE 만드는 법 (2)

CREATE TABLE ORDERS
(NO NUMBER(4),
SNO NUMBER(4),
NAME VARCHAR2(10),
COUNT NUMBER(7),
CONSTRAINT ORDERS_NO_PK PRIMARY KEY(NO));

SELECT * FROM USER_CONSTRAINTS; -- RK 잘 만들어졌는지 확인


SELECT * FROM CUSTOMER;
SELECT * FROM ORDERS;

  • (FK - FOREIGN KEY ) 포린키

  • 참조하는 컬럼에 FK키 만들기

ALTER TABLE CUSTOMER
ADD CONSTRAINT  CUSTOMER_NO_FK FOREIGN KEY(NO) -- CUSTOMER의 NO
REFERENCES ORDERS(NO);

SELECT * FROM USER_CONSTRAINTS;



-->  ORDERS에 가서 참조해야하는데 1이 ORDERS에 데이터가 없으므로 불가

SELECT * FROM ORDERS;
SELECT * FROM CUSTOMER;

INSERT INTO ORDERS VALUES (1,10,'AAAA',10);
INSERT INTO ORDERS VALUES (2,20,'BBBB',20);
INSERT INTO ORDERS VALUES (3,30,'CCCC',30);

SELECT * FROM ORDERS;

INSERT INTO CUSTOMER VALUES (20,'AAA',4);
SELECT * FROM CUSTOMER; --> 참조한 키에도 1이 있으니까 참조되어지는 키를 통해 데이터 넣으면 들어감


CREATE TABLE CATALOGS
(CATALOGNO NUMBER(4) CONSTRAINT CATALOGS_CATALOGNO_PK PRIMARY KEY, 
NAME VARCHAR2(10),
NO NUMBER(4) CONSTRAINT CATALOGS_NO_FK REFERENCES ORDERS(NO));-- 포린키 필요없음 ( ORDER이 CUSTOMER껄 참조 FK하므로)


SELECT * FROM USER_CONSTRAINTS;
SELECT * FROM CATALOGS;

INSERT INTO CATALOGS VALUES (111,'AAA',1); -- O
INSERT INTO CATALOGS VALUES (222,'AAA',4); -- X

  • (UK - UNIQUE KEY )

  • 중복값 허용 X
  • NULL 값 허용 ㅇ
SELECT * FROM ORDERS;
DESC ORDERS;

ALTER TABLE ORDERS --이미 만들어진 테이블에 넣을 땐 ALTER TABLE
ADD CONSTRAINT ORDERS_SNO_UK UNIQUE(SNO);

INSERT INTO ORDERS VALUES (4,NULL,'DDDD',40); -- 명시적NULL 삽입
INSERT INTO ORDERS VALUES (5,NULL,'EEEE',40); -- 명시적NULL 삽입

  • (CK - CHECK KEY ) 


SELECT * FROM ORDERS;

ALTER TABLE ORDERS 
ADD CONSTRAINT ORDERS_SNO_CK CHECK(SNO BETWEEN 1 AND 100);

INSERT INTO ORDERS VALUES (6,100,'FFFF',60); --100까지 포함

  • (NN - NOT NULL ) 

CREATE TABLE TEST1
(NO NUMBER(4) CONSTRAINT TEST1_NO_PK PRIMARY KEY,
NAME VARCHAR2(10) CONSTRAINT TEST1_NAME_NN NOT NULL);

INSERT INTO TEST1 VALUES (10,'AAA');

SELECT * FROM TEST1;

INSERT INTO TEST1 (NO) VALUES (20); -- NAME은 NN이므로 에러뜸

3. 제약조건 - 추가/삭제/비활성화

  • 추가 (ALTER) / 삭제 (DROP) 

ALTER TABLE CUSTOMER
DROP CONSTRAINT CUSTOMER_ID_PK; --제약조건의 이름으로 지우기

ALTER TABLE CUSTOMER
DROP PRIMARY KEY; --프라이머리 키는 한개라서 이렇게만 써도됨

ALTER TABLE ORDERS
DROP PRIMARY KEY CASCADE; --에러 : 참조하는 FK키때문에 / 그러나, CASCADE로 한번에 지울 수 있음

SELECT * FROM USER_CONSTRAINTS; -- 다 지워진 제약조건 확인

  • 비활성화 (DISABLE) / 활성화 (ENABLE)

  • why? 테이블에 데이터 하나 넣는 순간 그 값을 가지고가서 확인하니까 일일히 체크하다보니까 집어넣는 속도 너무 오래걸림 ->
    -- 그래서 많은 양의 데이터를 집어넣을 땐 잠깐 비활성화 시킴
    -- 데이터 일단 집어넣고 활성화시켰을 때 아무 문제 없음 -> 중복되는 데이터 없음 / 문제생기면 데이터 있음

- 비활성화 (DISABLE) 

SELECT * FROM PERSONNEL;
SELECT * FROM DIVISION;


ALTER TABLE DIVISION
DISABLE PRIMARY KEY CASCADE;

SELECT * FROM PERSONNEL;

INSERT INTO PERSONNEL (PNO,DNO) VALUES (1234,50);

- 활성화 (ENABLE) 

ALTER TABLE DIVISION
ENABLE PRIMARY KEY;

ALTER TABLE PERSONNEL
ENABLE CONSTRAINT PERSONNEL_DNO_FK; -- 에러 WHY? 50이 없는 데이터가 발견돼서!

DELETE PERSONNEL WHERE DNO = 50;

4. DICTIONARY

  • DATABASE DICTIONARY
    : 정보를 저장해놓은 TABLE / VIEW

  • USER_ : USER 소유의 OBJECT에 관한 정보

  • ALL_ : USER에게 ACCESS가 허용된 OBJECT에 관한 정보

  • DBA_ : DBA권한을 가진 USER가 ACCESS할 수 있는 정보

  • V$_ : SERVER 성능에 관한 정보

SELECT * FROM USER_CONSTRAINTS; --제약조건의 정보를 보는 사전

SELECT * FROM DICTIONARY;

SELECT * FROM V$VERSION;

SELECT * FROM ALL_CONS_COLUMNS; --나에게 주어진 다른 TABLE에 대한 정보
SELECT * FROM USER_CONS_COLUMNS; -- 자기자신의 제약조건에 대한 정보

SELECT * FROM USER_TABLES; --자기가 만든 테이블에 대한 정보
SELECT * FROM USER_TAB_PRIVS; 
SELECT * FROM USER_SYS_PRIVS; --어떤 시스템 권한을 가지고 있는지?

5. ⭐ VIEW

  • 뷰 : view DICTIONARY

  • 가상테이블
  • 보안목적
CREATE VIEW PER10_VU
AS
SELECT * FROM PERSONNEL WHERE DNO = 10;  -- 뷰에 사용권한을 줌 


SELECT * FROM PER10_VU; -- 디비에 저장되어있는게 아니라 , 이거 실행할때마다 위에 SELECT 해서 만들어내는 것

 
CREATE VIEW PER20_VU
AS
SELECT PNO,PNAME,MANAGER,PAY,DNO FROM PERSONNEL;

SELECT * FROM PER20_VU;


CREATE VIEW PER_AVG
AS
SELECT DNO,AVG(PAY) 평균,SUM(PAY) 합계
FROM PERSONNEL
GROUP BY DNO;

SELECT * FROM PER_AVG; --각 부서의 평균을 가지고 있는 뷰 

SELECT * FROM USER_VIEWS;

SELECT * FROM PER_AVG WHERE DNO = 10; -- VIEW도 조건절 가능
SELECT * FROM PER_AVG WHERE 합계 >= 8000;

  • VIEW - INSERT/UPDATE/DELETE

INSERT - 추가 

SELECT * FROM PER20_VU;

INSERT INTO PER20_VU VALUES (1234,'AAAA',1001,2000,10);

SELECT * FROM PERSONNEL; -- 확인해보면 PER20_VU는 암시적 NULL삽입으로 비어있는곳 비워둠

UPDATE - 업데이트 

UPDATE PER20_VU SET PNAME = 'BBBB' WHERE PNO = 1234;

DELETE - 삭제 

DELETE PER20_VU WHERE PNO=1234;


SELECT * FROM TAB; -- TAB타입을 보여줌 VIEW인지 TABLE인지

5. VIEW의 INSERT 에러

  • INSERT 에러 (1) 

CREATE VIEW PER
AS
SELECT PNAME,JOB,PAY FROM PERSONNEL; -- PERSONNEL로부터 SELECT 하겠음 -> PK가지고 있음

SELECT * FROM PER;
SELECT * FROM PERSONNEL; -- PK는 널값 허용 X 그런데 

INSERT INTO PER VALUES ('BBB','ACCOUNT',3000); -- 에러 :  NULL넣을 수 없어서

  • INSERT 에러 (2) 

SELECT * FROM PER_AVG;

INSERT INTO PER_AVG VALUES (40,1234,5000); -- 낱개의 데이터가 아님 / 통합된 데이터임 (날코딩x)
  • join문으로 만든 뷰는 dml이 불가능함 (insert /update / delete)

6. VIEW 수정

  • create or replace view

  • create로 만든 애는 -> alter로 수정함

  • view는 create or replace view로 수정하거나 만들 수 있음

SELECT * FROM PER20_VU;

CREATE OR REPLACE VIEW PER20_VU --없으면 만들고, 있으면 수정해라
(번호,이름,직업,부서번호)
AS
SELECT PNO,PNAME,JOB,DNO FROM PERSONNEL
WHERE DNO = 20;


CREATE OR REPLACE VIEW AA
AS
SELECT * FROM PERSONNEL WHERE DNO = 10;

SELECT * FROM AA;

7. VIEW 삭제

  • 뷰 삭제 (DROP)

SELECT * FROM USER_VIEWS;

DROP VIEW AA;
DROP VIEW PER_AVG;

8. VIEW를 이용한 JOIN문

  • VEIW - JOIN문

  • JOIN문으로 만든 뷰는 INSERT / UPDATE / DELETE 불가능 X
     

CREATE TABLE GOGAK
(NO CHAR(10),
NAME CHAR(10));

CREATE TABLE COMP
(NO CHAR(10),
COMNAME CHAR(10));

INSERT INTO GOGAK VALUES ('A001', '배수지');
INSERT INTO GOGAK VALUES ('A002', '유인나');
INSERT INTO COMP VALUES ('A001', 'LG');
INSERT INTO COMP VALUES ('A002', 'HYUNDAI');


SELECT * FROM GOGAK;
SELECT * FROM COMP;


--JOIN문

CREATE VIEW INFO
AS
SELECT A.NO,NAME,COMNAME
FROM GOGAK A, COMP B
WHERE A.NO = B.NO;

SELECT * FROM INFO;
SELECT * FROM INFO WHERE NO = 'A001';


INSERT INTO INFO VALUES ('A003', '이이담', 'SAMSUNG');  
UPDATE INFO SET NAME = '이이담' WHERE NO = 'A001';  
DELETE INFO WHERE NO = 'A001';

9. WITH CHECK OPTION

  • WITH CHECK OPTION

  • 제약조건으로 생각

  • 해당되는 컬럼 이외엔 나머지는 다 막아주는 명령어

UPDATE PER10_VU SET DNO = 20 WHERE PNO = 1111;

SELECT * FROM PER10_VU;
SELECT * FROM PERSONNEL; --10번부서만 관리하는 얜데 20번을 관리하게끔 시켰으니 에러뜸

CREATE OR REPLACE VIEW PER10_VU
AS
SELECT * FROM PERSONNEL WHERE DNO = 10
WITH CHECK OPTION CONSTRAINT DNO10_VU_CK; -- 10번 이외엔 나머지는 다 막아주는 명령어 : WITH CHECK OPTION


SELECT * FROM PER10_VU;

UPDATE PER10_VU SET DNO = 20 WHERE PNO = 1001; --에러

10. WITH READ ONLY

  • WITH READ ONLY

  • 읽기전용

CREATE OR REPLACE VIEW PER10_VU
AS
SELECT PNO,PNAME,JOB FROM PERSONNEL WHERE DNO = 10
WITH READ ONLY;

SELECT * FROM PER10_VU;

DELETE PER10_VU WHERE PNO = 1001; -- 에러

11. ⭐ TOP-N (ex, 성적순으로,,)

  • ROWNUM

    : SELECT되는 그 순간에 일렬번호를 만드는 것 - 별칭 필수!!

--가장 최근에 입사한 5명의 직원


SELECT * FROM PERSONNEL ORDER BY STARTDATE DESC; --최근입사



SELECT ROWNUM, PNO, PNAME,STARTDATE FROM PERSONNEL ORDER BY STARTDATE DESC; --ROWNUM을 붙인 상태에서 내림차순 실행

-- 하위쿼리이용
SELECT ROWNUM FASTDATE, PNAME,STARTDATE FROM
(SELECT PNAME, STARTDATE FROM PERSONNEL ORDER BY STARTDATE DESC)
WHERE ROWNUM <=5;


-- **외우기!!!! 전체에서 특정범위값 구하기 (ex, 게시판 만들때 2페이지(5~16p),3페이지 등 만들때)

SELECT RNUM,PNAME,STARTDATE FROM 
(SELECT ROWNUM RNUM, PNAME,STARTDATE FROM 
(SELECT PNAME,STARTDATE FROM PERSONNEL ORDER BY STARTDATE DESC))
WHERE RNUM>=3 AND RNUM<=5;

--MS-SQL에서만 실행됨
--TOP-N

/
SELECT TOP 5 PNAME,STARTDATE FROM PERSONNEL
ORDER BY STARTDATE DESC;
/

/
SELECT TOP 5 PERCENT PNAME,STARTDATE FROM PERSONNEL
ORDER BY STARTDATE DESC;
/


### 12. SYNONYM (동의어)
- ####  <span style='background-color:#fff5b1'> SYNONYM
</span> : 별칭 
</br>
```sql
CREATE SYNONYM SALARY -- PAYGRADE를 SALARY로 불러서 만들어라 (= 별칭)
FOR PAYGRADE;

SELECT * FROM SALARY;



CREATE SYNONYM INSA --INSA라는 권한을 줌 
FOR KIM.PERSONNEL; --스키마 : 열쇠 ??

SELECT * FROM INSA; --KIM.PERSONNEL실행

13. INDEX

  • INDEX

    : 조건문의 컬럼에 만듦

- SELECT 컬럼명 FROM 테이블 WHERE 컬럼 = 10;
-> [WHERE 컬럼] 요 컬럼에 인덱스 만들기

CREATE INDEX PER_IDX
ON PERSONNEL (PAY);



CREATE TABLE AAA
(ID NUMBER(4) CONSTRAINT AAA_ID_PK PRIMARY KEY,
NAME CHAR(10));

--index만들어졌나 확인
SELECT * FROM USER_CONSTRAINTS;
--INDEXT 정보 / --PK(중복X / NULLX / 테이블당 하나만)를 만들면 PK의 이름과 같은 INDEX 정렬이 만들어짐 - 클러스트(MS-SQL)/넌클러스트 (오라클)
SELECT * FROM USER_INDEXES;

profile
윤수인
어제보다 조금 더 성장하기!
이전 포스트

[Query] Day 4 - DDL / DML / DCL

다음 포스트

[SQL] Day 6 - 시퀀스

0개의 댓글