Redshift 사용자 관리 - BESPIN Tech Blog

안녕하세요 오늘은 베스핀글로벌 Devops 정경희님이 작성해 주신 Redshift 사용자 관리에 대해 알아보겠습니다.

궁금하신 부분이 있으시면 댓글을 달아주세요 🙂

AGENDA

1.사용자 생성

2.그룹 생성

3.권한 관리

– 기본 권한 제거

– 사용자에 권한 부여

– 사용자 그룹에 권한 부여

– 권한 관련 정보

4.권한 확인

5.사용자 삭제

– 권한 제거

– 소유한 객체 제거

6.UDF 권한

7.Spectrum 권한

8.IAM 권한

1. 사용자 생성

[Syntax]

CREATE USER name [ WITH ] PASSWORD { 'password' | 'md5hash' | DISABLE } [ option [ ... ] ]


where option can be:

CREATEDB | NOCREATEDB

| CREATEUSER | NOCREATEUSER

| SYSLOG ACCESS { RESTRICTED | UNRESTRICTED }

| IN GROUP groupname [, ... ]

| VALID UNTIL 'abstime’

| CONNECTION LIMIT { limit | UNLIMITED }

| SESSION TIMEOUT { limit }

Password 제약 사항
- 8~64자 사이의 길이
- 최소한 대문자 1개, 소문자 1개 및 숫자 1개를 포함
- ‘(작은따옴표), “(큰따옴표), \, / 또는 @을 제외하고 모든 ASCII 문자(ASCII 코드 33~126) 사용 가능

CREATE USER r_user PASSWORD ‘password’; — 일반 사용자 생성
CREATE USER s_user CREATEUSER PASSWORD ‘password’; — 슈퍼 유저 생성

2. 사용자 그룹 생성

[Syntax]

CREATE GROUP group_name
[ [ WITH ] [ USER username ] [, ...] ]

여러 사용자를 생성할 경우 그룹으로 관리하는 것이 유용
사용자 삭제 시 권한 제거 단계가 간편

CREATE GROUP r_group; — 사용자 그룹 생성
CREATE GROUP r_group WITH user1, user2; — 사용자 user1, user2 포함하여 그룹 생성

ALTER GROUP r_group ADD USER r_user; — r_group 그룹에 사용자 r_user 추가
ALTER GROUP r_group DROP USER r_user; — r_group 그룹에서 사용자 r_user 제거

3. 권한 관리

Redshift에서 제공하는 권한

* super user 또는 객체 소유자는 객체(schema, table 등)에 대한 모든 권한을 가지고 있음

3-1. 기본 권한 제거

PUBLIC에 부여된 권한을 제거 (중요 포인트!!!)

redshift 생성 시 기본 부여된 권한을 제거하기 위함

REVOKE ALL ON DATABASE dev FROM PUBLIC; — PUBLIC에 부여된 ‘dev’ database의 모든 권한 제거
REVOKE ALL ON SCHEMA public FROM PUBLIC; — PUBLIC에 부여된 ‘public’ schema의 모든 권한 제거
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC; — PUBLIC에 부여된 ‘public’ schema에 포함된 table의 모든 권한 제거

PUBLIC ?

모든 사용자를 항상 포함하는 그룹을 나타내며, 이후 생성될 사용자도 포함

개별 사용자의 권한

= PUBLIC에 허용되는 권한 + 사용자가 속한 그룹에 허용되는 권한 + 사용자에게 개별적으로 허용되는 권한

3-2. 사용자에 권한 부여

▪ read only 권한 부여
GRANT USAGE ON SCHEMA public TO r_user; — ‘public’ schema에 대한 USAGE 권한 부여
GRANT SELECT ON ALL TABLES IN SCHEMA public TO r_user; — ‘public’ schem에 포함된 모든 table에 대한 SELETE 권한 부여
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO r_user;
— 현재 사용자가 ‘public’ schema에 앞으로 생성할 테이블에 대한 SELETE 권한 부여

