기본 콘텐츠로 건너뛰기

코드이그나이터 3.1.9 + 오라클 데이터베이스 세션

코드이그나이터 3.1.9 + 오라클 데이터베이스 세션

코드이그나이터 버전 : 3.1.9

오라클 : 12c

1. /application/config/config.php

$config['sess_driver'] = 'database';

$config['sess_save_path'] = 'CI_SESSIONS';

$config['sess_cookie_name'] = 'ci_session';

$config['sess_expiration'] = 3600 ;

$config['sess_expire_on_close'] = TRUE;

$config['sess_encrypt_cookie'] = TRUE;

$config['sess_use_database'] = TRUE;

$config['sess_table_name'] = 'CI_SESSIONS';

$config['sess_match_ip'] = TRUE;

$config['sess_match_useragent'] = TRUE;

$config['sess_time_to_update'] = 300 ;

$config['sess_regenerate_destroy'] = FALSE;

2. /application/config/database.php

);

'save_queries' => TRUE

'failover' => array(),

'stricton' => FALSE,

'compress' => FALSE,

'encrypt' => FALSE,

'swap_pre' => '',

'dbcollat' => 'utf8_general_ci',

'char_set' => 'utf8',

'cachedir' => '',

'cache_on' => FALSE,

'db_debug' => ( ENVIRONMENT !== 'production'),

'pconnect' => FALSE,

'dbprefix' => '',

'dbdriver' => 'oci8', //mysqli, oci8

'database' => '',

'password' => 'ci3',

'username' => 'ci3',

'hostname' => $tnsname,

'dsn' => '',

$db['default'] = array(

(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))';

$tnsname = '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.211.55.51)(PORT = 1521))

3. 테이블 만들기

) ;

, IP_ADDRESS VARCHAR2(256 BYTE) DEFAULT '0' NOT NULL

, USER_DATA VARCHAR2(1000 BYTE)

, USER_AGENT VARCHAR2(256 BYTE) NOT NULL

, TIMESTAMP NUMBER DEFAULT 0 NOT NULL

SESSION_ID VARCHAR2(50 BYTE) DEFAULT '0' NOT NULL

