사용권한 : 시스템 권한(Lock, Role)

권한 : 관리자가 각 개정별로 사용할 수 있는 작업의 범위를 지정해주는 것

1) 시스템 권한 : 관리자가 주로 작업

- 계정관리 : 생성, 수성, 삭제, 공용동의어 생성 등

2) 객체 권한 : 각 계정이 관리(소유자가 관리)

- 사용권한 : 테이블, 뷰, 시퀀스,,,

Q1. [계정관리]

형식) 특정계정에서 암호를 변경하라고 권고 : alter 구문

alter user 수정할 계정명 password expire; // 새암호로 변경할 수 밖에 없다.

1) hong 계정에게 암호를 변경하도록 설정

SQL> alter user hong password expire;

사용자가 변경되었습니다.

2) hong 연결

SQL> conn hong/h1234

ERROR:

ORA-28001: the password has expired // 암호가 만료되었기에 때문에 발생하는 베세지

hong에 대한 비밀번호를 변경합니다.

새 비밀번호:  // 타이핑해도 입력이 안되는 것처럼 보임

새 비밀번호 다시 입력:

비밀번호가 변경되었습니다.

연결되었습니다.

SQL> conn hong/h12345

연결되었습니다.

SQL>

[Lock 걸기]

형식1) 락 풀어주기/걸기

alter user 락을 풀어줄 계정명 account unlock|lock;

-> 이전에 계정을 이미 사용한 적이 있는 경우(로그인 한 적이 있는 경우)

형식2) 새 암호를 부여하여 락 풀어주기

alter user 락을 풀계정명 identified by 새암호 acouunt unlack

-> 계정은 존재하는데 한번도 사용한 적이 없는 경우

Q2. 어떤 계정을 사용하는 직원이 회사를 퇴직하거나, 휴가를 가는 경우 그 계정을 임시로 사용하기 못하게 하는 경우

-> 관리자가 락을 걸기

EX. 직원이 회사를 퇴직, 그 업무를 인수하기 위해 그 계정을 다시 사용 -> 암호만 변경해서 사용하는 경우

1) Lock에 걸린 계정

SQL> conn hr/hr1234

ERROR:

ORA-28000: the account is locked

경고: 이제는 ORACLE에 연결되어 있지 않습니다.

-> 락이 걸린 상태로 임시로 휴직 또는 퇴직하여 계정을 사용하지 못하게 함

-> 관리자가 락이 걸린 계정을 다시 사용할 수 있도록 처리, 락을 풀어준다.

2) hr 계정에 새암호를 부여하여 락 풀기

SQL> conn sys/sys12345 as sysdba

연결되었습니다.

SQL> alter user hr identified by hr1234 account unlock;

사용자가 변경되었습니다.

SQL> conn hr/hr1234

연결되었습니다.

Q3. 특정 계정을 일시적으로 락 걸기

형식) 락 걸기

alter user 락걸계정명 account lock;

1) hong 계정 락 걸기

SQL> conn sys/sys12345 as sysdba

연결되었습니다.

SQL> alter user hong account lock;

사용자가 변경되었습니다.

SQL> conn hong/h12345

ERROR:

ORA-28000: the account is locked

!) 관리자의 암호를 잃어버린 경우(원격보다는 로컬에서 사용)

- 인증절차를 거치지 않고 로그인하는 경우

conn / as sysdba -> 로컬에서 사용

아래와 같음

conn sys/sys1234 as sysdab 

SQL> conn / as sysdba

연결되었습니다.

SQL> show user

USER은 "SYS"입니다

Q4. 사원이 퇴사하여 그 사원이 사용했던 계정을 삭제할 경우

형식1) 작업한 기록이 없는 계정 삭제

drop user 삭제시킬 유저명

형식2) 작업한 기록이 있는 계정 삭제

drop user 삭제시킬 유저명 cascade <- 이 계정이 작업한 내용까지 같이 삭제하라는 옵션

1) hong 계정 삭제

SQL> drop user hong;

사용자가 삭제되었습니다.