ALTER DEFAULT PRIVILEGES FOR USER rw_user IN SCHEMA public GRANT SELECT ON TABLES TO r_user;
— rw_user 사용자가 ‘public’ schema에 앞으로 생성할 테이블에 대한 SELETE 권한을 r_user 사용자에게 부여

▪ read write 권한 부여
GRANT TEMP ON DATABASE dev TO rw_user; — 임시 테이블 생성 권한 부여
GRANT ALL ON SCHEMA public TO rw_user; — ‘public’ schema에 대한 모든 권한 부여
GRANT ALL ON ALL TABLES IN SCHEMA public TO rw_user; –‘public’ schema에 포함된 모든 table에 대한 모든 권한 부여
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO rw_user; — 현재 사용자가 ‘public’ schema에 앞으로 생성할 테이블에 대한 모든 권한 부여

▪ system table 권한 부여 (사용자 가시성 테이블 및 뷰의 모든 행 확인 가능, 다른 사용자 데이터도 확인 가능)
ALTER USER r_user SYSLOG ACCESS UNRESTRICTED;
ALTER USER rw_user SYSLOG ACCESS UNRESTRICTED;

3-3. 사용자 그룹에 권한 부여

▪ read only 권한 부여
GRANT USAGE ON SCHEMA public TO GROUP r_group; — ‘public’ schema에 대한 USAGE 권한 부여
GRANT SELECT ON ALL TABLES IN SCHEMA public TO GROUP r_group; — ‘public’ schem에 포함된 모든 table에 대한 SELETE 권한 부여
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO GROUP r_group;
— 현재 사용자가 ‘public’ schema에 앞으로 생성할 테이블에 대한 SELETE 권한 부여

ALTER DEFAULT PRIVILEGES FOR USER rw_user IN SCHEMA public GRANT SELECT ON TABLES TO GROUP r_group;
— rw_user 사용자가 앞으로 생성할 테이블에 대한 SELETE 권한을 r_group에 부여

▪ read write 권한 부여
GRANT TEMP ON DATABASE dev TO GROUP rw_group; — 임시 테이블 생성 권한 부여
GRANT ALL ON SCHEMA public TO GROUP rw_group; — ‘public’ schema에 대한 모든 권한 부여
GRANT ALL ON ALL TABLES IN SCHEMA public TO GROUP rw_group; — ‘public’ schema에 포함된 모든 table에 대한 모든 권한 부여
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO GROUP rw_group; — 현재 사용자가 앞으로 ‘public’ schema에 생성할 테이블에 대한 모든 권한 부여

3-4. 권한 관리

권한 부여

[Syntax]

GRANT { { SELECT | INSERT | UPDATE | DELETE | DROP | REFERENCES } [,...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...] | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO { username [ WITH GRANT OPTION ] | GROUP group_name | PUBLIC } [, ...]

GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
    ON DATABASE db_name [, ...]
    TO { username [ WITH GRANT OPTION ] | GROUP group_name | PUBLIC } [, ...]

GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schema_name [, ...]
    TO { username [ WITH GRANT OPTION ] | GROUP group_name | PUBLIC } [, ...]


ALTER DEFAULT PRIVILEGES
    [ FOR USER target_user [, ...] ]
    [ IN SCHEMA schema_name [, ...] ]

GRANT { { SELECT | INSERT | UPDATE | DELETE | DROP | REFERENCES } [,...] | ALL [ PRIVILEGES ] } 
	ON TABLES 
	TO { user_name [ WITH GRANT OPTION ]| GROUP group_name | PUBLIC } [, ...]

권한 제거

[Syntax]

REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | DELETE | DROP | REFERENCES } [,...] | ALL [ PRIVILEGES ] }

ON { [ TABLE ] table_name [, ...] | ALL TABLES IN SCHEMA schema_name [, ...] }

FROM { username | GROUP group_name | PUBLIC } [, ...]

[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ] { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }

