오라클 구동-(3)사용자 생성

목차

1. 사용자 계정 생성

1-1. 관리자 접속

1-2. 사용자 조회

1-3. 사용자가 객체를 저장 할 테이블 스페이스 식별

1-4. 계정 생성

2. role 

2-1. role에 대해

2-2. role 생성

2-3. role에 권한 할당 및 조회

2-4. 사용자에게 role 할당 및 확인

2-5. 사용자 접속 확인

 

1. 사용자 계정 생성

1-1. 관리자 접속

sqlplus / as sysdba

- 관리자 권한으로 접속. dba그룹에 속한 계정은 누구나 이렇게 접속 가능.

- sysdba는 sys의 권한을 의미.

 

1-2. 사용자 조회

<사용자 조회 명령어>

SELECT username, default_tablespace, temporary_tablespace,
account_status, profile
FROM dba_users;

- User의 이름과 각 user의 여러 설정 사항을 조회한다.
- USERNAME : 사용자명
- DEFAULT_TABLESPACE : 기본으로 사용할 tablespace명
- TEMPORARY_TABLESPACE : 사용할 temporary tablespace명
- ACCOUNT_STATUS : 계정의 상태
- PROFILE : 사용 중인 profile명

SELECT username, default_tablespace, temporary_tablespace,
account_status, profile
FROM dba_users;

// 일반 사용자가 사용할 수 있는 계정이 없음을 확인

 

1-3. 사용자가 객체를 저장 할 테이블 스페이스 식별

SELECT tablespace_name, bytes, file_name FROM dba_data_files;

- 테이블 스페이스는 오라클의 저장 영역 구성을 의미한다. 

- 일반 사용자는 SYSTEM, SYSAUX, UNDOTBS, TEMP이외의 USERS만 사용 가능.

- 이전 문서에서 만들었던 ST와 INDX 테이블 스페이스 확인

 

1-4. 계정 생성

<계정 생성 명령어>

CREATE USER [사용자] 
IDENTIFIED BY [암호] 
DEFAULT TABLESPACE [테이블스페이스] 
TEMPORARY TABLESPACE [임시테이블스페이스] 
QUOTA UNLIMITED ON [테이블스페이스];

- user : 사용자 이름

- by soccer : 암호

- default 또는 temporary 테이블스페이스 : 사용자의 기본 테이블스페이스나 임시 테이블스페이스 식별

- quota : 사용자가 소유한 객체에 할당량 설정

CREATE USER st 
IDENTIFIED BY st 
DEFAULT TABLESPACE st 
TEMPORARY TABLESPACE temp 
QUOTA UNLIMITED ON st;

// 계정 생성

 

SELECT username, account_status FROM dba_users WHERE account_status = 'OPEN';

// 계정이 생성된 것을 확인

 

2. role

2-1. role에 대해

- 권한 관리 용이성

- 권한 관리를 유연하게 할 수 있음(실질적인 가장 큰 장점) 

- 권한의 선택적 가용성

- 운영 체제를 통한 부여

- 생성, 삭제, 수정, 권한 할당이 가능

- 오라클에서 미리 설정해놓은 role들은 유용하지만 가급적 사용하지 않는게 좋음

 

롤의 정보를 얻을 수 잇는 뷰

DBA_USERS : 데이터베이스의 모든 유저 확인 

DBA_ROLES : 데이터베이스의 모든 롤 확인

DBA_ROLE_PRIVS : 사용자 및 롤에 할당된 롤 확인

DBA_SYS_PRIVS :  사용자 및 롤에 할당된 시스템 권한 확인

DBA_TAB_PRIVS :  사용자 및 롤에 할당된 객체(object) 권한 확인

ROLE_ROLE_PRIVS : 롤에 할당된 롤 확인

ROLE_SYS_PRIVS : 롤에 할당된 시스템 권한 확인

ROLE_TAB_PRIVS : 롤에 할당된 객체 권한 확인

SESSION_ROLES : 사용자가 현재 활성화 한 롤 확인

 

<role 생성 명령어>

CREATE ROLE 
[IDENTIFIED BY <암호>];
- role을 생성

 

<role 삭제 명령어>

DROP ROLE ;
- role을 삭제

 

