User/Group 관리

사용자 생성

SYBASE IQ를 설치하면 dba라는 사용자가 자동으로 생성되며 dba는 가장 높은 권한을 소유한 SYBASE IQ의 super user이고 비밀번호는 SQL이 자동 할당 됩니다.

dba 권한을 가진 사용자가 각 사용자 및 권한을 관리하기 위해 개별 사용자를 생성할 수 도 있고 권한을 부여할 수 도 있습니다.

사용자를 생성할 수 있는 방법은 아래처럼 두 가지가 있으며 sp_iqaddlogin은 12.5에서 새로 만들어진 기능 향상된 방법이기에 권장합니다.

syntax1
GRANT CONNECT TO user-name IDENTIFIED BY password;

• user-name : 사용자 계정
• password : 계정의비밀번호(128 문자까지 가능)

이미 존재하는 사용자에 대해 사용하면 그 사용자의 비밀번호를 변경하는 것이며 IDENTIFIED BY절을 생략하여 만들어진 사용자는 접속이 불가능하기 때문에 주의해야 합니다.

이 방법을 사용하면 새로 추가된 비밀번호 만기, 같은 사용자명을 가진 동시 접속자 수 제한 등과 같은 기능을 사용할 수 없으며 이를 이용하려면 sp_iqaddlogin 다시 한번 실행해주면 됩니다.

syntax2
sp_iqaddlogin user-name, password [,number-of-connections] [,password-expiration] ;

• user-name : 사용자 계정
• password : 계정의비밀번호(128 문자까지 가능)
• number-of-connections : 해당user-name 으로 동시에 접속할 수 있는 접속자 수
• password-expiration : 비밀번호 만기 일자

이 방법을 사용해서 비밀번호 만기, 같은 사용자명을 가진 동시 접속자 수 제한 등과 같은 기능을 사용하려면 선행작업으로 sp_iqmodifyadmin 을 이용하며 기능을 활성화 시켜야 합니다.

현재 설정 값을 확인하시려면 IQ_SYSTEM_LOGIN_INFO_TABLE 테이블의 내용을 확 인하시면 됩니다. 즉 user_admin_enabled 값이 기본적으로 N 으로 되어 있는데 Y로 설정되어야 해당 기능들을 사용하는 사용자를 생성할 수 있습니다.

사용자별 비밀번호 만기, 같은 사용자명을 가진 동시 접속자 수 제한과 같은 기능의 설정 확인은 IQ_USER_LOGIN_INFO_TABLE 테이블을 통해서 할 수 있습니다. 또한 비밀번호 만기까지 남은 일수 확인은 sp_iqlistpasswordexpirations를 통해 할 수 있습니다.

다음과 같은 옵션을 통해 비밀번호의 최소길이를 설정할 수 있습니다.

• set option PUBLIC.Min_Password_Length=integer;

syntax1 예)

syntax2 예)

GRANT CONNECT vs. sp_iqaddlogin

GRANT CONNECT로 등록한 사용자는 sysuserperm 이라는 시스템 테이블에만 등록되나 sp_iqaddlogin 으로 등록한 사용자는 IQ_USER_LOGIN_INFO_TABLE 이라는 시스템 테이블과 sysuserperm 이라는 시스템 테이블에 동시에 등록합니다. sp_iqcheckoptions

sp_iqmodifyadmin을 이용해서 사용자 관리 기능을 활성화 시킨 상태에서는 GRANT CONNECT로 등록된 사용자는 접속할 수 없습니다. 이를 가능하게 하려면 GRANT CONNECT로 등록된 사용자에 대해서도 추가적으로 sp_iqaddlogin을 실행해야 합니다.

사용자 관리 기능은 12.5 이전 버전에서 업그레이드한 사용자를 위해 비활성화 상태가 기본이나 새로 구성할 때나 사용자 관리 기능을 사용하고자 할 때는 sp_iqaddlogin의 사용을 권장합니다.

사용자 비밀번호 변경

사용자의 비밀번호를 변경할 수 있는 방법은 아래처럼 두 가지가 있으며 sp_iqpassword 는 12.5에서 새로 만들어진 기능 향상된 방법이기에 권장합니다.

dba 를 제외한 사용자는 자신의 비밀번호만 변경이 가능하며 dba의 비밀번호 분실시는 복구가 불가능하므로 주의 바랍니다.

syntax1
GRANT CONNECT TO user-name IDENTIFIED BY password;

• user-name : 이미 존재하는 사용자 계정
• password : 계정의비밀번호

첫번째 방법에 의해서는 해당 사용자의 비밀번호만 변경이 가능하며 이렇게 변경된 비밀번호는 만약 해당 사용자가 sp_iqaddlogin을 통해 비밀번호 만기일자 기능이 설정되어있다고 하더라도 그 효력이 없으니 주의하시기 바랍니다.

syntax2
sp_iqpassword caller-password, new-password [, user-name ];

• caller-password : 이 명령어를 실행하는 사용자의 현재 비밀번호
• new-password : 비밀번호 변경대상 사용자의 새로운 비밀번호
• user-name : 비밀번호 변경대상 사용자 계정

