AWS Redshift에서 유저 단위로 테이블에 대한 권한을 확인하기

안녕하세요 DA사업본부 빅데이터팀 송영진입니다.

오늘은 Redshift 운용중에 특정 유저가 특정 테이블에 권한이 있는지 여부를 확인하는 방법을 알아보았습니다.

보통 유저에 대한 권한은 그룹이나 특정 유저에게 GRANT 커맨드를 이용하여 스키마나 테이블, 뷰 같은 데이터에 접근 권한을 부여해서 사용하곤 합니다. 그러나 권한을 줬는지 안줬는지가 확실하지가 않아서 확인하고 싶을 때가 있습니다. 바로 그럴 때에 오늘 알아본 방법을 사용할 수 있습니다!

바로 시스템 정보 함수인 HAS_TABLE_PRIVILEGE를 사용하는 방법입니다.

HAS_TABLE_PRIVILEGE

사용자가 지정한 테이블에 대한 특정 권한을 가지고 있으면 true를 반환합니다.

심플한 함수입니다. 다음과 같은 형식으로 사용합니다.

has_table_privilege( [유저,] 테이블, 확인하고 싶은 기능)

 

HAS_SCHEMA_PRIVILEGE 함수도 똑같은 기능의 스키마 버전인데요, 이 함수들을 PostgreSQL의 시스템 카탈로그인 pg_tables과 pg_user를 사용하여 SQL을 만들겠습니다.

 

SQL

select
  usename
  , schemaname
  , tablename
  , has_table_privilege(usename, schemaname || '.' || tablename, 'select') as select
  , has_table_privilege(usename, schemaname || '.' || tablename, 'insert') as insert
  , has_table_privilege(usename, schemaname || '.' || tablename, 'update') as update
  , has_table_privilege(usename, schemaname || '.' || tablename, 'delete') as delete
  , has_table_privilege(usename, schemaname || '.' || tablename, 'references') as references
  , has_schema_privilege(usename, schemaname, 'usage') as usage
from
  pg_tables, pg_user
where
  schemaname in ('확인하고 싶은 스키마1', '확인하고 싶은 스키마2')  
and
  tablename in ('확인하고 싶은 테이블1', '확인하고 싶은 테이블2')
and
  usename in ('확인하고 싶은 유저')  
;

이러한 형식으로 SQL을 만들면 테이블에 유저가 어떤 권한을 가지고 있는지 확인하실 수 있습니다. 저는 where절을 이용하여 범위를 줄였지만 모든 테이블에 대해 확인할 경우는 usename만 남겨두는식으로 수정해서 사용하시면 되겠습니다.

| usename | schemaname | tablename | select | insert | update | delete | references | usage |
| ------- | ---------- | --------- | ------ | ------ | ------ | ------ | ---------- | ----- |
| user1   | schema1    | table1    | true   | true   | true   | true   | true       | true  |
| user1   | schema1    | table2    | true   | false  | false  | false  | false      | false |
| user1   | schema2    | table3    | false  | false  | false  | false  | false      | false |

결과는 tf로 반환되며 유저에게 권한이 있다면 true, 권한이 없다면 false로 표시됩니다.

 

끝으로

특정 유저가 어떤 테이블에 어떤 권한을 갖고 있는지 확인하는 방법을 알아보았습니다. 일을 하면서 확인해야 할 필요가 있었기 때문에 찾아보았습니다. 한국어로 된 자료가 없었기 때문에 블로그를 작성했는데요. 다른분들도 Redshift 운용하면서 이러한 케이스를 겪을 때 도움이 되기를 바랍니다. 감사합니다.