오라클 사용자 관리

* 사용자 비밀번호를 변경하는 방법.

        ALTER USER <username> IDENTIFIED BY <new_password>;

오라클 8 부터는 sqlplus 를 통해, 유닉스처럼 비밀번호를 변경한다.

         SQL> password
         Changing password for SCOTT
         Old password:
         New password:
         Retype new password:

만약 다른 사용자의 비밀번호를 변경하고 싶다면, 'password 사용자이름' 으로 한다.



* 사용자를 삭제하고, 만드는 방법.
생성)
  CREATE USER scott
  IDENTIFIED BY tiger  -- 비밀번호
  DEFAULT TABLESACE  tools -- 테이블스페이스 설정
  TEMPORARY TABLESPACE temp; -- 임시테이블 스페이스 설정 ( 임시테이블스페이스가 정렬시사용되므로 정렬공간확보 )
삭제)
        DROP USER scott CASCADE;

생성후 권한 주기)
        GRANT CONNECT, RESOURCE TO scott;
        GRANT DBA TO scott;  

사용자의 테이블 스페이스 사용량 지정하기)
        ALTER USER scott QUOTA UNLIMITED ON tools;



* 사용자 관리방법

DBA 는 특정사용자를 Lock, Unlock, 비밀번호를 바꾸도록 할 수 있다.
오라클 9i 는 디비를 만들때 dbca(DB Configuration Assistant) 를 사용해서, SYS, SYSTEM 을 제외한 모든 계정을 Lock 할 수 있다.
사용하려면, 각 계정들을 Unlock 해줘야 한다.

         ALTER USER scott ACCOUNT LOCK       -- lock a user account
         ALTER USER scott ACCOUNT UNLOCK;    -- unlocks a locked users account
         ALTER USER scott PASSWORD EXPIRE;   -- 새로운 비밀번호로 바꾸도록 한다.



* 비밀번호 관리 방법.
오라클은 한자리 비밀번호나, 사용자 계정 그대로 비밀번호를 만드는 것을 허용한다. 별로 좋지 않다.

오라클 8 부터는 profile 을 통해 비밀번호를 관리한다.

CREATE PROFILE my_profile LIMIT
  PASSWORD_LIFE_TIME 30;
ALTER USER scott PROFILE my_profile;

설정 가능한 값들)
  FAILED_LOGIN_ATTEMPTS - 로그인 몇번 실패시 Lock 할 것인지?
  PASSWORD_LIFE_TIME    - 패스워드 유효일수
  PASSWORD_REUSE_TIME   - 패스워드 재사용일수 (number of days before a password can be reused)
  PASSWORD_REUSE_MAX    - 현재 패스워드를 재사용한다면, 변경할때까지의 날수.
                         (number of password changes required before the current password can be reused )
  PASSWORD_LOCK_TIME    - 로그인 실패로 인한 Lock 일수
  PASSWORD_GRACE_TIME   - 로그인은 허용되고 경고가 주어지는 기간.
  PASSWORD_VERIFY_FUNCTION - 패스워드 변경 함수.


* administrative (privileged) 사용자란.
SYSOPER 혹은 SYSDBA 의 권한을 가진 사용자를 의미한다. 이런 권한이 있으면 디비가 꺼져있어도 디비에 접근해서 작업을 할 수 있다.
이 권한을 관리하는 것은 디비가 아닌 패스워드 파일을 사용해서 하는데, 패스워드 파일은 orapwd 유틸리티를 사용해서 만들어낸다.



* administrative user 로 연결하는 방법.
administrative user는 유닉스에서는 DBA 그룹이고, 윈도우즈 NT 계열에서는 ORA_DBA 그룹이다.
         connect / as sysdba

         connect sys/password as sysdba



* 패스워드파일을 만드는 방법.
패스워드 파일은 $ORACLE_HOME/dbs/orapw or orapwSID 에 있다.
원격의 접속자를 위해 패스워드 파일을 생성한다.

1) 로그인 해서
2) 다음 명령실행. :
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=mypasswd
3) Shutdown the database (SQLPLUS> SHUTDOWN IMMEDIATE)
4) INIT.ORA file 을 열어 REMOTE_LOGIN_PASSWORDFILE=exclusive 라고 수정한다.
5) Startup the database (SQLPLUS> STARTUP)



* 패스워드파일에 사용자 추가하는 방법.
패스워드파일에 어떤 사용자가 있는지는 SYS.V_$PWFILE_USERS 에 쿼리해서 알아낸다.
sysdba 나 sysoper 권한을 주면 추가되고, 혹은 orapwd 유틸을 사용한다.
         GRANT SYSDBA TO scott;



