Data Base/Linux

231220 Linux_user, 객체, 권한, ROLE 관리

잇꼬 2023. 12. 20. 18:09
728x90
반응형
SMALL

schema 와 user의 차이점

schema user
1. 특정 유저가 소유한 모든 object 를 포함하는 의미
2. 일부 응용 프로그램이나 설정에서 사용되며, 파일이나 디렉터리의 구조를 설명할 때 쓰이는 용어		 1. 오라클 계정, user 이름
2. 시스템에 로그인하거나 다영한 작업을 수행하는 객체



# 유저 생성(문법)

CREATE USER 유저이름
IDENTIFIED BY 암호
DEFAULT TABLESPACE 데이터스페이스 이름 : 업무팀에 따라 지정
TEMPORARY TABLESPACE 임시 데이블스페이 이름 : sort 수행, 업무별로 지정한다. 일반적으로 temp에 지정
QUOTA unlimited ON 데이터스페이스 이름 | QUOTA 1M ON 데이터스페이스 이름
PASSWORD EXPIRE : 암호 설정 기간(ex 암호 만기)
ACCOUNT LOCK | UNLOCK(기본값)
PROFILE profile | DEFAULT(기본값: 아무것도 지정해주지 않는 상태)


# 유저 수정 

ALTER USER 유저이름
IDENTIFIED BY 암호
DEFAULT TABLESPACE 데이터스페이스 이름 : 업무팀에 따라 지정
TEMPORARY TABLESPACE 임시 데이블스페이 이름 : sort 수행, 업무별로 지정한다. 일반적으로 temp에 지정
QUOTA unlimited ON 데이터스페이스 이름 | QUOTA 1M ON 데이터스페이스 이름
PASSWORD EXPIRE : 암호 설정 기간(ex 암호 만기)
ACCOUNT LOCK | UNLOCK(기본값)
PROFILE profile | DEFAULT(기본값: 아무것도 지정해주지 않는 상태)


# 유저 삭제

1) OBJECT를 생성한 것이 없을 경우 : 해당 유저는 스키마 

DROP USER 유저이름;


2) 유저에 속한 OBJECT가 있을 경우 유저를 삭제하면 오류발생
# OBJECT 를 먼저 찾아서 삭제한 후 유저 삭제하면 된다.

# CASCADE 옵션을 사용하면 유저 삭제하기 전에 그 유저가 생성한 OBJECT 찾아서 삭제한 후 유저 삭제한다. 

DROP USER 유저이름 CASCADE;

- USER를 잘못 삭제하면 불안정한 복구해야 한다. 조심해서 삭제할 것

SELECT property_name, property_value
FROM DATABASE_PROPERTIES;

 

SELECT * FROM dba_tablespaces;

 


# default tablespace 로 지정되어 있는 테이블스페이스는 삭제할 수 없다. 

DROP tablespace user_tbs INCLUDING CONTENTS AND DATAFILES;


# default temporary tablespace 로 지정되어 있는 테이블스페이스는 삭제할 수 없다. 

DROP tablespace user_temp INCLUDING CONTENTS AND DATAFILES;

# 변경 

ALTER DATABASE DEFAULT TABLESPACE users;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;


# 삭제가능 

DROP tablespace user_tbs INCLUDING CONTENTS AND DATAFILES;
DROP tablespace user_temp INCLUDING CONTENTS AND DATAFILES;

 

# SYS SESSION 


[oracle1@oracle ~]$ sqlplus / as sysdba
SQL> DROP tablespace user_temp INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
tip) sql-developer에서 로딩이 오래 걸릴경우, session 접속해지하고 진행할것

# 삭제한 후 확인 

SELECT * FROM dba_tablespaces;


권한 관리 방법(순서대로 진행할 것)
1. SYS SESSION
# sys session, sql developer 
# tablespace, temporary tablespace 생성 

CREATE TABLESPACE insa_tbs
DATAFILE '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' SIZE 10M AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

CREATE TEMPORARY TABLESPACE insa_temp
TEMPFILE '/u01/app/oracle/oradata/ora11g/insa_temp01.dbf' SIZE 5M AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT MANUAL;

 

# 생성 확인

SELECT * FROM dba_tablespaces;

 

SELECT * FROM dba_data_files;

SELECT * FROM dba_temp_files;


# 유저 생성 

create user insa
identified by oracle
DEFAULT TABLESPACE insa_tbs
TEMPORARY TABLESPACE insa_temp
QUOTA 1m on insa_tbs;


# 유저 확인 

SELECT * FROM dba_users WHERE username = 'INSA';