2) test1 계정 삭제하려는 경우(데이터 작업을 한 계정)

SQL> drop user test1

  2  ;

drop user test1

*

1행에 오류:

ORA-01922: 'TEST1'(을)를 삭제하려면 CASCADE를 지정하여야 합니다

-> 작업한 내용까지 같이 삭제하라는 오류메세지로 명령문에 cascade 옵션을 추가해야 함

SQL> drop user test1 cascade;

사용자가 삭제되었습니다.

---

Q5. [시스템 권한] 

- system_privilege_emp 테이블

SQL> select * from system_privilege_emp

 PRIVILEGE NAME                                       PROPERTY

---------- ---------------------------------------- ----------

        -3 ALTER SYSTEM                                      0

        -4 AUDIT SYSTEM                                      0

....

      -318 INSERT ANY MEASURE FOLDER                         0

      -319 CREATE CUBE BUILD PROCESS                         0

      -320 CREATE ANY CUBE BUILD PROCESS                     0

208 개의 행이 선택되었습니다. -> 관리자는 208개의 권한을 가지고 있다.

-> 다른사람에게 권한을 줄 수 있다.

[자주 쓰는 권한]

-5 CREATE SESSION : 로그인권한(CONNECT)

-20 CREATE USER : 계정생성권한

-22 ALTER USER : 수정권한(LOCK/UNLACK/새암호부여)

-23 DROP USER : 삭제권한

-83 SYSDBA : 관리자 권한

-85 CREATE PUBLIC SYNONYM : 공용동의어 생성

-86 DROP PUBLIC SYNONYM : 공용동의어 삭제

-90 CREATE VIEW (뷰를 생성)

-105 CREATE SEQUENCE  (시컨스 생성)

-125 CREATE ROLE : 롤을 생성할 수있는 권한

-180 CREATE TYPE : 자료형(varchar2,number,,,->객체연관)

Q6. [ROLE]

- 오라클에서 권한을 줄 때 롤을 사용 : 권한을 담아놓은 그릇(리눅스에서 말하는 그룹)

- 단순, 반복적인 권한을 롤을 이용하여 편리하게 권한을 부여

- 한꺼번에 많은 권한을 계정에게 부여할 수 있고 한꺼번에 권한을 회수할 수 있다.

*role 테이블명

- dba_roles : 롤의 목록

- dba_sys_privs : 롤에 부여된 시스템 권한 확인

1) 시스템(오라클)에서 현재 사용중인 롤을 조회하는 SQL문을 작성하시오

-> 현재 어떤 롤(dba_roles)이 있는지, 어떤 권한이 들어있는지 확인

select * from dba_roles;

ROLE                           PASSWORD AUTHENTICAT

------------------------------ -------- -----------

CONNECT : create session(접속권한)

RESOURCE : create table 권한 외

DBA : 관리자롤

*dba_xxx, sys_xxx, v$_xxx로 시작하는 테이블은 관리자만 사용이 가능

Q7. 특정 롤에 부여된 시스템 권한의 종류를 확인하는 SQL 구문

-> 파일(save)로 이용(view) (dba_sys_privs)

1) rolesearch 뷰 만들기

create or replace view v_rolesearch

as select * from dba_sys_privs

where grantee='CONNECT';

뷰가 생성되었습니다.

SQL> select * from v_rolesearch;

GRANTEE(롤이름)                 PRIVILEGE(권한종류)                       ADM

------------------------------ ---------------------------------------- ---

CONNECT                        CREATE SESSION                           NO

-> 즉, grant connect to 계정명1,, = grant create session to 계정명1,,

2) 파라미터 질의로 RESOURCE 확인

SQL> select * from dba_sys_privs

  2  where grantee=upper('&grantee');

grantee의 값을 입력하십시오: resource

구   2: where grantee=upper('&grantee')

신   2: where grantee=upper('resource')

GRANTEE                        PRIVILEGE                                ADM

------------------------------ ---------------------------------------- ---

RESOURCE                       CREATE TRIGGER                           NO

RESOURCE                       CREATE SEQUENCE                          NO

