//************************************************
-- 주요 자료사전(data dictionary)
------------------------------------------------------
-- SYS 계정
-- 사용자 목록 확인
SELECT username, password, created FROM dba_users;
SELECT username, password, created FROM dba_users WHERE username='사용자명';
SELECT * FROM all_users; -- 전체 사용자 확인
-- 시스템 권한(CREATE TABLE, CREATE VIEW 등)
SELECT * FROM dba_sys_privs; -- 부여된 모든 시스템 권한 조회
SELECT * FROM system_privilege_map; -- 전체 시스템 권한 목록
-- 롤 확인
SELECT * FROM DBA_ROLES;
-- 롤에 부여된 시스템 권한(CREATE TABLE, CREATE VIEW 등) 확인
SELECT * FROM role_sys_privs;
SELECT * FROM role_sys_privs WHERE role='CONNECT';
SELECT * FROM role_sys_privs WHERE role='RESOURCE';
-- 롤에 존재하는 롤 확인
SELECT * FROM role_role_privs;
-- 롤이 가지고 있는 객체 권한(INSERT, SELECT 등) 확인
SELECT * FROM role_tab_privs
-- 테이블스페이스 할당량을 확인
SELECT username, tablespace_name, bytes/1024, max_bytes/1024 FROM dba_ts_quotas;
------------------------------------------------------
-- 사용자 계정
-- 사용자의 시스템 권한(CREATE TABLE, CREATE VIEW 등) 확인
SELECT * FROM user_sys_privs;
-- 사용자에게 부여된 롤 확인
SELECT * FROM user_role_privs;
-- 가지고 있는 객체 권한 확인(INSERT, UPDATE, SELECT, DELETE 등)
SELECT * FROM user_tab_privs;
-- 다른 사용자로 부터 받은 객체 권한 확인(INSERT, UPDATE, SELECT, DELETE 등)
SELECT * FROM user_tab_privs_recd;
-- 테이블 목록 확인
SELECT * FROM tab;
-- 테이블 컬럼 확인
SELECT * FROM col WHERE tname='테이블명';
SELECT * FROM cols WHERE table_name='테이블명';
SELECT * FROM user_tab_columns WHERE table_name='테이블명';
SELECT * FROM all_tab_columns WHERE owner ='디비계정' and table_name = '테이블명'
-- 제약조건 확인
DESC user_constraints; -- user_constraints 구조 확인
SELECT * FROM user_constraints WHERE table_name='INSA'; -- 어떤 컬럼에 제약조건이 부여되었는지 확인 불가
P : 기본키, C : NOT NULL 등, U : UNIQUE, R : 참조키 등
SELECT constraint_name, table_name, constraint_type FROM user_constraints;
-- 현재 user가 가지고 있는 column에 할당된 제약조건에 대한 정보
SELECT * FROM user_cons_columns; -- 어떤 컬럼에 기본키가 부여되었는지 확인 가능
-- procedure, function 목록 확인
SELECT object_name FROM user_procedures;
-- 테이블의 프로시저, 함수, 패키지 등 상호 참조되는 관계 확인
SELECT * FROM user_dependencies;
-- 트리거 확인
SELECT trigger_name, trigger_type, table_name FROM user_triggers;
-- 프로시저, 함수등의 소스 확인
SELECT text FROM user_source;
-- 뷰 목록, 소스 확인
SELECT view_name, text FROM user_views;
-- 시퀀스 목록 확인
SELECT * FROM seq;
SELECT * FROM user_sequences;
//************************************************
-- 사용자 확인, 락 설정, 해제
-- SYS 계정
SQLPLUS sys/"암호" as sysdba
SELECT * FROM all_users; -- 전체 사용자 확인
-- soctt LOCK 설정
ALTER USER scott ACCOUNT LOCK;
-- SCOTT 계정
CONN scott/"암호"
-- 에러 : Lock이 설정되어 오라클 접속을 할수 없다.
-- SYS 계정
SQL>CONN sys/"암호" as sysdba
-- soctt LOCK 해제
SQL>ALTER USER scott ACCOUNT UNLOCK;
//************************************************
-- 사용자 관리 - 사용자 추가
-- SYS 계정
CREATE USER sky0 IDENTIFIED BY abc;
-- 로그온 권한이 없어서 CONN가 불가능
-- sky0 사용자에게 데이터베이스에 접속할 수 있는 권한 부여
GRANT CREATE SESSION TO sky0;
------------------------------------------------------
-- sky0 계정
-- 테이블 작성
CREATE TABLE TEST(id NUMBER);
-- 테이블 스페이스 사용 권한이 없으므로 테이블을 작성할 수 없다.
//************************************************
--사용자 삭제
-- SYS 계정
-- sky0 사용자가 CONN 되어 있는 상태에서는 삭제 불가
DROP USER sky0; -- 테이블등이 존재하면 오류
-- DROP USER sky0 CASCADE; -- 테이블등이 존재하면 객체도 같이 삭제
//************************************************
-- 테이블스페이스를 사용할수 있는 권한을 부여하여 sky0 사용자 추가
-- SYS 계정
CREATE USER sky0 IDENTIFIED BY abc
DEFAULT TABLESPACE USERS
QUOTA UNLIMITED ON USERS; -- 무한대
-- QUOTA 50M ON USERS;
GRANT CREATE SESSION TO sky0; -- DB 접속 권한
-- 테이블 스페이스의 권한을 부여 했지만 테이블을 만들수 있는 권한이 없으므로 테이블 생성 불가
//************************************************
-- SYS 계정
-- 롤 권한 확인
SELECT * FROM role_sys_privs WHERE role='CONNECT';
SELECT * FROM role_sys_privs WHERE role='RESOURCE';
-- 테이블 생성 권한 부여
GRANT CREATE TABLE TO sky0;
-- RESOURCE : 롤 -> 테이블스페이스, 테이블 생성, 자료형 생성등 몇가지 권한을 모아모은것
-- GRANT RESOURCE TO sky0;
-- 사용자에 CONN, 테이블스페이스, 테이블작성등의 권한을 한번에 부여
-- GRANT CONNECT, RESOURCE TO sky0;
DROP USER sky0; -- 테이블이 존재하면 삭제 불가능
-- DROP USER sky0 CASCADE; -- 테이블 등의 개체가 존재해도 삭제
------------------------------------------------------
-- SYS 계정
CREATE USER sky0 IDENTIFIED BY abc;
-- 사용자에게 테이블 스페이스 권한 부여
ALTER USER sky0
DEFAULT TABLESPACE USERS
QUOTA 50M ON USERS;
-- 테이블스페이스 할당량을 확인
SELECT * FROM dba_ts_quotas;
SELECT username, tablespace_name, bytes/1024,
max_bytes/1024 FROM dba_ts_quotas;
//************************************************
-- SYS 계정
-- 암호 관리
GRANT CREATE SESSION TO sky0; -- DB 접속 권한
-- SYS에 의해 암호 변경
ALTER USER sky IDENTIFIED BY king;
-- sky0 계정
-- sky0 자신이 암호변경
ALTER USER sky0 IDENTIFIED BY king1;
-- SYS 계정
-- sky 사용자의 암호를 만료 시킴
ALTER USER sky0 PASSWORD EXPIRE;
-- sky 사용자로 CONN시 오류출력후 새로운 암호 입력 요구
DROP USER sky0;
//************************************************
-- SYS 암호 변경
CMD>sqlplus / as sysdba
-- CMD>sqlplus /nolog : connect가 되지 않은 상태로 sqlplus 만 실행된 상태
SQL>ALTER USER sys IDENTIFIED BY 암호;
SQL>CONN sys/암호
//************************************************
-- 시스템 권한 : DB 작업, 연산을 수행하기 위해 필요한 권한
-- CREATE TABLE, CREATE USER, CREATE SESSION.....
-----------------------------------------------------
-- SYS 계정
SELECT * FROM dba_sys_privs; -- 부여된 모든 시스템 권한 조회
SELECT * FROM system_privilege_map; -- 전체 시스템 권한 목록
-----------------------------------------------------
-- 사용자가 시스템 권한 확인
-- 사용자 계정
SELECT * FROM user_sys_privs;
-----------------------------------------------------
-- SYS 계정
CREATE USER sky1 IDENTIFIED BY king1;
CREATE USER sky2 IDENTIFIED BY king2;
GRANT CREATE SESSION TO sky1; -- DB 접근 권한 부여
GRANT CREATE SESSION TO sky2;
-- sky1 계정
-- 부여된 시스템 권한 확인
SELECT * FROM USER_SYS_PRIVS;
-- SYS 계정
GRANT CREATE TABLE, CREATE VIEW TO sky1;
GRANT RESOURCE TO sky2;
-- sky1 계정
CREATE TABLE TEST(id NUMBER); -- 오류 : 테이블 스페이스 권한이 없어서
-- sky2
-- 사용자에게 부여된 롤 확인
SELECT * FROM user_role_privs;
CREATE TABLE TEST(id NUMBER);
-- RESOURCE 롤은 CREATE TABLE, 테이블스페이스등의 권한이 존재하므로
-- sky1 계정
GRANT CREATE VIEW TO sky2; -- ADMIN 권한을 가지고 있지 않으므로 다른 사용자에게 권한을 부여 하지 못함
-- SYS 계정
GRANT CREATE TABLE, CREATE VIEW TO sky1
WITH ADMIN OPTION; -- ADMIN 권한 부여
-- sky1 계정
GRANT CREATE VIEW TO sky2;
//************************************************
-- 권한 회수
-- SYS 계정
REVOKE CREATE TABLE, CREATE VIEW FROM sky1;
-- 사용자 제거
DROP USER sky1;
DROP USER sky2 CASCADE; -- 테이블등이 존재하면 오류가 발생하는데 CASCADE 옵션을 붙이면 테이블도 자동 삭제
//************************************************
-- 객체 권한(INSERT, UPDATE....... 등) 확인
-- 사용자 계정
-- 가지고 있는 객체 권한 확인
SELECT * FROM user_tab_privs;
-- 다른 사용자로 부터 받은 객체 권한 확인
SELECT * FROM user_tab_privs_recd;
-- SYS 계정
-- 모든 테이블 관련 권한 확인
SELECT * FROM dba_tab_privs;
-------------------------------------------
-- SYS : 객체 권한
CREATE USER sky1 IDENTIFIED BY king1;
CREATE USER sky2 IDENTIFIED BY king2;
GRANT CONNECT, RESOURCE, CREATE VIEW TO sky1;
GRANT CONNECT, RESOURCE TO sky2;
-- CONNECT, RESOURCE : 롤
-- sky1
CREATE TABLE TEST(id NUMBER, name VARCHAR2(20));
INSERT INTO TEST (id, name) VALUES(1, 'aaa');
INSERT INTO TEST (id, name) VALUES(2, 'bbb');
COMMIT;
GRANT SELECT ON test TO sky2;
-- sky1이 sky2한테 test 테이블의 SELECT 권한 부여
-- sky2
SELECT * FROM sky1.test;
SELECT * FROM user_tab_privs; -- 가지고 있는 객체 권한
SELECT * FROM user_tab_privs_recd; -- 받은 객체 권한 확인
INSERT INTO sky1.test (id, name) VALUES (3, 'ccc');
-- 오류 : INSERT 권한이 없으므로
-- sky1 계정
-- sky2 에게 sky1 테이블에 INSERT 권한 부여
GRANT INSERT(id, name) ON test TO sky2;
-- 또는 GRANT INSERT ON test TO sky2;
-- sky2 계정
INSERT INTO sky1.test (id, name) VALUES (3, 'ccc');
COMMIT;
SELECT * FROM sky1.test;
-- 권한 회수
-- sky1
REVOKE SELECT ON test FROM sky2;
-- sky2 계정
SELECT * FROM sky1.test;
-- 오류
-------------------------------------------
--SYS 계정
DROP USER sky1 CASCADE;
DROP USER sky2 CASCADE;
//************************************************
--ROLE : 관리의 효율성을 위해 서로 연관된 권한을 한데 묶은 그룹
------------------------------------------------------
-- SYS 계정
-- ROLE 확인
SELECT * FROM dba_roles;
------------------------------------------------------
-- ROLE 생성과정
-- 롤생성 -> 롤에 권한 부여 -> 롤을 사용자에 부여
------------------------------------------------------
-- Role 생성
CREATE ROLE std_role;
SELECT * FROM dba_roles;
------------------------------------------------------
-- 암호를 가진 롤 생성
CREATE ROLE std_role1 IDENTIFIED BY aaa;
SELECT * FROM dba_roles;
------------------------------------------------------
-- 롤에 시스템 권한 부여
GRANT RESOURCE, CONNECT, CREATE VIEW TO std_role;
-- std_role 에 RESOURCE, CONNECT 롤, CREATE VIEW 시스템 권한 부여
SELECT * FROM role_role_privs WHERE role='STD_ROLE';
-- 롤안의 롤 확인
SELECT * FROM role_sys_privs WHERE role='STD_ROLE';
-- 시스템 권한 확인
------------------------------------------------------
-- 롤에 객체 권한 부여
-- 사용자
GRANT SELECT, INSERT, UPDATE ON insa TO std_role;
--sys
SELECT * FROM ROLE_TAB_PRIVS WHERE role='STD_ROLE';
-- 객체 권한 확인
//************************************************
-- ROLE 변경
-- 롤의 암호 제거
ALTER ROLE std_role1 NOT IDENTIFIED;
SELECT * FROM dba_roles WHERE role='STD_ROLE1';
-- 사용자에게 ROLE 부여
CREATE USER sky1 IDENTIFIED BY king1;
-- 사용자에게 ROLE 적용
GRANT STD_ROLE TO sky1;
-- sky1 계정
SELECT * FROM scott.insa;
-- sky1이 권한 확인
SELECT * FROM user_role_privs; -- 롤 확인
SELECT * FROM user_tab_privs; -- 객체 권한 확인
SELECT * FROM user_sys_privs; -- 시스템 권한 확인
-- roll의 권한은 시스템 계정에서 확인
-- SELECT * FROM role_role_privs
-- SELECT * FROM role_sys_privs
-- SELECT * FROM role_tab_privs
//************************************************
-- 사용자의 ROLE 회수
-- SYS 계정
REVOKE std_role FROM sky1;
//************************************************
-- PUBLIC 롤 : 모든 사용자에게 자동으로 권한 부여
CREATE ROLE pub_role; -- 롤 생성
-- 롤에 권한 부여
GRANT RESOURCE, CREATE VIEW, CONNECT TO pub_role;
-- PUBLIC 롤
GRANT pub_role TO PUBLIC WITH ADMIN OPTION;
-- PUBLIC 롤 회수
REVOKE pub_role FROM PUBLIC;
-- REVOKE pub_role FROM sky1; -- 오류
-- sky1 CONN 오류
-- ROLE 삭제
DROP ROLE pub_role;
DROP ROLE std_role;
DROP ROLE std_role1;
'오라클' 카테고리의 다른 글
| 오라클 접속 방법 (0) | 2013.06.15 |
|---|---|
| EXERD 모델링 프로그램 (0) | 2013.06.10 |
| 동적 SQL (0) | 2013.06.08 |
| 데이터 링크 (0) | 2013.06.08 |
| 커서(Cursor) (0) | 2013.06.06 |