ON DATABASE db_name [, ...]

FROM { username | GROUP group_name | PUBLIC } [, ...]

[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ] { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }

ON SCHEMA schema_name [, ...]

FROM { username | GROUP group_name | PUBLIC } [, ...]

[ CASCADE | RESTRICT ]

ALTER DEFAULT PRIVILEGES

    [ FOR USER target_user [, ...] ]

    [ IN SCHEMA schema_name [, ...] ]

REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | DELETE | REFERENCES } [,...] | ALL [ PRIVILEGES ] }

  ON TABLES

  FROM user_name [, ...] [ CASCADE | RESTRICT ]

4. 권한 확인

database 권한 확인

SELETE has_database_privilege(‘r_user’, ‘dev’, ‘create’);
SELETE has_database_privilege(‘r_user’, ‘dev’, ‘temporary’);

schema 권한 확인

SELECT
    u.usename,
    s.schemaname,
    has_schema_privilege(u.usename,s.schemaname,’create’) AS user_has_create_permission,
    has_schema_privilege(u.usename,s.schemaname,’usage’) AS user_has_usage_permission
FROM
    pg_user u
CROSS JOIN
    (SELECT DISTINCT schemaname FROM pg_tables) s
WHERE
    u.usename = ‘r_user’
    AND s.schemaname = ‘public’;

table 권한 확인

SELECT
    u.usename,
    t.schemaname||’.’||t.tablename,
    has_table_privilege(u.usename,t.tablename,’select’) AS user_has_select_permission,
    has_table_privilege(u.usename,t.tablename,’insert’) AS user_has_insert_permission,
    has_table_privilege(u.usename,t.tablename,’update’) AS user_has_update_permission,
    has_table_privilege(u.usename,t.tablename,’delete’) AS user_has_delete_permission,
    has_table_privilege(u.usename,t.tablename,’references’) AS user_has_references_permission,
    has_table_privilege(u.usename,t.tablename,’drop’) AS user_has_drop_permission
FROM
    pg_user u
CROSS JOIN
    pg_tables t
WHERE
    u.usename = ‘r_user’
    AND t.schemaname = ‘public’;

5. 사용자 삭제

권한 제거

사용자에게 부여한 권한 모두 제거
- 사용자 그룹에 속한 경우 사용자에게 직접 부여된 권한이 없을 수 있음

REVOKE ALL ON ALL TABLES IN SCHEMA public FROM r_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON TABLES FROM r_user;
REVOKE ALL ON SCHEMA public FROM r_user;

REVOKE ALL ON ALL TABLES IN SCHEMA public FROM rw_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON TABLES FROM rw_user;
ALTER DEFAULT PRIVILEGES FOR USER rw_user2 IN SCHEMA public REVOKE ALL ON TABLES FROM r_user;
REVOKE ALL ON SCHEMA public FROM rw_user;
REVOKE ALL ON DATABASE dev FROM rw_user;

사용자에 부여된 권한 확인 쿼리

https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminScripts/user_to_be_dropped_privs.sql

소유한 객체 제거

삭제할 사용자가 소유한 객체를 삭제하거나, OWNER 권한을 다른 사용자에게 이전

ALTER TABLE test1 OWNER TO rw_user2; — 다른 사용자에게 소유권 이전
DROP TABLE test2; — 객체 삭제

사용자가 소유한 객체 확인 쿼리

https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminScripts/user_to_be_dropped_objs.sql

사용자 삭제
- 소유한 객체와 부여된 권한이 모두 제거되기 전까지는 사용자 삭제 시 에러 발생

DROP USER r_user;
DROP USER rw_user;

6. UDF, 저장 프로시저 권한

UDF, 저장 프로시저 생성 권한을 제거하기 위해서는 언어별 USAGE 권한을 제거

UDF, 저장 프로시저 생성 권한을 주기 위해서는 사용자 또는 그룹에 사용할 언어별로 USAGE 권한 부여