RESOURCE                       CREATE TYPE                              NO

RESOURCE                       CREATE PROCEDURE                         NO

RESOURCE                       CREATE CLUSTER                           NO

RESOURCE                       CREATE OPERATOR                          NO

RESOURCE                       CREATE INDEXTYPE                         NO

RESOURCE                       CREATE TABLE                             NO

8 개의 행이 선택되었습니다.

-> role인 resource 권한을 주면

create trigger, create sequence, creste type,, 총 8개의 시스템 권한(priveilege)가 부여됨

-> 즉, 한꺼번에 많은 권한을 부여할 때 편리하다.

3) DBA가 가지고 있는 권한 확인

GRANTEE                        PRIVILEGE                                ADM

------------------------------ ---------------------------------------- ---

DBA                            CREATE ANY SYNONYM                       YES

DBA                            ALTER ANY CLUSTER                        YES

DBA                            DROP USER                                YES

DBA                            MANAGE TABLESPACE                        YES

202 개의 행이 선택되었습니다.

4) 롤에 부여된 시스템 권한 확인하는 명령문 파일로 저장하기

SQL> save L:\2017NCS\1.db\print\prisrch.txt

file L:\2017NCS\1.db\print\prisrch.txt(이)가 생성되었습니다

---

Q8. [롤을 생성하기] 

- 관리자만 생성 가능

형식) 롤 생성

create role 생성시킬 롤이름;

Q9. 만들어진 롤에 권한을 넣어준 뒤 계정에게 롤을 이용하여 권한을 부여하라.

     (계정생성, 계정수정)

형식) 롤에 권한 넣기

grant 권한종류명,,,

to 롤이름,롤이름2,,,

-> 동일한 권한을 여러개의 롤에게 동시에 부여가 가능(ex. RESOURCE)

Q10. 권한이 부여된 롤을 계정에게 부여하고, 그 권한이 되는지 확인해라.

1) 권한이 부여된 롤을 계정에게 부여하기

grant 롤이름1,롤이름2,,

to 계정명1,계정명2,

EX) 로그인을 할 수 있는 usercreate 롤을 생성하고 scott, test01 계정에서 권한을 부여하라.

1) 롤 생성 / 확인

SQL> create role usercreate;

롤이 생성되었습니다.

SQL> select * from dba_roles;

ROLE                           PASSWORD AUTHENTICAT

------------------------------ -------- -----------

CONNECT                        NO       NONE

RESOURCE                       NO       NONE

DBA                            NO       NONE

SELECT_CATALOG_ROLE            NO       NONE

EXECUTE_CATALOG_ROLE           NO       NONE

.....

ROLE                           PASSWORD AUTHENTICAT

------------------------------ -------- -----------

USERCREATE                     NO       NONE

56 개의 행이 선택되었습니다.

2) 롤에 권한 넣기(계정생성, 계성수정 권한) 

SQL> grant create user, alter user to usercreate;

권한이 부여되었습니다.

SQL> @L:\2017NCS\1.db\print\prisrch.txt // 롤에 제대로 권한이 들어갔는지 확인

grantee의 값을 입력하십시오: usercreate

구   2: where grantee=upper('&grantee')

신   2: where grantee=upper('usercreate')

GRANTEE                        PRIVILEGE                                ADM

------------------------------ ---------------------------------------- ---

USERCREATE                     CREATE USER                              NO

USERCREATE                     ALTER USER                               NO

3) 권한이 부여된 롤(usercreate)을 scott, test01에게 부여하기

SQL> grant usercreate to scott,test01;

권한이 부여되었습니다.

4) scott으로 계정생성하고 수정하기 : imsi/t1234 부여되는지 확인 후, 비밀번호 변경

SQL> conn scott/tiger

연결되었습니다.

SQL> create user imsi identified by t1234;

사용자가 생성되었습니다.

SQL> alter user imsi identified by i1234;

사용자가 변경되었습니다.

SQL> drop user imsi;

drop user imsi

*

1행에 오류:

ORA-01031: 권한이 불충분합니다

