oracle 사용자 계정 생성 및 권한 생성

오랜만에 오라클 사용  

--권한 관리

1. 사용자와 관리자의 권한 분석.

2. 관리자 : sys, system 계정

--명령프롬프트에서 SQLPLUS 사용시
sqlplus sys/암호 as sysdba

--SQL 명령줄 실행에서 SQLPLUS 사용시
conn sys/암호 as sysdba

3. 관리자 암호 변경
--명령프롬프트에서 실행
DOS>sqlplus "/as sysdba" --> 암호 묻지 않고 접속됨
SQL>ALTER USER sys IDENTIFIED BY 새암호;
SQL>conn sys/새암호 as sysdba;

4. 사용자 계정 생성(관리자)

--사용자 생성
CREATE USER 사용자계정명 IDENTIFIED BY 암호;
-->주의) 사용자 계정이 생성되면 적절한 권한 부여 필요.

CREATE USER test01 IDENTIFIED BY test01
         DEFAULT TABLESPACE USERS;

--사용자 명단 확인
SELECT * FROM all_users;

--관리자 전용 사용자 명단 확인
SELECT * FROM dba_users;

--사용자 삭제
DROP USER test01;
-->주의) 접속된 상태에서는 삭제 불가.

5. 사용자에게 시스템 권한 부여.
시스템 권한 : 오라클 자원을 사용할 수 있는 권한. CREATE SESSION 외에 여러가지 권한 존재.

--권한 부여
GRANT 시스템권한 TO 사용자계정명;

GRANT CREATE SESSION TO test01;  -- 접속 권한 부여, CONNET 롤로 대체 가능.
GRANT RESOURCE TO test01;  -- 롤 권한 부여

GRANT CONNET, RESOURCE TO test01; -- 권한 부여는 중복으로 가능함.

6. 사용자 로그온 및 객체 생성

CREATE TABLE aa
(
 col1 VARCHAR2(10)
);
-->주의) 객체가 생성된 후에는 사용자 계정 삭제가 안될 수 있다.

--객체를 가지고 있는 사용자 계정 삭제
DROP USER test01; -- X
DROP USER test01 CASCADE; -- O

7. 테이블스페이스 지정

테이블스페이스 : 사용자가 객체를 생성 및 관리할 수 있는 전용 공간.
USERS 테이블 스페이스는 기본적으로 생성되는 사용자 전용 공간이다.

-관리자 화면전용-

CREATE USER angdoll IDENTIFIED BY angdoll;

ALTER USER angdoll
       DEFAULT TABLESPACE USERS
       QUOTA 100M ON USERS;
-->angdoll 계정이 사용할 수 있는 공간의 크기를 100M로 제한.

--용량확인
SELECT * FROM dba_ts_quotas;

GRANT CREATE SESSION TO angdoll;
GRANT CREATE TABLE TO angdoll;

--> angdoll 계정으로 로그인한 후 테이블 생성 가능 확인.

8. 암호 변경 및 제한

--관리자가 사용자의 암호 변경
ALTER USER 사용자계정 IDENTIFIED BY 새암호;

--자기 자신이 자신의 암호 변경
ALTER USER 사용자계정 IDENTIFIED BY 새암호;

--관리자가 사용자의 암호 만료시킬 수 있다.
이 경우 사용자는 암호를 의무적으로 변경해야됨.
ALTER USER 사용자계정 PASSWORD EXPIRE;
-->SQL Developer에서는 새암호 자동으로 변경 되지 않는다.
SQLPLUS에서 접속하면 새암호 변경하라고 함.

--관리자가 사용자 계정을 잠금.
일시적으로 접근 금지를 시킬 때 한다.
ALTER USER 사용자계정 ACCOUNT LOCK;

--관리자가 사용자 계정을 잠금 해제.
ALTER USER 사용자계정 ACCOUNT UNLOCK;

9. WITH ADMIN OPTION : 권한 부여시 승계가 가능하도록 지정.

GRANT 시스템권한 TO 사용자계정 WITH ADMIN OPTION;
-->관리자가 사용자1에게 권한 부여하면, 사용자1은 다른 사용자에게 동일한 권한을 부여할 수 있다.

--관리자
CREATE USER user01 IDENTIFIED BY user01;
CREATE USER user02 IDENTIFIED BY user02;

--관리자
GRANT CREATE SESSION TO user01 WITH ADMIN OPTION;

--USER01로 로그인한 후 USER02에게 권한 부여
GRANT CREATE SESSION TO user02;
-->USER02가 로그인 가능한지 확인.

10. 권한 회수

REVOKE 시스템권한 FROM 사용자계정;

--관리자
REVOKE CREATE SESSION FROM user01, user02;
-->user01, user02가 로그인 불가능한지 확인.

11. 객체 권한 : 사용자가 소유한 객체에 대한 권한 부여.

--객체 권한 부여 전

--관리자
--사용자의 객체 접근
SELECT * FROM hr.jobs; -- O
SELECT * FROM scott.insa; -- O

--HR 계정
--사용자의 객체 접근
SELECT * FROM hr.jobs; -- O
SELECT * FROM scott.insa; -- X

--SCOTT 계정
--사용자의 객체 접근
SELECT * FROM hr.jobs; -- X
SELECT * FROM scott.insa; -- O

--> 객체 권한을 부여하면 가능하다.

--객체 권한(SELECT, INSER, UPDATE, DELETE) 부여
GRANT 객체권한 ON 객체명 TO 사용자계정;

--HR 계정
GRANT SELECT ON jobs TO scott;
GRANT SELECT, INSERT, UPDATE, DELETE ON jobs TO scott; --> 동시 권한 부여 가능함.

--SCOTT 계정
SELECT * FROM hr.jobs; -- O

--객체 권한 회수
REVOKE 객체권한 ON 객체명 TO 사용자계정;

--부여된, 부여한 객체 권한 확인
SELECT * FROM user_tab_privs;

GRANTOR : 부여한 객체
GRANTEE : 부여된 객체

12. 롤 : 권한의 집합체

GRANT 롤 TO 사용자계정;

GRANT CONNECT, RESOURCE TO scott;
--> CONNECT 롤은 오라클 접속 권한
--> RESOURCE 롤은 대표적인 여러가지 객체 생성 권한. 단 , VIEW는 제외.

GRANT CREATE VIEW TO scott;

--관리자 권한 부여
GRANT DBA TO scott;
--> DBA 롤은 관리자 권한 모두를 가진 롤.

-- 간단 테이블 및 자동증가값 설정

create table st_member
(
  user_seq number not null primary key,
  user_id varchar(30) not null,
  user_pwd varchar(30) not null,
  user_name varchar(50) not null,
  user_date date not null
);

create sequence m_seq increment by 1 start with 1