SUNDB 사용자의 권한 분리
-- 권한부여 작업은 sysdba 계정을 사용한다.
[sunje@symphony conf]$ gsql --as sysdba
Copyright (c) 2010, 2014, SUNJESOFT Inc. All rights reserved.
Release Mercury.2.2.2 revision(14773)
Connected to SUNDB Database.
gSQL>
gSQL> \set linesize 300
gSQL> \set pagesize 1000
gSQL>
-- 사용할 Tablespace를 생성한다.
gSQL> CREATE TABLESPACE USR_DATA_TBS DATAFILE 'USR_DATA_TBS.dbf' SIZE 10M;
gSQL> CREATE TEMPORARY TABLESPACE USR_INDX_TBS MEMORY 'USR_INDX_TBS' SIZE 10m;
gSQL>
-- 관리자용 계정을 (app_adm) 생성한다. 계정생성시 Schema도 자동 생성한다.
gSQL> CREATE USER app_adm IDENTIFIED BY app_adm DEFAULT TABLESPACE USR_DATA_TBS TEMPORARY TABLESPACE MEM_TEMP_TBS WITH SCHEMA app_schema;
gSQL>
-- 사용자용 계정을 (app_usr) 생성한다. (Schema를 제외하고 생성한다)
gSQL> CREATE USER app_usr IDENTIFIED BY app_usr DEFAULT TABLESPACE USR_DATA_TBS TEMPORARY TABLESPACE MEM_TEMP_TBS WITHOUT SCHEMA;
gSQL>
-- 관리자 계정에 권한을 부여 한다.
gSQL> GRANT CREATE SESSION ON DATABASE TO app_adm;
gSQL> GRANT CREATE TABLE, CREATE INDEX, CREATE VIEW, CREATE SEQUENCE, ADD CONSTRAINT ON SCHEMA app_schema TO app_adm;
gSQL> GRANT CREATE OBJECT ON TABLESPACE USR_DATA_TBS TO app_adm;
gSQL> GRANT CREATE OBJECT ON TABLESPACE USR_INDX_TBS TO app_adm;
gSQL>
-- 사용자 계정에 권한을 부여한다.
gSQL> GRANT CREATE SESSION ON DATABASE TO app_usr;
gSQL> GRANT SELECT TABLE, INSERT TABLE, DELETE TABLE, UPDATE TABLE ON SCHEMA app_schema TO app_usr;
gSQL>
-- 생성된 권한을 확인해 본다.
gSQL> SELECT
A.GRANTOR_ID,
A.GRANTEE_ID,
B.AUTHORIZATION_NAME,
A.SCHEMA_ID,
C.SCHEMA_NAME,
A.PRIVILEGE_TYPE
FROM
SCHEMA_PRIVILEGES A,
AUTHORIZATIONS B,
DEFINITION_SCHEMA.SCHEMATA C
WHERE 1=1
AND A.GRANTEE_ID = B.AUTH_ID
AND A.SCHEMA_ID = C.SCHEMA_ID
;
GRANTOR_ID GRANTEE_ID AUTHORIZATION_NAME SCHEMA_ID SCHEMA_NAME PRIVILEGE_TYPE
---------- ---------- ------------------ --------- ----------------------- ---------------
1 2 SYS 1 DEFINITION_SCHEMA SELECT TABLE
1 2 SYS 2 FIXED_TABLE_SCHEMA SELECT TABLE
1 2 SYS 3 DICTIONARY_SCHEMA SELECT TABLE
1 2 SYS 4 INFORMATION_SCHEMA SELECT TABLE
1 2 SYS 5 PERFORMANCE_VIEW_SCHEMA SELECT TABLE
1 2 SYS 6 PUBLIC SELECT TABLE
1 5 PUBLIC 6 PUBLIC CREATE TABLE
1 5 PUBLIC 6 PUBLIC CREATE VIEW
1 5 PUBLIC 6 PUBLIC CREATE SEQUENCE
1 5 PUBLIC 6 PUBLIC CREATE INDEX
1 5 PUBLIC 6 PUBLIC ADD CONSTRAINT
1 5 PUBLIC 1 DEFINITION_SCHEMA SELECT TABLE
1 5 PUBLIC 2 FIXED_TABLE_SCHEMA SELECT TABLE
1 25 APP_ADM 27 APP_SCHEMA CREATE TABLE
1 25 APP_ADM 27 APP_SCHEMA CREATE VIEW
1 25 APP_ADM 27 APP_SCHEMA CREATE SEQUENCE
1 25 APP_ADM 27 APP_SCHEMA CREATE INDEX
1 25 APP_ADM 27 APP_SCHEMA ADD CONSTRAINT
1 26 APP_USR 27 APP_SCHEMA SELECT TABLE
1 26 APP_USR 27 APP_SCHEMA INSERT TABLE
1 26 APP_USR 27 APP_SCHEMA DELETE TABLE
1 26 APP_USR 27 APP_SCHEMA UPDATE TABLE
22 rows selected.
gSQL>