— 생성 권한 제거 및 부여
revoke usage on language sql from PUBLIC; — PUBLIC에 부여된 권한 제거
grant usage on language sql to group rw_group; — 사용자 그룹에게 권한 부여
grant usage on language sql to rw_user; — 사용자에게 권한 부여
* SQL UDF에서는 sql, Python UDF에서는 plpythonu, 저장 프로시저에서는 plpgsql를 language로 사용

— EXECUTE 권한 제거 및 부여
alter default privileges revoke execute on functions from PUBLIC;
alter default privileges grant execute on functions to group rw_group;

alter default privileges revoke execute on procedures from PUBLIC;
alter default privileges grant execute on procedures to group rw_group;

7. Spectrum 권한

Spectrum(external schema)에서 external table 생성은 슈퍼 유저 또는 스키마 소유자만 가능

일반 사용자가 테이블 생성하기 위해서는 스키마 소유권을 이전

테이블 사용 권한을 부여하려면 USAGE ON SCHEMA를 부여

ALTER SCHEMA spectrum OWNER TO rw_user; — schema owner 권한을 rw_user에게 이전. external table 생성 가능
GRANT USAGE ON SCHEMA spectrum TO r_user; — external table 접근 가능 (select)

— external table 권한 확인
SELECT
    u.usename,
    s.schemaname,
    has_schema_privilege(u.usename,s.schemaname,’create’) AS user_has_create_permission,
    has_schema_privilege(u.usename,s.schemaname,’usage’) AS user_has_usage_permission
FROM
    pg_user u
CROSS JOIN
    (SELECT DISTINCT schemaname FROM SVV_EXTERNAL_TABLES) s
WHERE
    u.usename = ‘r_user’
    AND s.schemaname = ‘spectrum’;

8. IAM 권한

COPY, UNLOAD 및 CREATE LIBRARY 작업을 위한 IAM 권한

CREDENTIALS 파라미터에서 참조하는 IAM 역할 또는 IAM 사용자는 최소한 다음 권한을 가지고 있어야 함

감사합니다 🙂

Written by 정 경희 / Kyunghee Jung

Project Manager

AGENDA

1.사용자 생성

2.그룹 생성

3.권한 관리

– 기본 권한 제거

– 사용자에 권한 부여

– 사용자 그룹에 권한 부여

– 권한 관련 정보

4.권한 확인

5.사용자 삭제

– 권한 제거

– 소유한 객체 제거

6.UDF 권한

7.Spectrum 권한

8.IAM 권한

1. 사용자 생성

[Syntax]

2. 사용자 그룹 생성

[Syntax]

3. 권한 관리

Redshift에서 제공하는 권한

3-1. 기본 권한 제거

PUBLIC에 부여된 권한을 제거 (중요 포인트!!!)

PUBLIC ?

개별 사용자의 권한

3-2. 사용자에 권한 부여

3-3. 사용자 그룹에 권한 부여

3-4. 권한 관리

권한 부여

권한 제거

4. 권한 확인

database 권한 확인

schema 권한 확인

table 권한 확인

5. 사용자 삭제

권한 제거

소유한 객체 제거

6. UDF, 저장 프로시저 권한

UDF, 저장 프로시저 생성 권한을 제거하기 위해서는 언어별 USAGE 권한을 제거

UDF, 저장 프로시저 생성 권한을 주기 위해서는 사용자 또는 그룹에 사용할 언어별로 USAGE 권한 부여

7. Spectrum 권한

Spectrum(external schema)에서 external table 생성은 슈퍼 유저 또는 스키마 소유자만 가능

일반 사용자가 테이블 생성하기 위해서는 스키마 소유권을 이전

테이블 사용 권한을 부여하려면 USAGE ON SCHEMA를 부여

8. IAM 권한

COPY, UNLOAD 및 CREATE LIBRARY 작업을 위한 IAM 권한

이 글 공유하기:

관련글

Leave a Comment 응답 취소