<role 할당 명령어>

 GRANT <role명> TO <user명 | role 명>;
- role을 user나 role에 할당.
- role을 role에 할당되는 경우 순환되게 할당해서는 안됨.

 

<role 조회 명령어>

SELECT role FROM dba_roles;

 

<role의 권한 조회 명령어>

SELECT role, privilege FROM role_sys_privsSELECT grantee, privilege 
FROM dba_sys_privs

 

<시스템 권한 할당 및 해제 명령어> 
GRANT <시스템 권한>,<시스템 권한> .......
TO [<user명 | role명>,<user명 | role명> ...... | PUBLIC];
[WITH ADMIN OPTION];
- User 또는 role에 시스템 권한을 할당.
- PUBLIC : DB의 모든 user에게 권한을 할당.
- WITH ADMIN OPTION : 할당된 권한을 SYS 관리자와 동일하게 할당하거나 해제 가능.

 

REVOKE <시스템 권한>,<시스템 권한> .......
FROM [<user명 | role명>,<user명 | role명> ...... | PUBLIC];
- User 또는 role에 시스템 권한을 해제

 

<user 또는 role에 할당된 시스템 권한 조회 명령어>

SELECT grantee, privilege, admin_option FROM dba_sys_privs

- GRANTEE : 권한을 할당 받은 user
- PRIVILEGE : 권한
- ADMIN_OPTION : 할당 받은 권한을 관리자와 동일하게 사용할 수 있는지 여부

 

<user 또는 role에 할당된 role 조회 명령어>

SELECT grantee, granted_role FROM dba_role_privs;

 

2-2. role 생성

create ROLE role_resource;

create ROLE role_connect;

create ROLE role_create_view;

// 기존에 존재하는 resource, connect를 대체할 role_resource, role_connect의 이름으로 role과 create session 권한을 부여할 role_create_view; 생성

 

SELECT role FROM dba_roles
WHERE role IN ('ROLE_RESOURCE', 'ROLE_CONNECT', 'ROLE_CREATE_VIEW');

// 생성된 role 확인.

 

2-3. role에 권한 할당 및 조회

SELECT grantee, privilege
FROM dba_sys_privs
WHERE grantee in ('RESOURCE','CONNECT');

// 새롭게 role에 할당해 주기 위한 resource, connect 롤의 권한 확인

GRANT create sequence, create trigger, create cluster, create procedure, create type, create operator, create table, create indextype
TO role_resource;

 

GRANT set container, create session TO role_connect;

 

GRANT create view TO role_create_view;

// role_resource, role_connect, role_create_view에 시스템 권한 부여

SELECT grantee, privilege FROM dba_sys_privs
WHERE grantee IN('ROLE_RESOURCE', 'ROLE_CONNECT', 'ROLE_CREATE_VIEW');

// role에 할당된 시스템 권한 조회. 의도대로 들어갔는지 확인 

 

2-4. 사용자에게 role 할당 및 확인 

CREATE ROLE role_st;

// 일반적으로 사용자에게 role을 여러개 부여하는 것은 하나의 role을 부여하는 것이 바람직

// 따라서 부여할 role이 통합 될 role을 생성. 여기서는 사용자의 이름인 st를 참고해서 role_st로 생성 

 

GRANT role_resource, role_connect, role_create_view TO role_st;

// role_st에게 role_resource, role_connect, role_create_view 롤을 부여

SELECT role, granted_role FROM role_role_privs
WHERE role IN ('ROLE_ST');

// role_st에 할당된 role 조회

 

SELECT grantee, granted_role FROM dba_role_privs
WHERE grantee =  'ST';

// st 사용자에게 할당된 role 조회. 문제없이 적용된 것을 확인.

GRANT role_st TO st;

// st 사용자에게 role_st 롤을 할당

 

SELECT grantee, granted_role FROM dba_role_privs
WHERE grantee =  'ST';

// st 사용자에게 할당된 role 조회

 

2-5. 사용자 접속 확인 

sqlplus st@oracle/st

// 사용자의 접속 확인은 network상 접속여부를 확인.

// tns명인 oracle로 정상적으로 접속이 되는 것을 확인할 수 있다.