데이터베이스 권한(grant,revoke)

권한이란?

-데이터베이스 연산을 하려면 해당 사용자가 연산에 필요한 권한을 가지고 있어야 하며, 만약에 권한을 가지지 못했다면 수행이 거부됩니다.
DBA는 모든 권한을 다 가지고 있으며, 특정사용자에게 권한 부여가능합니다. DCL을 사용하여 테이블과 뷰를 대상으로 권한을 부여가능하며 각 튜플에 대한 권한은 지원하지않습니다.

 

권한의 종류

• 인스턴스 권한
  -읽기 권한
  -입력 권한
  -갱신 권한
  -삭제 권한
  
  
• 스키마 권한
  -색인 생성/삭제 권한
  -테이블 생성 권한
  -테이블 속성 변경 권한
  -테이블 삭제 권한

SQL에서 지원하는 권한

• SELECT 권한 - 한 릴레이션에서 select를 할 수 있는 권한
• INSERT 권한 - 한 릴레이션에서 insert를 할 수 있는 권한
• UPDATE 권한 - 한 릴레이션에서 update를 할 수 있는 권한
• DELETE 권한 - 한 릴레이션에서 delete를 할 수 있는 권한
• REFERENCES 권한 - 한 릴레이션에서 다른테이블에 외래키(foreign key)를 생성할 수 있는 권한
  references (속성값) on 선언함
• USAGE 권한 - 다른 사용자에게 권한을 부여할 수있는 권한을 주는권한

 

 

 

 

 

 

GRANT 문법

-권한을 부여하는 명령어. 이를 사용하여 특정 사용자,그룹에 대해 데이터베이스 객체의 권한을 부여할 수 있습니다.

GRANT 권한 ON 객체 TO 사용자  (with grant option);

특정사용자에게 객체에 대한 권한을 주는 명령어이다. with grant option을 사용하면 사용자가 부여받은 권한을 다른사용자에게 부여할 수 있다.

 

 

예시

GRANT select ON professor TO person;  // person이란 사용자에게 professor 테이블에 대한 select권한을 부여

 

 

 

 

 

 

REVOKE 문법

-부여된 권한을 취소하는 명령어. 이를 사용하여 특정 사용자,그룹에 대해 데이터베이스 객체의 권한을 취소할 수 있습니다.

REVOKE (grant option) 권한 ON 객체 FROM 사용자  (CASCADE or RESTRICT);

특정사용자에게 객체에 대한 권한을 취소하는 명령어 이다.
CASCADE는 대상 사용자가 다른 사용자나 그룹에게 부여한 권한도 한꺼번에 같이 취소하는 옵션이고,
RESTRICT는 대상 사용자가 다른 사용자나 그룹에게 부여한 권한이 있으면 일단 중단하고 에러를 발생 시키는 옵션입니다. grant option옵션은  grant option만을 취소하는 옵션입니다.

 

 

 

 

 

 

 

 

 

권한 그래프

위 그림은 권한 그래프이며 권한부여한 것을 그래프로 표시한 것입니다. 위 그림에서 DBA가 USER1의 권한을 취소하면 USER1이 권한을 부여한 USER3,4,6의 권한도 같이 취소가됩니다. 여기서 중요한것은 USER3가 USER1에게 권한을 준거까지 취소됩니다.

 

 

 

 

 

 

뷰권한

-테이블과 마찬가지로 권한 부여 대상이므로 똑같이 권한을 부여할 수 있습니다. 다만, 뷰를 업데이트하게 될시 테이블도 업데이트되므로  뷰권한뿐아니라 테이블권한도 같이 가져야합니다. 즉, 뷰권한과 테이블권한은 독립적입니다.

 

-- 뷰 생성 (테이블에 대한 읽기 권한만 있으면 뷰 생성가능)
CREATE VIEW my_view AS
SELECT column1, column2
FROM my_table
WHERE column3 = 'some_value';

-- SELECT 권한 부여
GRANT SELECT ON my_view TO my_user;

위의 예시는 my_user에게 my_view를 조회할 수 있는 권한을 부여한것입니다. select외에 다른 연산은 하지 못합니다. 또한, 테이블 생성자는 그 테이블에 대한 모든 권한을 바로 가지지만, 뷰 생성자는 테이블과 연관되어 있기에 뷰를 생성한다해도 뷰에대한 모든권한을 가지지 못합니다. 뷰의 이러한 접근권한 검사는 뷰가 확장되기 전에 뷰에 대하여 수행됩니다.

 

 

 

 

 

 

 

롤(role)

-특정 권한을 가진 사용자들의 그룹. 권한 부여를할때 개개인이 아닌 롤로 묶어서 사용하면 권한 관리를 효율적으로 할 수 있습니다.

 

-- Role 생성
CREATE ROLE my_role;
CREATE ROLE my_role2;

-- 권한 부여
GRANT SELECT, INSERT ON my_table TO my_role;

-- Role에 사용자 추가
GRANT my_role TO my_user1, my_user2;

-- Role 끼리 권한 부여
GRANT my_role TO my_role2;

위의 예시를 보시면 CREATE ROLE을 이용해 롤을 생성하고 ROLE에게 권한을 부여하고 사용자를 추가하는 방식으로 롤을 생성할 수 있습니다. 그리고 롤을 상위 하위 관계로 나누어 롤끼리 권한부여를 할 수 있습니다.
위에서 GRANT my_role TO my_role2; 이것은 my_role이 가지고 있는 권한을 my_role2에게 다 주는 것이고, 이후에 my_role의 권한이 수정되면 my_role2도 같이 수정이 되게 됩니다.