(

CREATE TABLE CI_SESSIONS

ALTER TABLE CI_SESSIONS ADD CONSTRAINT CI_SESSIONS_PK PRIMARY KEY (SESSION_ID);

CREATE UNIQUE INDEX CI_SESSIONS_PK ON CI_SESSIONS (SESSION_ID ASC)

4. /system/libraries/Session/drivers/Session_database_driver.php

변경(추가)된 부분은 빨간색 으로 표시함.

-필드명과, 대소문자 주의

*오라클 사용자에게 SYS.DBMS_LOCK 권한이 주어져 있어야 함.

그렇지 않으면 다음과 같은 에러 메시지가 출력됨.

권한이 부여되었습니다.

SQL> GRANT EXECUTE ON SYS.DBMS_LOCK TO CI3;

SQLPLUS로 SYS로 로그인.

CI3사용자에게 SYS.DBMS_LOCK 권한을 주는 방법 :

Message: oci_execute(): ORA-06550: 줄 3, 열4:PLS-00201: 'SYS.DBMS_LOCK' 식별자가 정의되어야 합니다 ORA-06550: 줄 3, 열4:PL/SQL: Statement ignored ORA-06550: 줄 4, 열15:PLS-00201: 'SYS.DBMS_LOCK' 식별자가 정의되어야 합니다 ORA-06550: 줄 4, 열4:PL/SQL: Statement ignored

/**

* CodeIgniter

*

* An open source application development framework for PHP

*

* This content is released under the MIT License (MIT)

*

* Copyright (c) 2014 - 2018, British Columbia Institute of Technology

*

* Permission is hereby granted, free of charge, to any person obtaining a copy

* of this software and associated documentation files (the "Software"), to deal

* in the Software without restriction, including without limitation the rights

* to use, copy, modify, merge, publish, distribute, sublicense, and/or sell

* copies of the Software, and to permit persons to whom the Software is

* furnished to do so, subject to the following conditions:

*

* The above copyright notice and this permission notice shall be included in

* all copies or substantial portions of the Software.

*

* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR

* IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,

* FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE

* AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER

* LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,

* OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN

* THE SOFTWARE.

*

* @package CodeIgniter

* @author EllisLab Dev Team

* @copyright Copyright (c) 2008 - 2014, EllisLab, Inc. (https://ellislab.com/)

* @copyright Copyright (c) 2014 - 2018, British Columbia Institute of Technology (http://bcit.ca/)

* @license http://opensource.org/licenses/MIT MIT License

* @link https://codeigniter.com

* @since Version 3.0.0

* @filesource

*/

defined('BASEPATH') OR exit('No direct script access allowed');

/**

* CodeIgniter Session Database Driver

*

* @package CodeIgniter

* @subpackage Libraries

* @category Sessions

* @author Andrey Andreev

* @link https://codeigniter.com/user_guide/libraries/sessions.html

*/

class CI_Session_database_driver extends CI_Session_driver implements SessionHandlerInterface {

/**

* DB object

*

* @var object

*/

protected $_db;

/**

* Row exists flag

*

* @var bool

*/

protected $_row_exists = FALSE;

/**

* Lock "driver" flag

*

* @var string

*/

protected $_platform;

// ------------------------------------------------------------------------

/**

* Class constructor

*

* @param array $params Configuration parameters

* @return void

*/

public function __construct(&$params)

{

parent::__construct($params);

$CI =& get_instance();

isset($CI->db) OR $CI->load->database();

$this->_db = $CI->db;

if ( ! $this->_db instanceof CI_DB_query_builder)

{

throw new Exception('Query Builder not enabled for the configured database. Aborting.');

}

elseif ($this->_db->pconnect)

{

throw new Exception('Configured database connection is persistent. Aborting.');

}

elseif ($this->_db->cache_on)

{

throw new Exception('Configured database connection has cache enabled. Aborting.');

}

$db_driver = $this->_db->dbdriver.(empty($this->_db->subdriver) ? '' : '_'.$this->_db->subdriver);

if (strpos($db_driver, 'mysql') !== FALSE)

{

$this->_platform = 'mysql';

}

elseif (in_array($db_driver, array('postgre', 'pdo_pgsql'), TRUE))

{

$this->_platform = 'postgre';

}

/* ellord */

elseif (strpos($db_driver,'oci8') !== FALSE)

{

$this->_platform = 'oracle';

}

// Note: BC work-around for the old 'sess_table_name' setting, should be removed in the future.

if ( ! isset($this->_config['save_path']) && ($this->_config['save_path'] = config_item('sess_table_name')))

{

log_message('debug', 'Session: "sess_save_path" is empty; using BC fallback to "sess_table_name".');

}

}

// ------------------------------------------------------------------------

/**

* Open

*

* Initializes the database connection

*

* @param string $save_path Table name

* @param string $name Session cookie name, unused

* @return bool

*/

public function open($save_path, $name)

{

if (empty($this->_db->conn_id) && ! $this->_db->db_connect())

{

return $this->_fail();

}

$this->php5_validate_id();

return $this->_success;

}

// ------------------------------------------------------------------------

/**

* Read

*

* Reads session data and acquires a lock

*

* @param string $session_id Session ID

* @return string Serialized session data

*/

public function read($session_id)

{

if ($this->_get_lock($session_id) !== FALSE)

{

// Prevent previous QB calls from messing with our queries

$this->_db->reset_query();

// Needed by write() to detect session_regenerate_id() calls

$this->_session_id = $session_id;

$this->_db

->select('USER_DATA')

->from($this->_config['save_path'])

->where('SESSION_ID', $session_id);

if ($this->_config['match_ip'])

{

$this->_db->where('IP_ADDRESS', $_SERVER['REMOTE_ADDR']);

}

if ( ! ($result = $this->_db->get()) OR ($result = $result->row()) === NULL)

{

// PHP7 will reuse the same SessionHandler object after

// ID regeneration, so we need to explicitly set this to

// FALSE instead of relying on the default ...

$this->_row_exists = FALSE;

$this->_fingerprint = md5('');

return '';

}

// PostgreSQL's variant of a BLOB datatype is Bytea, which is a

// PITA to work with, so we use base64-encoded data in a TEXT

// field instead.

if ($this->_platform === 'postgre')

{

$result=base64_decode(rtrim($result->data));

}

/* ellord */

elseif ($this->_platform === 'oracle')

{

//$result=base64_decode(rtrim($result->data->load()));

$result=base64_decode(rtrim($result->USER_DATA));

}

else {

$result=$result->data;

}

$this->_fingerprint = md5($result);

$this->_row_exists = TRUE;

return $result;

}

$this->_fingerprint = md5('');

return '';

}

// ------------------------------------------------------------------------

/**

* Write

*

* Writes (create / update) session data

*

* @param string $session_id Session ID

* @param string $session_data Serialized session data

* @return bool

*/

public function write($session_id, $session_data)

{

// Prevent previous QB calls from messing with our queries

$this->_db->reset_query();

// Was the ID regenerated?

if (isset($this->_session_id) && $session_id !== $this->_session_id)

{

if ( ! $this->_release_lock() OR ! $this->_get_lock($session_id))

{

return $this->_fail();

}

$this->_row_exists = FALSE;

$this->_session_id = $session_id;

}

elseif ($this->_lock === FALSE)

{

return $this->_fail();

}

if ($this->_row_exists === FALSE)

{

if ($this->_platform === 'oracle')

{

$dateTime = time() + 3600;

$sql = '

MERGE INTO "'.$this->_config['save_path'].'" A

USING DUAL

ON (A.SESSION_ID = :session_id)

WHEN MATCHED THEN

UPDATE SET

TIMESTAMP = :timestamp,

USER_AGENT = :user_agent,

USER_DATA = :user_data,

IP_ADDRESS = :ip_address

WHEN NOT MATCHED THEN

INSERT (SESSION_ID, TIMESTAMP, USER_AGENT, USER_DATA, IP_ADDRESS)

VALUES (:session_id, :timestamp, :user_agent, :user_data, :ip_address)

';

$enc_session_data = base64_encode($session_data);

$stmt = oci_parse($this->_db->conn_id, $sql);

oci_bind_by_name($stmt, ':session_id', $session_id, -1, SQLT_CHR);

oci_bind_by_name($stmt, ':ip_address', $_SERVER['REMOTE_ADDR'], -1, SQLT_CHR);

$time=time();

oci_bind_by_name($stmt, ':timestamp', $time, -1, SQLT_INT);

oci_bind_by_name($stmt, ':user_data', $enc_session_data, -1, SQLT_CHR);

oci_bind_by_name($stmt, ':user_agent', $_SERVER['HTTP_USER_AGENT'], -1, SQLT_CHR);

$bOk = oci_execute($stmt, OCI_DEFAULT);

if ($bOk)

{

oci_commit($this->_db->conn_id);

oci_free_statement($stmt);

$this->_fingerprint = md5($session_data);

$this->_row_exists = TRUE;

return $this->_success;

}

else

{

oci_rollback($this->_db->conn_id);

oci_free_statement($stmt);

return $this->_fail();

}

}else{

$insert_data = array(

'session_id' => $session_id,

'ip_address' => $_SERVER['REMOTE_ADDR'],

'timestamp' => time(),

'user_data' => ($this->_platform === 'postgre' ? base64_encode($session_data) : $session_data)

);

}

if ($this->_db->insert($this->_config['save_path'], $insert_data))

{

$this->_fingerprint = md5($session_data);

$this->_row_exists = TRUE;

return $this->_success;

}

return $this->_fail();

}

$this->_db->where('SESSION_ID', $session_id);

if ($this->_config['match_ip'])

{

$this->_db->where('IP_ADDRESS', $_SERVER['REMOTE_ADDR']);

}

$update_data = array('TIMESTAMP' => time());

if ($this->_fingerprint !== md5($session_data))

{

$update_data['USER_DATA'] = ($this->_platform === 'postgre' || $this->_platform === 'oracle')

? base64_encode($session_data)

: $session_data;

}

if ($this->_db->update($this->_config['save_path'], $update_data))

{

$this->_fingerprint = md5($session_data);

return $this->_success;

}

return $this->_fail();

}

// ------------------------------------------------------------------------

/**

* Close

*

* Releases locks

*

* @return bool

*/

public function close()

{

return ($this->_lock && ! $this->_release_lock())

? $this->_fail()

: $this->_success;

}

// ------------------------------------------------------------------------

/**

* Destroy

*

* Destroys the current session.

*

* @param string $session_id Session ID

* @return bool

*/

public function destroy($session_id)

{

if ($this->_lock)

{

// Prevent previous QB calls from messing with our queries

$this->_db->reset_query();

$this->_db->where('SESSION_ID', $session_id);

if ($this->_config['match_ip'])

{

$this->_db->where('IP_ADDRESS', $_SERVER['REMOTE_ADDR']);

}

if ( ! $this->_db->delete($this->_config['save_path']))

{

return $this->_fail();

}

}

if ($this->close() === $this->_success)

{

$this->_cookie_destroy();

return $this->_success;

}

return $this->_fail();

}

// ------------------------------------------------------------------------

/**

* Garbage Collector

*

* Deletes expired sessions

*

* @param int $maxlifetime Maximum lifetime of sessions

* @return bool

*/

public function gc($maxlifetime)

{

// Prevent previous QB calls from messing with our queries

$this->_db->reset_query();

return ($this->_db->delete($this->_config['save_path'], 'TIMESTAMP < '.(time() - $maxlifetime)))

? $this->_success

: $this->_fail();

}

// --------------------------------------------------------------------

/**

* Validate ID

*

* Checks whether a session ID record exists server-side,

* to enforce session.use_strict_mode.

*

* @param string $id

* @return bool

*/

public function validateId($id)

{

// Prevent previous QB calls from messing with our queries

$this->_db->reset_query();

$this->_db->select('1')->from($this->_config['save_path'])->where('SESSION_ID', $id);

empty($this->_config['match_ip']) OR $this->_db->where('IP_ADDRESS', $_SERVER['REMOTE_ADDR']);

$result = $this->_db->get();

empty($result) OR $result = $result->row();

return ! empty($result);

}

// ------------------------------------------------------------------------

/**

* Get lock

*

* Acquires a lock, depending on the underlying platform.

*

* @param string $session_id Session ID

* @return bool

*/

protected function _get_lock($session_id)

{

if ($this->_platform === 'mysql')

{

$arg = md5($session_id.($this->_config['match_ip'] ? '_'.$_SERVER['REMOTE_ADDR'] : ''));

if ($this->_db->query("SELECT GET_LOCK('".$arg."', 300) AS ci_session_lock")->row()->ci_session_lock)

{

$this->_lock = $arg;

return TRUE;

}

return FALSE;

}

elseif ($this->_platform === 'postgre')

{

$arg = "hashtext('".$session_id."')".($this->_config['match_ip'] ? ", hashtext('".$_SERVER['REMOTE_ADDR']."')" : '');

if ($this->_db->simple_query('SELECT pg_advisory_lock('.$arg.')'))

{

$this->_lock = $arg;

return TRUE;

}

return FALSE;

}

/* ellord */

elseif ($this->_platform === 'oracle')

{

$lockname=$session_id.($this->_config['match_ip'] ? '_'.$_SERVER['REMOTE_ADDR'] : '');

$lockid='';

$result=99;

$sql='

begin

SYS.DBMS_LOCK.ALLOCATE_UNIQUE (:lockname, :lockid);

:result := SYS.DBMS_LOCK.REQUEST (:lockid, DBMS_LOCK.X_MODE, 300, FALSE);

end;';

$stmt = oci_parse($this->_db->conn_id,$sql);

oci_bind_by_name($stmt,':lockname',$lockname,-1,SQLT_CHR);

oci_bind_by_name($stmt,':lockid',$lockid,256,SQLT_CHR);

oci_bind_by_name($stmt,':result',$result,2,SQLT_INT);

$bOk=oci_execute($stmt);

/*echo 'getlock ';

var_dump(array($result,$lockid)); */

if ($bOk && $result==0)

{

$this->_lock = $lockid;

return TRUE;

}

return FALSE;

}

return parent::_get_lock($session_id);

}

// ------------------------------------------------------------------------

/**

* Release lock

*

* Releases a previously acquired lock

*

* @return bool

*/

protected function _release_lock()

{

if ( ! $this->_lock)

{

return TRUE;

}

if ($this->_platform === 'mysql')

{

if ($this->_db->query("SELECT RELEASE_LOCK('".$this->_lock."') AS ci_session_lock")->row()->ci_session_lock)

{

$this->_lock = FALSE;

return TRUE;

}

return FALSE;

}

elseif ($this->_platform === 'postgre')

{

if ($this->_db->simple_query('SELECT pg_advisory_unlock('.$this->_lock.')'))

{

$this->_lock = FALSE;

return TRUE;

}

return FALSE;

}

/* ellord */

elseif ($this->_platform === 'oracle')

{

$sql='

begin

:result := SYS.DBMS_LOCK.RELEASE (:lockid);

end;';

$stmt = oci_parse($this->_db->conn_id,$sql);

$result=99;

oci_bind_by_name($stmt,':lockid',$this->_lock,256,SQLT_CHR);

oci_bind_by_name($stmt,':result',$result,2,SQLT_INT);

$bOk=oci_execute($stmt);

/*echo 'releaselock ';

var_dump(array($result,$this->_lock));*/

if ($bOk && $result==0)

{

$this->_lock = FALSE;

return TRUE;

}

return FALSE;

}

return parent::_release_lock();

}

}

from http://ellordnet.tistory.com/198 by ccl(A) rewrite - 2020-03-07 10:55:41

댓글

댓글 쓰기

이 블로그의 인기 게시물

PHP framework 종류

PHP framework 종류 분류 전체보기 (1461) API (0) Android (19) Common (11) Component (0) adb (2) DDMS (1) GCM (4) HTTP (0) sqlite (1) View (0) build (0) Bigdata (4) Common (1) Hadoop (2) Spark (0) SQL on Hadoop (1) Cloud (12) Common (8) Multitenancy (2) OpenStack (0) UCloud (2) Computer Science (24) Common (4) Asynchronous IO (1) Cache (6) Distributed (0) Message Queue (1) Parallel Computing (5) Software Engineering (7) DB (172) Common (32) HSQLDB (1) In-memory DB (0) MariaDB (2) MongoDB (30) MySQL (69) NoSQL (8) Oracle (17) ORM (6) Redis (1) SQL Server (6) Design Pattern (7) Common (7) Programming paradigms (0) Reactor (0) Development (459) Common (37) AngularJS (3) Bootstrap (3) C (22) C++ (0) CSS (32) HTML (31) HTML5 (2) Java (118) JavaEssential (25) JSP & Servlet (33) JavaScript (69) jQuery (26) jQuery Mobile (4) Linux Programming (3) python (3) PHP (21) Reactive Streams (0) ShellScript (5) UML (6) Windows Programming (2) XML (14) Framework & Platform (161) Common (8) EFL (1) mybati
댓글 쓰기
자세한 내용 보기

MariaDB 에서 access denied for user 'root' 문제

MariaDB 에서 access denied for user 'root' 문제 heidisql 등의 원격 접속 툴을 이용해도 접속이 안 됐다. 포트, 방화벽 설정 등등 모두 확인해 봤고 로컬에서 잘 돌아가는 데도 원격 접속이 안됐다. 사실 원격 접속만 안 되면 상관 없는데, codeigniter에서도 똑같이 로그인을 못해서 자꾸 에러가 났었다. 일단, MariaDB는 10.4부터 root 권한 소유자에게 따로 비밀번호를 물어보지 않는다. 즉, 루트 계정으로 로그인했으면 그냥 콘솔에 mysql 치면 접속이 된다. 물론 다른 유저는 전처럼 비밀번호를 물어본다. 나는 이 root 계정으로 로그인을 며칠째 계속 시도해 봤는데 계속 안됐다.... 포트 문제도 아니었고 딱히 통신 문제도 아니었다. 심지어 SSH 접속 계정도 root 였다. 해답은 새로운 계정을 파서, 그 계정으로 접속을 하니까 잘 됐다. 계정 만드는 SQL 문이야 검색하면 바로 나올 거니까 따로 적진 않겠다.사실 별 내용 없는 건 아는데 새 계정을 파서 시도하란 말을 며칠 내내 구글을 뒤진 다음에야 본 것 같아서, 혹시 같은 어려움을 겪는 사람들이 있다면 빨리 해결할 수 있었으면 좋겠다. from http://skyseven73.tistory.com/5 by ccl(A) rewrite - 2020-03-11 02:54:31
댓글 쓰기
자세한 내용 보기

[공학] 목차

[공학] 목차 자동차공학 컴퓨터공학 컴퓨터공학 | 개발환경 컴퓨터공학 | 웹개발 컴퓨터공학 | 모바일개발 ✓ 컴퓨터공학 | 운영체제 컴퓨터공학 | 프로그래밍 컴퓨터공학 | 네트워크 컴퓨터공학 | 데이터베이스 from http://sanctacrux.tistory.com/679 by ccl(A) rewrite - 2020-03-12 16:54:22
댓글 쓰기
자세한 내용 보기