# 유저에 대한 quotas 로 확인 

SELECT * FROM dba_ts_quotas WHERE username = 'INSA';



2. INSA SESSION
[oracle1@oracle ~]$ sqlplus insa/oracle

 

■ 권한(privilege) 관리
- 권한은 특정한 SQL문을 실행하거나 다른 유저가 소유한 객체(OBJECT)에 대해서 액세스 할 수 있는 권한이다.

  정의 권한 관리
SYSTEM
권한		 데이터베이스에 영향을 줄 수 있는 권한 SYS
OBJECT
권한		 다른 유저가 소유한 객체(object)에 대해서 액세스할 수 있는 권한 SYS, 객체 소유자


3. SYS SESSION 
# 권한 부여 

GRANT create session TO insa;


# 확인 

SELECT * FROM dba_sys_privs WHERE grantee = 'INSA';


4. INSA SESSION 
# 권한 부여 후 로그인
[oracle1@oracle ~]$ sqlplus insa/oracle


SQL> select * from user_sys_privs;

USERNAME                PRIVILEGE                  ADM
------------------------------ -------------------------------- -----------
INSA                           CREATE SESSION          NO

                                   (로그인 정보)             


SQL> select * from user_users;


SQL> select default_tablespace, temporary_tablespace from user_users;



SQL> select * from user_ts_quotas;




# table 생성
SQL> create table insa_tbs(id number, name varchar2(30));
create table insa_tbs(id number, name varchar2(30))
*
ERROR at line 1:
ORA-01031: insufficient privileges : 권한x

5. SYS SESSION  

GRANT create table To insa;
select * from dba_sys_privs where grantee = 'INSA';



 # WITH ADMIN OPTION으로 받은 시스템 권한에 대해서는 내가 다른 유저들한테 권한 부여 및 권한 취소할 수 있다.
- SYSTEM으로 권한부여한 것만 부여가능하다.
- CREATE TABLE 을 INSA가 SYS처럼 다른 유저에게 권한부여 및 권한취소 가능
- 연계관계 가능하나, 권한 취소는 각각으로 해야 하니 위험부담성이 크다. 

GRANT create table To insa WITH ADMIN OPTION;
select * from dba_sys_privs where grantee = 'INSA';

6. INSA SESSION 
SQL> SELECT * FROM user_sys_privs;


# table 생성 및 확인
# 테이블을 생성하면 그 순간은 딕셔러니에 정보만 저장한다.
- 설계도만 갖고 있다. 
- 실제 segment 는 생성되지 않는다.
SQL> create table insa_tba(id number, name varchar2(30)); -- 설계도면만 생성


SQL> select tablespace_name from user_tables where table_name = 'INSA_TBA';



7. SYS SESSION 
# 보여지지 않음, DML 작업을 진행해야 확인 가능

select * from dba_segments where owner = 'INSA' and segment_name = 'INSA_TBA';
select * from dba_extents where owner = 'INSA' and segment_name = 'INSA_TBA';


8. INSA SESSION 
SQL> INSERT INTO insa_tba(id, name) values(1, 'james');

 



9. SYS SESSION  

select * from dba_segments where owner = 'INSA' and segment_name = 'INSA_TBA';
select * from dba_extents where owner = 'INSA' and segment_name = 'INSA_TBA';

dba_segment
dbs_extents

10. INSA SESSION 
SQL> ROLLBACK;



11. SYS SESSION  

select * from dba_segments where owner = 'INSA' and segment_name = 'INSA_TBA';
select * from dba_extents where owner = 'INSA' and segment_name = 'INSA_TBA';

- ROLLBACK을 해도 dba_segment, dba_extents에서 확인가능

# 유저생성 

CREATE USER insa_buha
IDENTIFIED BY oracle
DEFAULT TABLESPACE insa_tba
TEMPORARY TABLESPACE insa_temp
QUOTA 1m ON insa_tbs
PASSWORD EXPIRE;

 

# 유저 권한 부여

GRANT CREATE SESSION TO insa_buha;


# 생성 확인

SELECT * FROM dba_tablespaces WHERE username = 'INSA_BUHA';

 

SELECT * FROM dba_ts_quotas WHERE username = 'INSA_BUHA';

SELECT * FROM dba_sys_privs WHERE grantee = 'INSA_BUHA';


12. INSA_BUHA SESSION
login as: oracle1
oracle1@192.168.56.108's password:
Last login: Tue Dec 19 21:00:14 2023 from 192.168.56.1
[oracle1@oracle ~]$ sqlplus insa_buha/oracle

...

