Oracle 계정 비번 모를 경우

: Oracle User 의 비번을 모를 경우

  ( Private DB Link, Job, Scheduler 등은 해당 유저로만 수정 가능함 

    >> 물론 이것도 우회 방법이 있다 ( 링크 ) 

            

     )

방법1) 비번 변경 > 접속 > 원복 순 
/*+ 이하 SYS USER 수행 */ 
[C:\]$ sqlplus "/as sysdba"
SQL*Plus: Release 12.2.0.1.0 Production on Wed Nov 28 14:02:28 2018
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> show user
USER is "SYS"
SQL> 
/*+ 이하 SCOTT USER 수행  */
[C:\]$ sqlplus scott/oracle123
SQL*Plus: Release 12.2.0.1.0 Production on Wed Nov 28 14:02:56 2018
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Last Successful login time: Wed Nov 28 2018 14:01:41 +09:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> show user
USER is "SCOTT"
/*+ 이하 SYS USER 수행 */ 
SQL> select * from v$version where rownum < 2 ;
BANNER
--------------------------------------------------------------------------------
    CON_ID
----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
     0
SQL> select password from sys.user$ where name ='SCOTT';
PASSWORD
--------------------------------------------------------------------------------
51D4FA704CC0D4EC
-- dba_usrs.password 칼럼은 더이상 지원되지 않는다. 
SQL> select password from dba_users where username ='SCOTT';
PASSWORD
--------------------------------------------------------------------------------
-- 임시 접속을 위해 비번 변경 
SQL> alter user scott identified by tiger;
User altered.
/*+ 이하 SCOTT USER 수행  */
-- 변경된 비번으로 접속 가능 
SQL> conn scott/tiger
Connected.
/*+ 이하 SYS USER 수행 */ 
-- 비번 원복
SQL> alter user scott identified by values '51D4FA704CC0D4EC';
User altered.
/*+ 이하 SCOTT USER 수행  */
SQL> conn scott/oracle123
Connected.
SQL> show user
USER is "SCOTT"
SQL> 
 

방법2) Proxy 유저 설정 ( dba 권한 가지면 가능 )
/*+ 이하 SYS USER 수행,  t_user 생성 후, scott 유저에 대한 Proxy 유저 설정 */ 
SQL> create user t_user identified by t_user;
User created.
SQL> alter user scott grant connect through t_user;
User altered.
/*+ 이하 SCOTT USER 수행  */
-- 변경된 비번으로 접속 가능 
SQL> conn scott/tiger
Connected.
/*+ 이하 SYS USER 수행 */ 
-- 비번 원복
SQL> alter user scott identified by values '51D4FA704CC0D4EC';
User altered.
/*+ 이하 SCOTT USER 수행  */
SQL> conn scott/oracle123
Connected.
SQL> show user
USER is "SCOTT"
SQL> 
/*+ 이하 t_user 로 접속 및 수행 */ 
SQL> conn t_user[scott]/t_user
Connected.
SQL> show user
USER is "SCOTT"
SQL> select proxy, client from proxy_users ;
PROXY
--------------------------------------------------------------------------------
CLIENT
--------------------------------------------------------------------------------
T_USER
SCOTT
SQL> create table t_user_table ( a1 number );
Table created.
SQL> insert into t_user_table values ( 1 );
1 row created.
SQL> commit ;
Commit complete.
SQL> conn scott/oracle123
Connected.
SQL> select * from t_user_table ;
	A1
----------
	 1
SQL> select owner, table_name from dba_tables where table_name ='T_USER_TABLE';
OWNER
--------------------------------------------------------------------------------
TABLE_NAME
--------------------------------------------------------------------------------
SCOTT
T_USER_TABLE
/*+ 이하 SYS USER 수행, t_user 생성 후, scott 유저에 대한 Proxy 유저 설정 해제*/ 
SQL> alter user scott revoke connect through t_user ;
User altered.