이렇게 변경된 비밀번호는 비밀번호 만기일자의 시작 시점이 됩니다.

syntax1 예)

syntax2 예)

사용자 속성변경

사용자의 변경 가능한 속성으로는 비밀번호 만기, 같은 사용자명을 가진 동시 접속자 수 제한이 있으며 dba 권한을 가진 사용자만이 실행할 수 있습니다.

비밀번호 만기 된 사용자 목록은 sp_iqlistexpiredpasswords를 통해 조회할 수 있습니다.

syntax
sp_iqmodifylogin user-name | all overrides,
number-of-connections ,password-expiration;

• user-name : 속성 변경대상 사용자 계정. all override 는dba 제외한 모든 사용자
• number-of-connections : 해당user-name 으로 동시에 접속할 수 있는접속자 수
• password-expiration : 비밀번호 만기
• password-expiration : 비밀번호 만기 일자

예)

sp_iqmodifylogin laura_smith, 20, 200;

사용자 임시 접속 차단

여러 가지 이유로 특정한 사용자의 접속을 임시로 차단할 수 있으며 또한 이렇게 차단된 사용자를 다시 접속 가능하게 할 수도 있습니다.

이렇게 임시 접속 차단된 사용자의 목록은 sp_iqlistlockedusers를 통해 조회할 수 있습니다.

syntax
sp_iqlocklogin user-name, lock | unlock

user-name : 임시 접속 차단 대상 사용자

예)
sp_iqlocklogin laura_smith, lock;

setuser

DBA 권한을 가진 사용자가 현재 세션을 종료하지 않고 다른 사용자의 역할을 대신할 수 있는 구조를 지원하고 있습니다.

다른 사용자의 역할을 대신할 때 옵션에 따라 그 사용자의 권한만 대신할 수도 있고 그 사용자에게 설정된 옵션도 대신할 수 있습니다.

syntax
SET SESSION AUTHORIZATION | SETUSER [ WITH OPTION ] user-name ;

예)

사용자 삭제

사용자를 삭제할 수 있는 방법은 아래처럼 두 가지가 있으며 sp_iqdroplogin은 12.5에서 새로 만들어진 기능 향상된 방법이기에 권장합니다.

REVOKE CONNECT를 통해 사용자를 삭제하면 sp_iqaddlogin을 통해 만들어진 사용 자의 정보는 IQ_USER_LOGIN_INFO_TABLE에는 그대로 살아있습니다. 이 테이블에서 의 정보까지 삭제하려면 두 번5>

syntax1
REVOKE CONNECT FROM user-name;

syntax2

syntax1 예)

syntax2 예)

그룹생성

개별 사용자들을 집합으로 묶어 관리를 편리하게 위해 만들 수 있으며 그룹에게 어떤 권한을 주면 그룹 구성원 모두가 그 권한을 상속 받게 됩니다. 그룹은 개별 사용자처럼 로그인 할 수도 있고 그냥 그룹으로 로그인 못 할 수도 있습니다.

SYBASE IQ에 자동으로 생성되는 다음과 같은 특별한 그룹이 존재합니다.

• sys : system 테이블, 뷰의 소유자로 비밀번호가 없고 SYBASE IQ로 로그인할 수는 없습니다.
• public : sys 그룹의 구성원이며 새로 생성된 모든 사용자는 디폴트로 public 그룹의 구성원이 됩니다.

syntax
GRANT GROUP TO user-name;

예)
GRANT CONNECT TO marketing;
GRANT GROUP TO marketing;
// connect 불가한 오직 그룹을 위한 사용자

예)
GRANT CONNECT TO marketing IDENTIFIED BY thebestteam;
GRANT GROUP TO marketing;
// connect 가 가능한 사용자겸 그룹

구룹 구성원 관리

특정한 그룹에 특정한 사용자 혹은 그룹을 해당 그룹의 구성원으로 등록할 수 있습니다.

syntax
GRANT MEMBERSHIP IN GROUP group-name TO user-name;

예)
GRANT MEMBERSHIP IN GROUP marketing TO laura_smith;

특정한 그룹에서 특정한 그룹 구성원을 제거할 수 있습니다.

syntax
REVOKE MEMBERSHIP IN GROUP group-name FROM user-name;

예)
REVOKE MEMBERSHIP IN GROUP marketing FROM laura_smith;

그룹삭제

그룹 구성원과 object를 소유하지 않은 그룹은 삭제될 수 있습니다.

syntax
REVOKE GROUP FROM user-name;

예)
REVOKE GROUP FROM marketing;
REVOKE CONNECT FROM marketing;

테이블 permission 관리

GRANT 문을 이용해서 테이블이나 뷰에 대한 ALTER, DELETE, UPDATE, INSERT, REFERENCES, SELECT permission을 사용자나 혹은 그룹에게 부여할 수 있습니다? 생성할 수 있는 것은 아닙니다.

ALL 이라는 keyword를 이용해 위에서 언급한 모든 permission을 부여할 수 있습니다.

syntax
GRANT ALL | ALTER | DELETE | INSERT | REFERENCES | UPDATE |
SELECT [ ( column-name [ , . . . . ] ) ] ON table-name TO user-name [,…]
[ with grant option ];