Changing password for insa_buha
New password: oracle1234(보이지 않으니 잘 보고 작성할 것)
Retype new password: oracle1234
Password changed

 

# 권한 확인

SQL> SELECT * FROM session_privs;



SQL> SELECT * FROM user_users;


SQL> SELECT * FROM user_ts_quotas;



13. INSA SESSION 
SQL> SELECT * FROM user_sys_privs;

USERNAME                  PRIVILEGE                        ADM
------------------------------ ---------------------------------------- ---------
INSA                           CREATE TABLE(권한 부여)   YES



SQL> GRANT create table To insa_buha;



tip) 권한 부여한 날은 확인 없으니 스스로 날짜를 적어주자
SQL> SELECT * FROM user_sys_privs;

14. INSA_BUHA SESSION 
# INSA 에서 부여한 권한 확인
SQL> SELECT * FROM user_sys_privs;


# 테이블 생성
SQL> CREATE TABLE buha(id number, name varchar2(30), day date);


SQL> INSERT INTO buha(id, name, day) VALUES(1, 'SCOTT', SYSDATE);
SQL> COMMIT;
SQL> SELECT * FROM buha;

 


15. INSA SESSION
SQL> REVOKE create table FROM insa_buha;

 

16. INSA_BUHA SESSION
SQL> SELECT * FROM user_sys_privs;



17. SYS SESSION 
SQL> GRANT create sequence TO insa WITH ADMIN OPTION;
SQL> SELECT * FROM dba_sys_privs WHERE grantee = 'INSA';


18. INSA SESSION 
SQL> SELECT * FROM user_sys_privs;

USERNAME                       PRIVILEGE                               ADM
------------------------------ -------------------------------------------------- -----------
INSA                           CREATE SESSION                           NO
INSA                           CREATE SEQUENCE(자동 일련번호) YES
INSA                           CREATE TABLE                              YES


# seq 생성
SQL> CREATE SEQUENCE id_seq
  2  START WITH 1
  3  MAXVALUE 10
  4  INCREMENT BY 1
  5  NOCYCLE
  6  NOCACHE;


# seq 생성 확인
SQL> SELECT * FROM user_sequences WHERE sequence_name = 'ID_SEQ';


SQL> SELECT * FROM tab;


SQL> SELECT * FROM insa_tba;


SQL> INSERT INTO insa_tba(id, name) VALUES(id_seq.nextval, 'sophia');
SQL> INSERT INTO insa_tba(id, name) VALUES(id_seq.nextval, 'liam');
SQL> INSERT INTO insa_tba(id, name) VALUES(id_seq.nextval, 'noah');
SQL> COMMIT;
SQL> SELECT * FROM insa_tba;

# insa_buha 에게 권한 부여

SQL> GRANT create sequence TO insa_buha;


19. INSA_BUHA SESSION 

# 부여받은 권한 확인

SQL> SELECT * FROM user_sys_privs;

# table 확인

SQL> SELECT * FROM buha;

# seq 생

SQL> CREATE SEQUENCE buha_seq START WITH 2;
SQL> SELECT * FROM user_sequences WHERE sequence_name = 'BUHA_SEQ';


SQL> INSERT INTO buha(id, name, day) VALUES (buha_seq.nextval, 'emma', sysdate);
SQL> INSERT INTO buha(id, name, day) VALUES (buha_seq.nextval, 'james', sysdate);
SQL> INSERT INTO buha(id, name, day) VALUES (buha_seq.nextval, 'elijah', sysdate);
SQL> COMMIT;
SQL> SELECT * FROM buha;


20. SYS SESSION
# 권한 확인
SQL> SELECT * FROM dba_sys_privs WHERE grantee = 'INSA';


SQL> SELECT * FROM dba_sys_privs WHERE grantee = 'INSA_BUHA';


# 권한 회수
SQL> REVOKE create sequence FROM insa;
SQL> SELECT * FROM dba_sys_privs WHERE grantee = 'INSA';


SQL> SELECT * FROM dba_sys_privs WHERE grantee = 'INSA_BUHA';



# insa_buha 권한 회수 및 확인
SQL> REVOKE create sequence FROM insa_buha;
SQL> SELECT * FROM dba_sys_privs WHERE grantee = 'INSA_BUHA';



■ 객체권한
- 객체를 ACCESS 할 수 있는 권한
- 객체 권한 : SYS, 객체 소유가자 권한을 부여, 취소 할 수 있다.