* 다른 사용자로 바꾸고 싶다면.

        SQL> select password from dba_users where username='SCOTT';
        PASSWORD
         -----------------------------
         F894844C34402B67

        SQL> alter user scott identified by lion;
        User altered.

        SQL> connect scott/lion
        Connected.

        REM Do whatever you like...

        SQL> connect system/manager
        Connected.

        SQL> alter user scott identified by values 'F894844C34402B67';
        User altered.

        SQL> connect scott/tiger
        Connected.



* 원격 로그인시에 반드시 비밀번호를 입력하도록 한다.

REMOTE_OS_AUTHENT = FALSE 로 한다.



* 오라클 생성시 만들어지는 유저들... (이렇게나 많았나????)



SYS/CHANGE_ON_INSTALL or INTERNAL
Oracle Data Dictionary/ Catalog
생성 스크립트: ?/rdbms/admin/sql.bsq and various cat*.sql scripts
패스워드수정가능(디비만든 직후)
삭제불가



SYSTEM/MANAGER
The default DBA user name (please do not use SYS)
생성 스크립트: ?/rdbms/admin/sql.bsq
패스워드수정가능(디비만든 직후)
삭제불가


OUTLN/OUTLN
Stored outlines for optimizer plan stability
생성 스크립트: ?/rdbms/admin/sql.bsq
패스워드수정가능(디비만든 직후)
삭제불가


SCOTT/TIGER, ADAMS/WOOD, JONES/STEEL, CLARK/CLOTH and BLAKE/PAPER.
Training/ demonstration users containing the popular EMP and DEPT tables
생성 스크립트: ?/rdbms/admin/utlsampl.sql
패스워드수정가능(디비만든 직후)
삭제가능


HR/HR (Human Resources), OE/OE (Order Entry), SH/SH (Sales History).
Training/ demonstration users containing the popular EMPLOYEES and DEPARTMENTS tables
생성 스크립트: ?/demo/schema/mksample.sql
패스워드수정가능
삭제가능


CTXSYS/CTXSYS
Oracle interMedia (ConText Cartridge) administrator user
생성 스크립트: ?/ctx/admin/dr0csys.sql


TRACESVR/TRACE
Oracle Trace server
생성 스크립트: ?/rdbms/admin/otrcsvr.sql


DBSNMP/DBSNMP
Oracle Intelligent agent
생성 스크립트: ?/rdbms/admin/catsnmp.sql, called from catalog.sql
패스워드수정가능 - snmp_rw.ora 파일에 새로운 비밀번호를 넣는다
삭제가능 - Only if you do not use the Intelligent Agents


ORDPLUGINS/ORDPLUGINS
Object Relational Data (ORD) User used by Time Series, etc.
생성 스크립트: ?/ord/admin/ordinst.sql


ORDSYS/ORDSYS
Object Relational Data (ORD) User used by Time Series, etc
생성 스크립트: ?/ord/admin/ordinst.sql


DSSYS/DSSYS
Oracle Dynamic Services and Syndication Server
생성 스크립트: ?/ds/sql/dssys_init.sql


MDSYS/MDSYS
Oracle Spatial administrator user
생성 스크립트: ?/ord/admin/ordinst.sql


AURORA$ORB$UNAUTHENTICATED/INVALID
Used for users who do not authenticate in Aurora/ORB
생성 스크립트: ?/javavm/install/init_orb.sql called from ?/javavm/install/initjvm.sql


PERFSTAT/PERFSTAT
Oracle Statistics Package (STATSPACK) that supersedes UTLBSTAT/UTLESTAT
생성 스크립트: ?/rdbms/admin/statscre.sql


* 의심가는 테이블 감시하는 법.
DBMS_FGA 를 통해 의심가는 테이블에 대한 감시 정책을 생성한다.
DBA_AUDIT_POLICIES 에서 감시정책의 리스트를 볼 수 있고,
감시 기록은 DBA_FGA_AUDIT_TRAIL 에 남는다.
DBMS_FGA PL/SQL패키지는 테이블이나 뷰에 대한 정책을 적용하기 위한 패키지 안의 프로시져 즉 인터페이스이다.

-- Add policy on table with auditing condition...
execute dbms_fga.add_policy('HR', 'EMP', 'policy1', 'deptno > 10');
-- Must ANALYZE, this feature works with CBO (Cost Based Optimizer)
analyze table EMP compute statistics;

select * from EMP where c1 = 11;  -- Will trigger auditing
select * from EMP where c1 = 09;  -- No auditing

-- Now we can see the statments that triggered the auditing condition...
select sqltext from sys.fga_log$
delete from sys.fga_log$;