-> scott은 계정삭제 원한을 부여받지 않았다.

※ 새로 생성된 imsi 계정은 로그인이 되지 않는다.

   scott에게 connect(create session) 권한이 없기 때문이다.

형식) 롤 삭제하기

drop role 삭제시킬 롤 이름;

Q11. 관리자가 usercreate 롤을 삭제시킨다면 scott, test01은 그 권한을 계속 사용 가능한가?

-> 롤을 통해서 권한을 부여받은 모든 계정은 롤이 삭제되면 권한도 자동으로 회수(revoke)가 된다.

1) 롤 삭제하기

SQL> conn sys/sys12345 as sysdba

연결되었습니다.

SQL> drop role usercreate;

롤이 삭제되었습니다.

2) 롤을 통해 부여받은 권한이 유지되는지 확인

SQL> conn scott/tiger

연결되었습니다.

SQL> create user test02 identified by t1234;

create user test02 identified by t1234

                                 *

1행에 오류:

ORA-01031: 권한이 불충분합니다

-> 롤이 삭제되면 그 롤을 통해 받은 권한도 회수가 된다.

Q12. 관리자가 권한 일부를 위임하기 [with admin option]

형식)

grant 롤이름1,롤이름2,,

to 계정명1,계정명2,

with admin option

-> 부여받은 권한에 한해서만 다른 계정에게 권한을 줄 수 있다.

EX1) aaa, bbb, ccc 계정을 생성(암호는 계정명과 동일하게)

각자 로그인에서 자원을 생성할 수 있는 권한을 부여(connect, resource, ccc제외)

bbb 계정은 ccc계정에게 권한을 줄 수 있는 관리자 권한 일부 부여(bbb가 ccc에게 connect, resoucr 권한 부여하기)

1) aaa 계정생성 및 권한 부여

SQL> create user aaa identified by aaa;

사용자가 생성되었습니다.

SQL> grant connect, resource to aaa;

권한이 부여되었습니다.

2) bbb 계정생성, 권한부여 및 관리자 권한

SQL> create user bbb identified by bbb;

사용자가 생성되었습니다.

SQL> grant connect, resource to bbb with admin option;

권한이 부여되었습니다.

-> 다른 계정에게 connect, resource 권한을 줄 수 있다.

3) ccc 계정생성

SQL> create user ccc identified by ccc;

사용자가 생성되었습니다.

4) aaa 권한 확인하기

SQL> conn aaa/aaa

연결되었습니다.

SQL> grant connect, resource to ccc;

grant connect, resource to ccc

*

1행에 오류:

ORA-01031: 권한이 불충분합니다

5) bbb 권한 확인하기

SQL> conn bbb/bbb

연결되었습니다.

SQL> grant connect, resource to ccc;

권한이 부여되었습니다.

-----------------------------

SQL> grant create sysnonym to ccc;

grant create sysnonym to ccc

      *

1행에 오류:

ORA-00990: 권한이 부적합합니다

-> bbb는 connect, resource에 대한 관리자 권한을 부여받았지만

create sysnonym에 대한 관리가 권한은 부여받지 않았다.

6) ccc 로그인/자원생성

SQL> conn ccc/ccc

연결되었습니다.

SQL> create table abc(id number);

테이블이 생성되었습니다.

Q13. 관리자가 bbb에게 부여한 관리자 권한을 회수(revoke)하면 bbb를 통해 권한을 받은 ccc의 권한은 유지가 되는가?

1) 권한 회수하기

SQL> conn / as sysdba

연결되었습니다.

SQL> revoke connect, resource from bbb;

권한이 취소되었습니다.

2) ccc 계정이 권한이 회수되었는지 확인

SQL> conn ccc/ccc

연결되었습니다.

SQL> create table abc2(id varchar2(10));

테이블이 생성되었습니다.

-> 생성된 계정은 자기가 부여받은 권한범위 내에서 권한을 다른 계정에게 위임할 수 있다.

-> 권한을 회수당해도 위임받는 계정에게는 영향이 없다.

-> 그대로 권한 행사 가능