유저 액세스 제어

 

1.유저 생성(sys 계정으로 접속)

Create user demo identified by demo;

 

2.권한 부여

grant create session

    , create table

    , create sequence

    ,create view

to demo;

 

 

권한 부여를 할 때 매번 위와 같이 작성하는 것에 한계를 느낌

 

3.롤 생성

create role manager;

 

4.생성 롤에 권한 부여

grant create table, create view to manager;

 

5.테스트

create user alice identified by alice;

 

grant manager to alice;

 

6.암호 변경

Alter user demo identified by employ;

 

7.객체 권한(n1으로 접속)

grant select on employees to demo;

-- demo계정에게 자신의 employees테이블 select의 권한을 줌

 

grant update (department_name,location_id)

on  departments

to  demo,manager;

 -- demo계정과 manager(role)에게 department_idlocation_id update할 수 있는 권한 부여

 

8.권한 전달 : 시스템의 모든 유저가 권한을 받도록 설정(alice 계정)

Grant select on departments to pulbic;

 

9.sysgrant 권한 주기

grant select, insert

on   departments

to    demo

with grant option; -- 이 부분을 입력해주면 demo sys grant권한을 가지게 된다.

-- demo계정은 다음이 가능해 진다.

grant select on alice.departments to public;

-- alice departments select의 권한을 demo가 모두에게 뿌린다.

 

시퀀스


시퀀스 = 정수 값을 생성하는 데이터베이스 객체

 

시퀀스 특징

1)     고유 번호를 자동으로 생성 -> Primary key로 자주 사용

2)     공유할 수 있는 객체

3)     시퀀스 값이 메모리에서 캐시된 경우 엑세스 속도 향상

 

시퀀스 생성

Create sequence seq_emp

increment by 1 – 부를 때마다1씩 증가

start with 1 –시작 번호는 1

maxvalue 100 –최대 값 100

nocache nocycle;--캐시를 사용하지 않고, 사이클을 사용하지 않음

 

시퀀스 확인

Select * from user_seqeunce

 

시퀀스 적용

Insert into t_emp values(seq_emp.nextval);  --처음 실행하면 당연히 1이 들어감

Insert into t_emp values(seq_emp.nextval);  -- 2

Insert into t_emp values(seq_emp.nextval);  -- 3

 



◆뷰


= 자주 쓰이는 쿼리를 저장하고 쓴다.

 

1)     권한 받기(sys로 접속)

grant create view to n1;

 

2)     뷰 생성

create view empvu80

as select employee_id, last_name,salary

from employees

where department_id =80;

 

3)     뷰 사용

select * from empvu80;

 

4)     뷰 삭제

drop view empvu80;

 

5)     뷰 수정

CREATE OR REPLACE VIEW empvu80

(id_number, name, sal, department_id)

AS SELECT employee_id, first_name || ' '

|| last_name, salary, department_id

FROM employees

WHERE department_id = 80;

 

6)     복합 뷰

CREATE OR REPLACE VIEW dept_sum_vu

(name, minsal, maxsal, avgsal)

AS SELECT d.department_name, MIN(e.salary),

MAX(e.salary),AVG(e.salary)

FROM employees e JOIN departments d

ON (e.department_id = d.department_id)

GROUP BY d.department_name;

 

7)     동의어 생성 및 제거

create synonym d_sum for dept_sum_vu; -- 생성

 

select * from d_sum;  -- 확인

 

drop synonym d_sum; -- 동의어 제거

 

 

8)     뷰를 쓸 때 주의 사항

A.     단순 뷰는 DML을 수행할 수 있다.

-       with check option 절을 사용함으로 DML을 수행하지 못하도록 막을 수 있다.

CREATE OR REPLACE VIEW empvu20

AS SELECT *

FROM employees

WHERE department_id = 20

WITH CHECK OPTION CONSTRAINT empvu20_ck ;

 

B.      뷰에 그룹 함수, group by, distinct , rownum ,표현식으로 정의되는 열 등이 포함되어 있을 경우 뷰의 데이터를 수정,삭제,추가가 불가능

◆트랜잭션 :최소작업단위



트랜잭션의 시작 : 첫 번째 DML문 이후

트랜잭션의 종료 : 1) commit or rollback

       2) DDL문 실행

       3) DCL문 실행

       4) 툴 종료

       5) 시스템 문제가 생겼을 때




프로세스1

DML문(시작)--------

Update employees

set salary += salary+1000

where employee_id = 113;

-----------------------------------커밋x -> 확정 x

.

.

.

.

Commit(확정)!!

 


*확정 전까지는 하나의 트랜잭션



 

프로세스1

프로세스2

Update employees

set salary += salary+1000

where employee_id = 113;

 

 

 

 

 

 

 

 

 

Update employees

set salary += salary+1000

where employee_id = 50;

대기

데드락

 

 

 

Update employees

set salary += salary+1000

where employee_id = 50;

 

Update employees

set salary += salary+1000

where employee_id = 113;

대기

 

 

 

 

 

데드락

 *양방향에서 서로를 기다리게 되면서 무한대기에 빠진다.

 

-명시적 트랜잭션 제어문


COMMIT = 모든 데이터 변경 사항을 영구적으로 적용

SAVEPOINT = 현재 트랜잭션 안에 저장점을 만든다.

ROLLBACK = 커밋 전의 데이터 변경사항을 폐기

ROLLBACK TO SAVEPOINT = 지정한 저장점으로 롤백

 

--Lorentz : 4200

update tcltest set salary = salary * 1.5 where last_name = 'Lorentz';

 

select * from tcltest;

 