TABLE VIEW SEQUENCE PROCEDURE
FUNCTION
PACKAGE
SELECT 
INSERT 
UPDATE 
DELETE 
ALTER
INDEX
REFERENCES(private key, foreign key) 		SELECT
INSERT
UPDATE
DELETE
간접 액세스할 때 		SELECT(딕셔너리)
ALTER(수정)

수정불가) START WITH절		 EXECUTE

 

# SYS SESSION
# WITH GEANT OPTION 옵션으로 받은 객체 권한 만큼은 다른 유저들한테 권한을 부여(grant)하고 취소(revoke)할 수 있다.
SQL> GRANT select ON hr.employees TO insa WITH GRANT OPTION;
SQL> GRANT select ON hr.departments TO insa WITH GRANT OPTION;
SQL> SELECT * FROM dba_tab_privs WHERE grantee = 'INSA';


21. INSA SESSION
SQL> SELECT * FROM user_tab_privs;


SQL> SELECT * FROM hr.employees;

SQL> SELECT * FROM hr.departments;


SQL> GRANT select ON hr.employees To insa_buha;
SQL> GRANT select ON hr.departments To insa_buha;


SQL> SELECT * FROM user_tab_privs;


22. INSA_BUAH SESSION

SQL> SELECT * FROM user_tab_privs;

SQL> SELECT * FROM hr.employees;
SQL> SELECT * FROM hr.departments;


23. INSA SESSION 
SQL> SELECT * FROM user_tab_privs;


SQL> REVOKE select ON hr.employees FROM insa_buha;
SQL> SELECT * FROM user_tab_privs;



24. INSA_BUHA SESSION
SQL> SELECT * FROM hr.employees;


SQL> SELECT * FROM user_tab_privs;



25. SYS SESSION 
SQL> SELECT * FROM dba_tab_privs WHERE grantee = 'INSA';
SQL> SELECT * FROM dba_tab_privs WHERE grantee = 'INSA_BUHA';


# WITH GRANT OPTION 을 사용하여 부여한 객체권한을 취소하면 연쇄적으로 취소를 수행한다.
SQL> REVOKE select ON hr.departments FROM insa;
SQL> SELECT * FROM dba_tab_privs WHERE grantee = 'INSA_BUHA';



■ ROLE 관리
- 정의 : 유저나 다른 롤에 부여된 관련되어 있는 권한의 이름으로 부여된 그룹, 관련성 있는 권한들의 그룹
- 롤이란 관련성이 있는 권한들을 하나로 묶어서 관리하는 객체
- 권한의 관리 대한 편리성

ex)
프로그래머들에게 부여할 시스템 권한
1. create procedure : 함수, 패키지, 프로시저
2. create trigger 
3. create view

# SYS SESSION
SQL> create role prog;


SQL> SELECT * FROM dba_roles;



# role에 시스템 권한을 부여
SQL> GRANT create view, create procedure, create trigger TO prog;
SQL> SELECT * FROM dba_sys_privs WHERE grantee = 'PROG';


# role에 객체 권한을 부여
SQL> GRANT select ON hr.departments To prog;
SQL> SELECT * FROM dba_tab_privs WHERE grantee = 'PROG';


# role을 유저한테 부여 
SQL> GRANT prog TO insa;


SQL> SELECT * FROM dba_role_privs WHERE grantee = 'INSA';



26. INSA SESSION 
SQL> SELECT * FROM session_roles;

SQL> conn insa/oracle
SQL> SELECT * FROM session_roles;

 

# 데이터 베이스에서 권한을 부여할 때 사용되는 옵션

WITH ADMIN OPTION WITH GRANT OPTION
특정 사용자에게 권한을 부여한 사용자가 그 권한을 다른 사용자에게도 부여할 수 있는 권한 권한을 부여받은 사용자가 다른 사용자에게도 동일한 권한을 부여할 수 있는 권한
A →(권한 부여)→B

A →(권한 부여 + with admin option )→ B
B →(B에게 부여받은 권한)→ C 		 A →(권한 부여)→B
A →(권한 부여 + with grant option)→ B
B →(B의 모든 권한)→ C 
728x90
반응형
LIST
저작자표시

'Data Base > Linux' 카테고리의 다른 글

231221 Linux_ Definer' right 와 Invoker's right  (1) 2023.12.21
231221 Linux_role 권한 관리, role 비활성화/활성화 설정  (0) 2023.12.21
231219 Linux_user 관리  (0) 2023.12.19
231219 Linux_block 관리  (0) 2023.12.19
231218 Linux_redo log file 이관작업  (1) 2023.12.18

'Data Base/Linux'의 다른글

  • 현재글231220 Linux_user, 객체, 권한, ROLE 관리

관련글

티스토리툴바