예)
GRANT SELECT ON customer TO laura_smith;
// customer 테이블을 읽을 수 있는permission 을laura_smith 에게 부여합니다.

REVOKE 문을 이용해서 인가된 permission 을 취소할 수 있습니다.

syntax
REVOKE ALL | ALTER | DELETE | INSERT | REFERENCES | UPDATE |
SELECT [ ( column-name [ , . . . . ] ) ] ON table-name FROM user-name [,…]

예)
GRANT SELECT ON customer TO laura_smith;
REVOKE SELECT ON customer FROM laura_smith;

stored procedure permission 관리

특정 stored procedure에 대해 실행할 수 있는 EXECUTE 권한을 사용자나 혹은 그룹에게 부여할 수 있습니다.

참고로 이런 권한 부여 받았다고 stored procedure를 생성할 수 있는 것은 아닙니다.

syntax
GRANT EXECUTE ON procedure-name TO user-name;

예)
GRANT EXECUTE ON my_procedure TO laura_smith;

REVOKE 문을 이용해서 인가된 permission 을 취소할 수 있습니다.

syntax
REVOKE EXECUTE ON procedure-name FROM user-name;

예)
REVOKE EXECUTE ON my_procedure FROM laura_smith;

권한 관리

사용자에게 부여할 수 있는 권한으로는 테이블이나 뷰 혹은 stored procedure와 같은 object를 생성할 수 있는 RESOURCE 권한과 dba 역할을 할 수 있는 DBA 권한이 있습니다.

GRANT를 통해 특정 사용자 혹은 그룹에게 RESOURCE나 dba 권한을 부여할 수 있습니다.

syntax
GRANT RESOURCE | DBA TO user-name;

예)
GRANT RESOURCE TO laura_smith;

REVOKE 문을 이용해서 부여된 권한을 취소할 수 있습니다.

syntax
REVOKE RESOURCE | DBA FROM user-name;

예)
REVOKE RESOURCE FROM laura_smith;

참고:사용자별 resource 제한 옵션

Max_Query_Time : 사용자별로 혹은 모든 사용자의 질의 사용시간을 제한할 수 있습니 다. 만약 설정된 시간보다 오래 수행되는 질의는 적절한 오류 메시지를 출력하고 취소됩니다. 기본 값은 질의의 수행시간에 제약이 없는 0입니다.

Max_Cartesian_Result : 사용자별로 혹은 모든 사용자의 질의중 Cartesian 조인의 결과가 설정된 건수보다 많다면 그 질의는 적절한 오류 메시지를 출력하고 취소됩니다. 기본값은 100,000,000 입니다.

Max_IQ_Threads_Per_Connection : 사용자가 connection당 이용할 수 있는 쓰레드의 수를 설정된 값으로 제한할 수 있습니다. 기본 값은 모든 사용자에게 72입니다.

Load_Memory_MB : 적재를 포함하여 입력,수정,삭재할 때 사용되는 heap 메모리의 한계 값을 설정된 값으로 제한할 수 있습니다. 기본 값은 0으로 제한이 없습니다.

Query_Temp_Space_Limit : 사용자별로 혹은 모든 사용자의 질의중 임시 결과 값을 저장하기 위해 설정된 값보다 많은 Temp Store를 사용하면 적절한 오류 메시지를 출력하고 취소됩니다. 기본 값은 2000MB 입니다.

Query_Rows_Returned_Limit : 사용자별로 혹은 모든 사용자의 질의중 결과 값으로 설정된 값보다 많은 건수를 산출하면 적절한 오류 메시지를 출력하고 취소됩니다. 기본 값은 0으로 제한이 없습니다.

참고: synonym 구현

다른 사용자의 테이블을 사용하기 위해서는 원칙적으로 owner.table-name으로 테이블이름을 기술하여야 합니다. 아래 예는 다른 사용자의 테이블 사용할 때도 owner의 기술없이 사용하고자 하는 경우의 절차입니다.

사용자겸 그룹을 하나 만들어서 그 사용자가 테이블을 생성합니다.
GRANT CONNECT TO marketing IDENTIFIED BY iq_is_fast; // by dba
혹은 sp_iqaddlogin marketing, iq_is_fast; // by dba
GRANT GROUP TO marketing; // by dba
GRANT RESOURCE TO marketing; // by dba

CREATE TABLE marketing_tbl … … ; // by marketing

dba는 사용자를 새로 만들어서 먼저 만든 그룹에 구성원으로 등록합니다.
GRANT CONNECT TO marketing_member IDENTIFIED BY iq_is_fast;
GRANT MEMBERSHIP IN GROUP marketing TO marketing_member;

dba나 marketing 사용자겸 그룹은 marketing_tbl 테이블의 SELECT 권한을 소속 구성원에게 부여합니다
GRANT SELECT ON marketing_tbl TO marketing_member;

이제 marketing_member라는 사용자는 marketing_tbl 테이블을 owner 명 없이 마치 자기가 만든 테이블처럼 직접 액세스 할 수 있습니다.
SELECT * FROM marketing_tbl;