--Lorentz = 6300

 

savepoint s1;

 

update tcltest set salary = salary * 2 where last_name = 'Lorentz';

select * from tcltest;

 

--Lorentz : 12600

 

savepoint s2;

 

rollback to s1;

 

rollback;


'Programming > 데이터베이스' 카테고리의 다른 글

Oracle 문법 정리 - 시퀀스, 뷰  (0) 2018.01.09
Oracle 문법 정리 - DDL  (0) 2018.01.08
Oracle 문법 정리 - 트랜잭션  (0) 2018.01.05
Oracle 문법 정리 - DML  (0) 2018.01.04
Oracle 문법 정리 - 집합 연산자  (0) 2018.01.03
Oracle 문법 정리 - join  (0) 2018.01.02

◆Join

 데이터베이스 내의 여러 테이블 레코드를 조합하여 하나의 열로 표현한 .(위키백과)





Oracle Join

Ansi Join


1.     equal join

2.     outer join

3.     non equal join

4.     sef join

5.     threeways join


1.     inner join ~ on = join ~ on

2.     left outer join ~ on = left join ~ on

3.     right outer join ~on = right join ~ on

4.     full outer join ~ on = full join ~ on

5.     natural join

6.     join ~ using

 

Oracle Join

 

1) equal join

select ename,dname

from emp e, dept d

where e.deptno = d.deptno;

관계 (=)연산을 사용할 수 없을 떄 즉, join조건에 포함되지 못해(deptno = null) 나오지 못한 데이터가 있을 때

 

2)outer join

 

a) left outer join

select ename,nvl(dname,'대기발령')

from emp e, dept d

where e.deptno = d.deptno(+);


b) right outer join

select dname as deptName, ename

from dept d,emp e

where D.DEPTNO = E.DEPTNO(+)

order by deptName;


c) full outer join

오라클에서는 지원 x

 

3)non equal join

select ename

       ,sal

       ,s.grade

from emp e, salgrade s

where e.sal between s.losal and s.hisal;

 

4)self join

select e1.ename as mentee

      ,e2.ename as mento

from emp e1, emp e2

where e1.mgr = e2.empno(+)

order by mento;

 

 

Ansi Join

 

a)    inner join ~ on

select last_name

      ,department_name

from employees e join departments d

on e.department_id = d.department_id and e.last_name = initcap('&NAME')

order by department_name, last_name;

 

b)    left outer join ~ on, right outer join ~ on 

select last_name

      ,department_name

from employees e left outer join departments d

on e.department_id = d.department_id

order by department_name, last_name;

 

c)     full outer join ~ on

select department_name

      ,last_name as last_name

from employees e full outer join departments d

on e.department_id = d.department_id

order by department_name desc, last_name;

 

 

d)    natural join

select last_name,department_name

from employees natural join departments;

 

l  natural join 은 사용하지 않습니다.

테이블과 테이블의 공통된 컬럼을 알아서 합쳐주는 것 인데, 잘못된 결과가 나올 위험이 크다.

e)    join ~ using 

select last_name, department_name

from employees join departments

using (department_id);

 


p.s)마지막으로 카르테시안 조인(=크로스 조인)이 있습니다.


크로스 조인 = 모든 경우의 수를 나열하는 것.

select last_name,department_name

from employees cross join departments;

사원 수 (20) * 부서 수 (8) = 160개의 행이 리턴된다.

저번 글에서는 설치를 진행 했었습니다.


그렇다면 이번 글에서는 cmd에서 oracle을 다뤄보도록 합시다^^.


먼저 지난 번에 배웠었던 접속 방법으로 다들 접속을 해주세요



계정을 먼저 만들어보도록 할게요.


create user [아이디] identified by [비밀번호]; 




계정을 만들었다면 그 계정에 권한을 주게 할 차례입니다.


 grant connect,resource to [아이디];



이제 접속을 만든 계정으로 로그인을 해봅시다


conn [id]/[pw] 




다음은 아주 유명한 샘플인 scott.sql을 집어 넣어보도록 하겠습니다.  


C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin 경로에 들어가게 되면 scott.sql이라는 파일이 있습니다.


cmd에 @를 먼저 입력하신 후에 파일을 cmd창으로 밀어넣게 되면 다음과 같은 명령어가 생길것입니다. 


@C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\scott.sql



sql문을 잘 실행했다면 다음과 같이 어떤 테이블들이 있는지 확인해보도록 합시다

select * from tab; 




*저는 7개의 테이블들이 보이네요.


한번에 보기 쉽게 하기 위해 설정을 조금 해보겠습니다


한줄에 출력되는 문자의 갯수를 120으로 설정해보겠습니다.


set lines 120


그리고 슬래쉬 (/)를 입력하게 되면 방금전에 했던 select * from tab; 명령어가 다시 실행될 것입니다. 아마도 바로 전에 명령어를 다시 부르는 것인가 보네요.


*조금 더 깔끔하게 나오네요. ㅎ


하지만 TNAME이 쓸데 없이 너무 긴 바람에 이것도 보기가 흉하니 컬럼의 크기를 조금 조정하겠습니다.


  col tname for a35


테이블의 tname의 보여주는 최대의 갯수를 35로 설정하였습니다.






이것으로 cmd를 통해서 어느정도 접근을 해봤는데요!! 재미지죠?? 


그래도 cmd창으로만 하기에는 너무 불편하니 아무래도 GUI를 사용하는 것이 제일 편할 것 같습니다. 

다음 편에서부터는 GUI를 사용하여 배워보도록 하겠습니다. 


그럼 20000~

+ Recent posts