ALTER USER 유저명 ACCOUNT UNLOCK;
[출처] 오라클 계정 잠금 해제|작성자 임유경
- 계정 생성
create user 아이디
identified by 비밀번호
- DBA 롤 권한 주기
grant create session, dba
to 아이디
- 암호 변경
alter user 아이디
identified by 새로운암호
- 계정 잠그기
alter user 아이디 account [lock | unlock]
- 테이블 스페이스 확인ss
select tablespace_name, contents
from dba_tablespaces
order by tablespace_name
- 테이블 스페이스 변경
alter user 아이디
default tablespace users -- default 테이블 스페이스
temporary tablespace temp; -- 임시 테이블 스페이스
- 사용자 삭제
DROP USER 아이디
CASCADE 옵션 사용시 사용자의 모든 것이 같이 삭제
DROP USER 아이디 CASCADE
SYS 권한(privilege) 유포 확인 쿼리
1) DATABASE에 있는 SYS가 나눠준 모든 권한 보기
SQL> select * from dba_sys_privs;
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
HR UNLIMITED TABLESPACE NO
OE QUERY REWRITE NO
OE CREATE SNAPSHOT NO
;adm = with admin option으로 SYS가 HR에게 권한을 줄때
EX) GRANT greate user TO hr with admin option을 받은 hr은 그 받은 권한만을 다른 대상에게 부여할 수 있다.
2) 모든 계정이 table에 대한 권한을 알아보려면..
SELECT * FROM USER_TAB_PRIVS --( 내가 가진 객체 권한 )
SELECT * FROM USER_TAB_PRIVS_RECD --( 내가 받은 권한 )
SELECT * FROM USER_TAB_PRIVS_MADE --( 모든 테이블의 권한 확인 )
SELECT * FROM DBA_TAB_PRIVS --( 모든 테이블 관련 권한 )
▶ User의 생성 및 권한관리
- User
모든 Database객체는 특정 user의 소유로 되어 있으며 자신의 소유객체 외에 다른 소유의 객체는 권한이 있어야 접근이 가능
- Oracle은 user로 Database 사용을 control
- Database 생성시 자동으로 생성되는 User
① SYS
DBA Role을 가지고 있으며 Data Dictionary Table의 소유자
② SYSTEM
DBA Role을 가지고 있으며 Data Dictionary View의 소유자
⑴ User 생성 및 변경
- User 생성 구문
create user user명 identified by 패스워드명
SQL > show user -- 현재 접속된 사용자명
SQL > create user ocp identified by ocp;
- User 변경 구문
password 변경은 해당 user 혹은 dba Role안의 alter user 권한을 가지고 있는 user만 변경이 가능하다.
SQL> alter user tonkjsp identified by tonkjsp1;
SQL> conn tonkjsp/tonkjsp1
연결되었습니다.
⑵ 권한(Privilege)
- 특정 SQL문을 수행 할 수 있는 권리.
- Oracle의 모든 명령문은 그에 대한 권한이 있어야만 가능하다.
- 권한에는 database에서 객체를 정의할 수 있는 System권한과 정의된 객체에 대한 특정 작업을 부여 할 수 있는 객체 권한이 있다.
- System 권한에서 Any라는 옵션은 다른 계정 소유의 객체에게도 수행 할 수 있다.
Drop any Table table명
- 권한
- 권한 부여 및 회수
① system 권한 부여
grant
system 권한 [, system권한 ..., rol명... , role명 , .... ]
to
{user명 | role명 | public } , [{user명 | role명 | public } .... ]
[with admin option]
SQL> grant create session, create table to user;
SQL> grant create session to user1 with admin option
② 객체 권한 부여
grant
객체 권한 [, 객체권한 ..., ] || all
on 대상객체, 대상객체, ....
to
{user명 | role명 | public} [,{user명 | role명 | public } ... ]
[with grant option]
SQL> grant execute on dbms_pipe
SQL> to public ;
SQL> grant upate(ename, sal) on emp
SQL> to user1
SQL> with grant option;
③ system 권한 회수
REVOKE
system권한[, system권한.., role명, role명]
from
{user명 | role명 | public } [,{user명 | role명 | public } ...]
SQL> revoke create table from user1;
④ 객체 권한 회수
REVOKE
객체권한 [, 객체권한 ..,] | ALL
ON 대상객체, 대상객체, ....
FROM
{user명 | role명 | public } [,{user명 | role명 | public} ...]
SQL> revoke execute on dbms_pipe from winter;
※ public , all, with admin option , with grant option
① public
특정 user가 아닌 모든 user에게 권한 부여
② all
객체 권한에 명시하는 것으로 해당 객체의 모든 권한을 부여
③ with admin option
권한 상속
④ with grant option
권한 상속, cascade ,,,,,,
⑶ Role
- 권한의 모음 , 권한을 묶어서 수많은 권한들을 편리하게 제어할 수 있는 기능
- 수행하는 명령문이 기존의 권한 부여 및 취소하는 명령과 동일
- system권한 ,객체권한 모두, 같이 묶을 수 있다.
- role에 포함된 권한 설정을 변경하면 해당 role을 포함한 계정도 즉시 변경됨.
그럼으로 구너한 설정 변경에 대한 조치를 취할 필요가 없다.
- 소유되지 않으면 schema 객체가 아니다.
- dba 관리자가 생성하면 아무 계정이나 자신의 권한을 포함시킬 수 있다.
- create role권한이 있으면 다른 계정이라도 role을 생성할 수 있으나 자신의 객체가 되지 않고 공동으로 사용하게 됨.
SQL> conn system/manager
연결되었습니다.
SQL> create role arole;
롤이 생성되었습니다.
SQL> create role broke;
롤이 생성되었습니다.
SQL> grant drop any table, select any table to arole;
권한이 부여되었습니다.
SQL> conn tonkjsp/tonkjsp1
연결되었습니다.
SQL> grant select on staff to broke;
권한이 부여되었습니다.
=== User Managerment ===
- sys 사용자 : Database 내의 모든 권한을 갖구 있다.
- system 사용자 : DBA, pw : manager
- scott 사용자 : pw : tiger, 사용자별 공간, 리소스 제한, 패스워드 관리, 세션관리 등
- table, view, trigger등 database object는 사용자별로 생성, object 소유자는 해당 object를 생성한 사용자
* 사용자 생성 (system 계정에서)
create user oratest identified by oratest; //user:oratest pw:oratest
create oratest indetified by oratest
default tablespace appl_data // 해당 사용자 아이디가 사용할 테이블 등의 오브젝트들을 생성하고자 할때 할당되는 테이블스페이스
temporary tablespace temp; // 해당 사용자 아이디가 Sort, Group by 등의 작업을 수행하고자 할때 사용되는 테이블스페이스
create oratest indetified by oratest
default tablespace appl_data
temporary tablespace temp
quota 15M on appl_data
quota 10M on system;
* 변경
alter user oratest identified by studyhard; (암호바꾸기)
alter user oratest default tablespace users; (테이블스페이스 바꾸기)
alter user oratest
default tablespace users
quota 20M on users;
* 제거
drop user oratest;
drop user oratest cascade; (객체가 들어 있을때)
* user 보기 - 딕셔너리
select * from dba_users;
select * from user_users;
* 사용자 공간 할당 모니터링- 딕셔너리
select * from user_ts_quotas;
select * from dba_ts_quotas;
* 사용자 세션 모니터링 및 중단
select sid, serial#, username, program from v$session;
alter system kill session '13, 25060'; (sid, serial#)
col program format A20;
----------------------------------------------------------------------------------------
=== Password Managerment ===
- account locking : 횟수이상 틀리면 락 걸음.
- password aging and expiration : password 유효기간 설정.
- password history : 이전에 사용했던 password 다시 사용못함.
- password complexity verification : 복잡성 체크
* account locking
careate profile my_profile limit
failed_login_attempts 4 (4번 기회)
password_lock_time 30; (30일동안)
alter user scott profile my_profile; (적용)
alter user scott account unlock; (락걸렸을때 해제)
alter user scott accout lock; (걍 잠구기)
* password aging and expiration
create profile my_profile limit
failed_login_attempts 4 (4번 기회)
password_lock_time 30 (30일동안)failed
password_life_time 60 (유효 60일)
password_grace_time 3 (유예기간 3일)
alter user scott profile my_profile; (적용)
alter user scott password expire; (걍 잠구기)
* password history
create profile my_profile limit
password_reuse_time 60 (60일 동안 사용할수 없음)
password_reuse_max unlimited;
alter user scott profile my_profile; (적용)
create profile my_profile limit
password_reuse_max 3
password_reuse_max unlimited;
alter user scott profile my_profile; (적용)
* password complexity verification :스크립트(utlpwdmg.sql)사용
- 스크립트 위치 : /oracle\Ora81\RDBMS\ADMIN\utlpwdmg.sql
- sys 계정에서 해야한다.
* 최종
careate profile my_profile limit
failed_login_attempts 4 (4번 기회)
password_life_time 60 (패스워드 사용일수)
password_reuse_time 60 (60일 동안 사용할수 없음)
password_reuse_max unlimited
password_verify_function verify_function (암호검증_
password_lock_time 1/24 (로그인 시도시 실패 후 account lock걸려있는 기간)
password_grace_time 0; (첫번째 로그 성공후 password 소멸 전에 변경 할 수 있는 유예기간)
* profile 초기화
careate profile my_profile unlimited
failed_login_attempts unlimited (4번 기회)
password_life_time unlimited (패스워드 사용일수)
password_reuse_time unlimited (60일 동안 사용할수 없음)
password_reuse_max unlimited
password_verify_function null (암호검증_
password_lock_time unlimited (로그인 시도시 실패 후 account lock걸려있는 기간)
password_grace_time unlimited; (첫번째 로그 성공후 password 소멸 전에 변경 할 수 있는 유예기간)
* password 딕셔너리
select resource_name, limit from dba_profiles
where profile="my_profile' and resource_type = 'password';
----------------------------------------------------------------------------------------
=== 시스템 권한 ===
- create session : 궈한을 받은자(grantee) 데이타베이스 접속할 수 있게
- create table : 테이블, 인덱스 생성, (unlimited tablespace 같이 준다.)
- unlimited tablespace : 테이블스페이스에서 블록을 할당
- select any table : 어떤 schema로 된 snapshot이라도 검색할 수 있다.
* 권한 주기 - system 권한에서
create user acc_user identified by acc1030;
grant create session to acc_user; (세션주기)
grant create session to public; (전체 유저에게)
grant create table, unlimited tablespace to acc_user;(table 생성 권한주기)
* 권한 회수
revoke create session from acc_user; (권한 회수)
* 사용자가 다른사용자에게 권한주기 with admin option이 있어야 함
connect system/manager
create user a_user identified by a1030;
create user b_user identified by b1030;
grant create session to a_user with admin option;
connect a_user/a1030;
grant create session to b_user with admin option;
* 딕셔너리
select * from dba_sys_privs;
----------------------------------------------------------------------------------------
=== object privileges ===
* 권한 주기
connect scott/tiger
grant select on emp to acc_user; (select 권한 주기)
grant insert on emp to acc_user; (insert 권한 주기)
grant update (sal) n emp to acc_user; (sal 컬럼 update 권한 주기)
connect acc_user/acc1030;
select * from scott.emp;
* 권한 회수
revoke update on emp from acc_user;
* 사용자가 다른사용자에게 권한주기 with admin option이 있어야 함
- 시스템 권한은 계층적이다.
- revoke 할때 cascade 된다.
* 딕셔너리
user_tab_privs : 사용자가 소유자, 부여자 또는 권한을 받은 자인 경우의 오브젝트에 대한 권한.
user_tab_privs_made : 사용자가 소유하고 있는 오브젝트에 대한 모든 권한
user_tab_privs_recd : 사용자가 권한을 받은 자인 경우의 오브젝트에 대한 권한.
user_col_privs : 사용자가 소유자, 부여자 또는 권한을 받은 자인 경우 열에 대한 권한
user_col_privs_made : 사용자가 소유하고 있는 오브젝트의 열에 대한 모든 권한
user_col_privs_recd : 사용자가 권하을 받은자인 경우의 열에 대한 권한
----------------------------------------------------------------------------------------
=== Roles ===
- 권한들의 묶음
- System권한, Object 권한 모두 구성되어 질수 있다.
- Enable/Disable 될 수 있다.
- Role은 Password를 가질 수 있다.
* 생성
create role clerk_role not inentified; (암호 필요 X)
create role clerk_role indentified by xyz901; (암호 필요)
* rolls에게 권한부여
grant create session, create table to clerk_role;
* 사용자에게 부여
grant clerk_role to sctt, other_role;
* role 활성화/비활성화
set role clerk_role identified by xyz901; (clerk_role만 활성화, 나머진 비활성화)
set role all excpt acct_role; (acct_role 빼구 모든 role 활성화)
set role none; (모두 비활성화)
* 예제
create user pay_clack identified by pay_clerk;
create user manager identified by manager;
create user president identified by president;
grant create session to pay_clerk, manager, president; (접속 권한 주기)
grant create role to scott;(roll 생성 권한 주기)
conn scatt/tiger;
create role pay_clerk_role not identified;
create role manager_role not identified;
create role president_role not identified;
grant select on emp to pay_clerk_role, manager_role;
grant update(sal, comm) on emp to pay_clerk_role;
grant update(mgr, depno) on emp to manager_role;
grant pay_clerk_role, manager to president_role;
grant pay_clerk_role to pay_clerk;
grant manager_role to manager;
grant preident_role to presidet;
* 딕셔너리
role_sys_privs : role에 부여된 시스템 권한에 대한 정보
role_tab_privs : role에 부여된 테이블 권한에 대한 정보
role_role_privs : 다른 role에 부여된 role에 대한 정보
session_roles : 현재 사용자에게 활성화된 role에 대한 정보
user_role_privs : 사용자에게 부여된 role에 대한 정보
dba_sys_privs : 사용자 및 role에 부여된 시스템 권하에 대한 정보
dba_roles : 데이터베이스에 존재하는 모든 role에 대한 정보
