OCI.php 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270
  1. <?php
  2. /**
  3. * @copyright Copyright (c) 2016, ownCloud, Inc.
  4. *
  5. * @author Andreas Fischer <bantu@owncloud.com>
  6. * @author Bart Visscher <bartv@thisnet.nl>
  7. * @author Joas Schilling <coding@schilljs.com>
  8. * @author Jörn Friedrich Dreyer <jfd@butonic.de>
  9. * @author Manish Bisht <manish.bisht490@gmail.com>
  10. * @author Morris Jobke <hey@morrisjobke.de>
  11. * @author Roeland Jago Douma <roeland@famdouma.nl>
  12. * @author Thomas Müller <thomas.mueller@tmit.eu>
  13. * @author Thomas Pulzer <t.pulzer@kniel.de>
  14. * @author Victor Dubiniuk <dubiniuk@owncloud.com>
  15. *
  16. * @license AGPL-3.0
  17. *
  18. * This code is free software: you can redistribute it and/or modify
  19. * it under the terms of the GNU Affero General Public License, version 3,
  20. * as published by the Free Software Foundation.
  21. *
  22. * This program is distributed in the hope that it will be useful,
  23. * but WITHOUT ANY WARRANTY; without even the implied warranty of
  24. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  25. * GNU Affero General Public License for more details.
  26. *
  27. * You should have received a copy of the GNU Affero General Public License, version 3,
  28. * along with this program. If not, see <http://www.gnu.org/licenses/>
  29. *
  30. */
  31. namespace OC\Setup;
  32. class OCI extends AbstractDatabase {
  33. public $dbprettyname = 'Oracle';
  34. protected $dbtablespace;
  35. public function initialize($config) {
  36. parent::initialize($config);
  37. if (array_key_exists('dbtablespace', $config)) {
  38. $this->dbtablespace = $config['dbtablespace'];
  39. } else {
  40. $this->dbtablespace = 'USERS';
  41. }
  42. // allow empty hostname for oracle
  43. $this->dbHost = $config['dbhost'];
  44. $this->config->setValues([
  45. 'dbhost' => $this->dbHost,
  46. 'dbtablespace' => $this->dbtablespace,
  47. ]);
  48. }
  49. public function validate($config) {
  50. $errors = array();
  51. if(empty($config['dbuser']) && empty($config['dbname'])) {
  52. $errors[] = $this->trans->t("%s enter the database username and name.", array($this->dbprettyname));
  53. } else if(empty($config['dbuser'])) {
  54. $errors[] = $this->trans->t("%s enter the database username.", array($this->dbprettyname));
  55. } else if(empty($config['dbname'])) {
  56. $errors[] = $this->trans->t("%s enter the database name.", array($this->dbprettyname));
  57. }
  58. return $errors;
  59. }
  60. public function setupDatabase($username) {
  61. $e_host = addslashes($this->dbHost);
  62. // casting to int to avoid malicious input
  63. $e_port = (int)$this->dbPort;
  64. $e_dbname = addslashes($this->dbName);
  65. //check if the database user has admin right
  66. if ($e_host == '') {
  67. $easy_connect_string = $e_dbname; // use dbname as easy connect name
  68. } else {
  69. $easy_connect_string = '//'.$e_host.(!empty($e_port) ? ":{$e_port}" : "").'/'.$e_dbname;
  70. }
  71. $this->logger->debug('connect string: ' . $easy_connect_string, ['app' => 'setup.oci']);
  72. $connection = @oci_connect($this->dbUser, $this->dbPassword, $easy_connect_string);
  73. if(!$connection) {
  74. $errorMessage = $this->getLastError();
  75. if ($errorMessage) {
  76. throw new \OC\DatabaseSetupException($this->trans->t('Oracle connection could not be established'),
  77. $errorMessage.' Check environment: ORACLE_HOME='.getenv('ORACLE_HOME')
  78. .' ORACLE_SID='.getenv('ORACLE_SID')
  79. .' LD_LIBRARY_PATH='.getenv('LD_LIBRARY_PATH')
  80. .' NLS_LANG='.getenv('NLS_LANG')
  81. .' tnsnames.ora is '.(is_readable(getenv('ORACLE_HOME').'/network/admin/tnsnames.ora')?'':'not ').'readable');
  82. }
  83. throw new \OC\DatabaseSetupException($this->trans->t('Oracle username and/or password not valid'),
  84. 'Check environment: ORACLE_HOME='.getenv('ORACLE_HOME')
  85. .' ORACLE_SID='.getenv('ORACLE_SID')
  86. .' LD_LIBRARY_PATH='.getenv('LD_LIBRARY_PATH')
  87. .' NLS_LANG='.getenv('NLS_LANG')
  88. .' tnsnames.ora is '.(is_readable(getenv('ORACLE_HOME').'/network/admin/tnsnames.ora')?'':'not ').'readable');
  89. }
  90. //check for roles creation rights in oracle
  91. $query='SELECT count(*) FROM user_role_privs, role_sys_privs'
  92. ." WHERE user_role_privs.granted_role = role_sys_privs.role AND privilege = 'CREATE ROLE'";
  93. $stmt = oci_parse($connection, $query);
  94. if (!$stmt) {
  95. $entry = $this->trans->t('DB Error: "%s"', array($this->getLastError($connection))) . '<br />';
  96. $entry .= $this->trans->t('Offending command was: "%s"', array($query)) . '<br />';
  97. $this->logger->warning($entry, ['app' => 'setup.oci']);
  98. }
  99. $result = oci_execute($stmt);
  100. if($result) {
  101. $row = oci_fetch_row($stmt);
  102. if ($row[0] > 0) {
  103. //use the admin login data for the new database user
  104. //add prefix to the oracle user name to prevent collisions
  105. $this->dbUser='oc_'.$username;
  106. //create a new password so we don't need to store the admin config in the config file
  107. $this->dbPassword = \OC::$server->getSecureRandom()->generate(30, \OCP\Security\ISecureRandom::CHAR_LOWER.\OCP\Security\ISecureRandom::CHAR_DIGITS);
  108. //oracle passwords are treated as identifiers:
  109. // must start with alphanumeric char
  110. // needs to be shortened to 30 bytes, as the two " needed to escape the identifier count towards the identifier length.
  111. $this->dbPassword=substr($this->dbPassword, 0, 30);
  112. $this->createDBUser($connection);
  113. }
  114. }
  115. $this->config->setValues([
  116. 'dbuser' => $this->dbUser,
  117. 'dbname' => $this->dbName,
  118. 'dbpassword' => $this->dbPassword,
  119. ]);
  120. //create the database not necessary, oracle implies user = schema
  121. //$this->createDatabase($this->dbname, $this->dbuser, $connection);
  122. //FIXME check tablespace exists: select * from user_tablespaces
  123. // the connection to dbname=oracle is not needed anymore
  124. oci_close($connection);
  125. // connect to the oracle database (schema=$this->dbuser) an check if the schema needs to be filled
  126. $this->dbUser = $this->config->getValue('dbuser');
  127. //$this->dbname = \OC_Config::getValue('dbname');
  128. $this->dbPassword = $this->config->getValue('dbpassword');
  129. $e_host = addslashes($this->dbHost);
  130. $e_dbname = addslashes($this->dbName);
  131. if ($e_host == '') {
  132. $easy_connect_string = $e_dbname; // use dbname as easy connect name
  133. } else {
  134. $easy_connect_string = '//'.$e_host.'/'.$e_dbname;
  135. }
  136. $connection = @oci_connect($this->dbUser, $this->dbPassword, $easy_connect_string);
  137. if(!$connection) {
  138. throw new \OC\DatabaseSetupException($this->trans->t('Oracle username and/or password not valid'),
  139. $this->trans->t('You need to enter either an existing account or the administrator.'));
  140. }
  141. $query = "SELECT count(*) FROM user_tables WHERE table_name = :un";
  142. $stmt = oci_parse($connection, $query);
  143. $un = $this->tablePrefix.'users';
  144. oci_bind_by_name($stmt, ':un', $un);
  145. if (!$stmt) {
  146. $entry = $this->trans->t('DB Error: "%s"', array($this->getLastError($connection))) . '<br />';
  147. $entry .= $this->trans->t('Offending command was: "%s"', array($query)) . '<br />';
  148. $this->logger->warning( $entry, ['app' => 'setup.oci']);
  149. }
  150. $result = oci_execute($stmt);
  151. if($result) {
  152. $row = oci_fetch_row($stmt);
  153. }
  154. if(!$result or $row[0]==0) {
  155. \OC_DB::createDbFromStructure($this->dbDefinitionFile);
  156. }
  157. }
  158. /**
  159. * @param resource $connection
  160. */
  161. private function createDBUser($connection) {
  162. $name = $this->dbUser;
  163. $password = $this->dbPassword;
  164. $query = "SELECT * FROM all_users WHERE USERNAME = :un";
  165. $stmt = oci_parse($connection, $query);
  166. if (!$stmt) {
  167. $entry = $this->trans->t('DB Error: "%s"', array($this->getLastError($connection))) . '<br />';
  168. $entry .= $this->trans->t('Offending command was: "%s"', array($query)) . '<br />';
  169. $this->logger->warning($entry, ['app' => 'setup.oci']);
  170. }
  171. oci_bind_by_name($stmt, ':un', $name);
  172. $result = oci_execute($stmt);
  173. if(!$result) {
  174. $entry = $this->trans->t('DB Error: "%s"', array($this->getLastError($connection))) . '<br />';
  175. $entry .= $this->trans->t('Offending command was: "%s"', array($query)) . '<br />';
  176. $this->logger->warning($entry, ['app' => 'setup.oci']);
  177. }
  178. if(! oci_fetch_row($stmt)) {
  179. //user does not exists let's create it :)
  180. //password must start with alphabetic character in oracle
  181. $query = 'CREATE USER '.$name.' IDENTIFIED BY "'.$password.'" DEFAULT TABLESPACE '.$this->dbtablespace;
  182. $stmt = oci_parse($connection, $query);
  183. if (!$stmt) {
  184. $entry = $this->trans->t('DB Error: "%s"', array($this->getLastError($connection))) . '<br />';
  185. $entry .= $this->trans->t('Offending command was: "%s"', array($query)) . '<br />';
  186. $this->logger->warning($entry, ['app' => 'setup.oci']);
  187. }
  188. //oci_bind_by_name($stmt, ':un', $name);
  189. $result = oci_execute($stmt);
  190. if(!$result) {
  191. $entry = $this->trans->t('DB Error: "%s"', array($this->getLastError($connection))) . '<br />';
  192. $entry .= $this->trans->t('Offending command was: "%s", name: %s, password: %s',
  193. array($query, $name, $password)) . '<br />';
  194. $this->logger->warning($entry, ['app' => 'setup.oci']);
  195. }
  196. } else { // change password of the existing role
  197. $query = "ALTER USER :un IDENTIFIED BY :pw";
  198. $stmt = oci_parse($connection, $query);
  199. if (!$stmt) {
  200. $entry = $this->trans->t('DB Error: "%s"', array($this->getLastError($connection))) . '<br />';
  201. $entry .= $this->trans->t('Offending command was: "%s"', array($query)) . '<br />';
  202. $this->logger->warning($entry, ['app' => 'setup.oci']);
  203. }
  204. oci_bind_by_name($stmt, ':un', $name);
  205. oci_bind_by_name($stmt, ':pw', $password);
  206. $result = oci_execute($stmt);
  207. if(!$result) {
  208. $entry = $this->trans->t('DB Error: "%s"', array($this->getLastError($connection))) . '<br />';
  209. $entry .= $this->trans->t('Offending command was: "%s"', array($query)) . '<br />';
  210. $this->logger->warning($entry, ['app' => 'setup.oci']);
  211. }
  212. }
  213. // grant necessary roles
  214. $query = 'GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE TRIGGER, UNLIMITED TABLESPACE TO '.$name;
  215. $stmt = oci_parse($connection, $query);
  216. if (!$stmt) {
  217. $entry = $this->trans->t('DB Error: "%s"', array($this->getLastError($connection))) . '<br />';
  218. $entry .= $this->trans->t('Offending command was: "%s"', array($query)) . '<br />';
  219. $this->logger->warning($entry, ['app' => 'setup.oci']);
  220. }
  221. $result = oci_execute($stmt);
  222. if(!$result) {
  223. $entry = $this->trans->t('DB Error: "%s"', array($this->getLastError($connection))) . '<br />';
  224. $entry .= $this->trans->t('Offending command was: "%s", name: %s, password: %s',
  225. array($query, $name, $password)) . '<br />';
  226. $this->logger->warning($entry, ['app' => 'setup.oci']);
  227. }
  228. }
  229. /**
  230. * @param resource $connection
  231. * @return string
  232. */
  233. protected function getLastError($connection = null) {
  234. if ($connection) {
  235. $error = oci_error($connection);
  236. } else {
  237. $error = oci_error();
  238. }
  239. foreach (array('message', 'code') as $key) {
  240. if (isset($error[$key])) {
  241. return $error[$key];
  242. }
  243. }
  244. return '';
  245. }
  246. }