시큐리티 테이블 SQL

--USER = SICURITY가 지정해논 형식 무조건 USERS로 해야함
--사용자(1)
CREATE TABLE USERS(
    USERNAME VARCHAR2(150),
    PASSWORD VARCHAR2(150),
    ENABLED VARCHAR2(1),
    CONSTRAINT PK_USERS PRIMARY KEY(USERNAME)
);

--권한들(N)
--기본키{P,K}와 외래키(F.K)의 자료형(데이터타입)과 크기는 동일해야 함
CREATE TABLE AUTHORITIES(
    USERNAME VARCHAR2(150),
    AUTHORITY VARCHAR2(150),
    CONSTRAINT PK_AUTH PRIMARY KEY(USERNAME, AUTHORITY),
    CONSTRAINT FK_AUTH FOREIGN KEY(USERNAME) REFERENCES USERS(USERNAME)
);

SELECT A.USERNAME, A.PASSWORD, A.ENABLED
FROM USERS A, AUTHORITIES B
WHERE A.USERNAME = B.USERNAME
AND   A.USERNAME= 'admin';

--ANSI표준. INNER JOIN
SELECT A.USERNAME, A.PASSWORD, A.ENABLED
FROM USERS A INNER JOIN AUTHORITIES B ON(A.USERNAME = B.USERNAME)
WHERE A.USERNAME= 'admin';

--ANSI표준.NATURAL JOIN
--기본키 컬럼명과 외래키 컬럼명이 동일해야 함
--조인조건 컬럼은 테이블 ALIAS를 붙이지 않음
SELECT USERNAME, A.PASSWORD, A.ENABLED
FROM USERS A NATURAL JOIN AUTHORITIES B
WHERE USERNAME = 'admin';