SQLD 4-7 ] DCL (DATA CONTROL LANGUAGE)
❏ DCL 이란?
- 유저를 생성하고 권한을 제어할 수 있는 명령어.
(지금까지 해왔던 걸 보면. DDL/DML/TCL 이 있었지.)
❏ 유저와 권한
- 보안을 위해서 새로운 유저를 생성해서 그 유저에게 권한 주고 뭐 이런건데 (관리자를 그냥 알려줄순 없어서..)
ORACLE 에서 기본적으로 제공되는 유저들인 SYS, STSTEM, SCOTT 유저가 있다.
지금 쓰고 있는 슈퍼관리자 는 SYSTEM 이고. 추가로 SCOTT, SYS 가 있었네.
ORACLE 과 SQL SERVER 의 사용자에 대한 아키텍쳐는 다른 면이 많다.
ORACLE
- 유저를 통해 데이터베이스에 접속.
아이디와 비밀번호 방식으로 데이터베이스의 인스턴스에 접속을 한다.
유저에 해당하는 권한을 부여받는다. 구역마다 다른 역할을 할 수 있게
SQL SERVER
- 로그인이라는것을 생성. 인스턴스 내에 존재하는 데이터베이스에 연결하기 위해 유저를 생성한 후에 로그인과 유저를 맵핑
로그인 방식 1
- Windows 인증 방식. Windows 에 로그인한 정보를 가지고 SQL Server 에 접속하는 방식.
기본 모드 같은데. 그냥 윈도우 쓰면 그 윈도우 안에 있는 보안토큰 가지고 확인하는 것.
(하긴, 관리자유저인 우리는 이런거 있는지도 모르고 잘 되는데 다른데서 침입할때.. 비교를 하긴 해야겠지...??)
로그인 방식 2
- 혼합모드. 로그인방식 1 처럼 Windows 인증으로도 할 수 있고, ORACLE 처럼 ID/PW 로도 할 수 있고.
❏ 유저 생성과 시스템 권한 부여
- 100개 이상의 권한이 있다. 너무 복잡하고 많다. 그래서 롤(ROLE) 을 이용하여 간편하게 할 수 있다! (조금 이따 설명)
SCOTT 유저로 접속한 다음 PJS 유저 (PW:KOREA7) 을 생성하자
왜 접속부터 잠금걸려있냐..
CMD 켜서 select username, account_status from dba_users;
다시 .... CONN SCOTT/TIGER 해보자.
SCOTT 유저는 유저를 생성하는 권한이 없어서..
다시 그럼 권한이 쎈 슈퍼유저로 다시 로그인하자..
사용된 PJS 로 로그인해보자
CONN PJS/KOREA7;
오류: PJS 는 CREATE SESSION 권한이 없습니다
계정 만들어도 바로 못들어가네. 세션 이 있어야하나.
다시 또 그럼 관리자로 들어가서 만들어주자.
SCOTT 에게 그냥 관리자 권한을 줬다
CONN /as sysdba
연결되었다.
GRANT CREATE SESSION TO SCOTT WITH ADMIN OPTION;
권한이 부여되었다.
다시.. SCOTT 로 들어가서
PJS 계정에게 로그인 할 수 있는 세션을 줘보자
CONN SCOTT/TIGER;
연결되었다.
GRANT CREATE SESSION TO PJS;
권한이 부여되었다.
CONN PJS/KOREA7;
연결되었다.
드디어 PJS 계정 만들고 로그인 까지 했다.
테이블을 생성하자
SELECT * FROM TAB;
선택된 레코드가 없다. (당연..)
CREATE TABLE MENU (
MENU_SEQ NUMBER NOT NULL,
TITLE VARCHAR(10)
);
권한이 불충분하다.테이블도 못만들어!!!!!
CONN /as sysdba
연결되었다.
GRANT CREATE TABLE TO PJS;
권한이 부여되었다.
CONN PJS/KOREA7;
연결되었다.
CREATE TABLE MENU (
MENU_SEQ NUMBER NOT NULL,
TITLE VARCHAR(10)
);
테이블이 생성되었다.
드디어.. 테이블 까지 만들었다.
❏ OBJECT 에 대한 권한 부여
- 유저생성, 시스템권한 부여 까진 했고. 이제 OBJECT 에 권한에 대해 알아보자
앞에서 PJS 유저가 생성한 테이블을 SCOTT 가 들여다 볼수 있을까? 염탐 하러 가보자
CONN SCOTT/TIGER;
연결되었다.
SELECT * FROM PJS.MENU;
오류: 테이블 또는 뷰가 존재하지 않는다.
안되네.
일단 그럼 테이블에 내용부터 하나 넣고와보자
CONN PJS/KOREA7;
연결되었다.
INSERT INTO MENU VALUES(1, '화이팅');
오류: 테이블 스페이스 USERS 에 대한 권한이 없습니다.
아니.. insert into 도 못해?!
또 PJS 에게 insert into 할수 있는 권한 주러 또 가야되네...
CONN /as sysdba
연결되었다.
ALTER USER PJS QUOTA 10M ON USERS;
사용자가 변경되었다.
테이블 용량을 10M 로.
ALTER USER <USERNAME> QUOTA <용량> on <TABLESPACE_NAME>;
다시.. PJS 로 들어와서 insert into 를 해보자.
CONN PJS/KOREA7;
연결되었다.
INSERT INTO MENU VALUES(1, '화이팅');
1개의 행이 만들어졌습니다.
COMMIT;
커밋되었다.
GRANT SELECT ON MENU TO SCOTT;
권한이 부여되었다.
내용 커밋 하고, 권한 까지 줬다.
CONN SCOTT/TIGER;
연결되었다.
SELECT * FROM PJS.MENU;
MENU_SEQ TITLE
----------- -------
1 화이팅
UPDATE PJS.MENU
SET TITLE = '코리아'
WHERE MENU_SEQ = 1;
권한이 불충분하다.
업데이트 메뉴도 주자..
너무 귀찮다!!!!
CONN PJS/KOREA7;
연결되었다.
GRANT UPDATE ON MENU TO SCOTT;
권한이 부여되었다.
UPDATE PJS.MENU
SET TITLE = '코리아'
WHERE MENU_SEQ = 1;
1행이 갱신되었다.
❏ ROLE 을 이용한 권한 부여
- 지금까지 한거 보면.. 유저를 생성하면 CREATE SESSION 줘야하고.. CREATE TABLE.. 등 여러가지 줄께 너무 많다..
어떻게 이런걸 일일히 유저 생성할때마다 다 할까. 그래서 나온게 ROLE 이다.
- ROLE 을 생성하고, 이 ROLE 에 각종 권한들을 부여한 후, ROLE을 다른 ROLE 이나 유저에게 부여할 수 있다.
PJS 유저에게 CREATE SESSION / CREATE TABLE 권한을 가진 ROLE 을 생성 후 이 ROLE 가지고 다른애한테 권한을 할당해보자
CONN /as sysdba
연결되었다.
REVOKE CREATE SESSION, CREATE TABLE FROM PJS;
권한이 취소되었다.
CONN PJS/KOREA7;
오류: CREATE SESSION 권한을 가지고 있지 않다.
있던 CREATE SESSION, CREATE TABLE 권한 뻇고.. 테스트 해본결과 연결조차 안된다. 정상이다.
CONN /as sysdba
연결되었다.
CREATE ROLE LOGIN_TABLE;
롤이 생성되었다.
GRANT CREATE SESSION, CREATE TABLE TO LOGIN_TABLE;
권한이 부여되었다.
GRANT LOGIN_TABLE TO PJS;
권한이 부여되었다.
CONN PJS/KOREA7;
연결되었다.
CREATE TABLE MENU (
MENU_SEQ NUMBER NOT NULL,
TITLE VARCHAR(10)
);
테이블이 생성되었다.
와. 진짜 간단하다.
CREATE ROLE <롤 이름>;
으로 만들고 GRANT 권한, 권한 TO 롤이름 주고.
이 롤 을 TO 유저 에게 수여해주고..
CONNECT 는 CREATE SESSION 과 같은 로그인 권한이 있고
RESOURCE 는 CREATE TABLE 과 같은 오브젝트의 생성 권한이 포함되어 있다.
기본적으로 일반적으로 유저 생성하고 주는게 CONNECT, RESOURCE 이 두개 롤 주면. 왠만한건 다 할수 있네.
CONN /as sysdba
연결되었다.
DROP USER PJS CASCADE;
사용자가 삭제되었다.
(CASCADE 를 쓰면 PJS유저가 만든 테이블도 같이 삭제된다)
CREATE USER PJS IDENTIFIED BY KOREA7;
사용자가 생성되었다.
GRANT CONNECT, RESOURCE TO PJS;
권한이 부여되었다.
CONN PJS/KOREA7
연결되었다.
CREATE TABLE MENU (
MENU_SEQ NUMBER NOT NULL,
TITLE VARCHAR(10)
);
테이블이 생성되었다.
이게 가장 이상적인 권한 부여 방식이네..
CONNECT / RESOURCES 를 부여하자..!!
수동으로 부여해도 되고.
반면..
SQL SERVER 에서는.
ROLE 을 생성해서 쓰기 보다는 기본적으로 제공되는 ROLE 에 멤버로 참여하는 방식으로 사용한다.
예도 없고... 그냥. 한번 보긴 하자.
