/**
  * @group pgsql8
  * @group mysql5
  * @group mssql10
  */
 public function testThrowWhenFalse()
 {
     dbsteward::$ignore_custom_roles = FALSE;
     try {
         xml_parser::role_enum($this->dbdoc, 'invalid');
     } catch (Exception $ex) {
         $this->assertEquals('Failed to confirm custom role: invalid', $ex->getMessage());
         return;
     }
     $this->fail("Expected exception when not ignoring custom roles");
 }
Example #2
0
 public static function get_creation_sql($db_doc, $node_schema, $node_view)
 {
     if (isset($node_view['description']) && strlen($node_view['description']) > 0) {
         $ddl = "-- {$node_view['description']}\n";
     }
     $view_name = mysql5::get_fully_qualified_table_name($node_schema['name'], $node_view['name']);
     $definer = strlen($node_view['owner']) > 0 ? xml_parser::role_enum($db_doc, $node_view['owner']) : 'CURRENT_USER';
     $ddl = "CREATE OR REPLACE DEFINER = {$definer} SQL SECURITY DEFINER VIEW {$view_name}\n";
     $ddl .= "  AS " . static::get_view_query($node_view) . ";";
     return $ddl;
 }
 /**
  * Creates and returns SQL for creation of trigger.
  *
  * @return created SQL
  */
 public static function get_creation_sql($node_language)
 {
     /*
         CREATE [ PROCEDURAL ] LANGUAGE name
         CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name
           HANDLER call_handler [ VALIDATOR valfunction ]
         /**/
     $ddl = "CREATE " . (strcasecmp(dbsteward::string_cast($node_language['trusted']), 'true') == 0 ? "TRUSTED " : "") . (strcasecmp(dbsteward::string_cast($node_language['procedural']), 'true') == 0 ? "PROCEDURAL " : "") . "LANGUAGE " . pgsql8::get_quoted_object_name($node_language['name']) . (strlen($node_language['handler']) > 0 ? "HANDLER " . pgsql8::get_quoted_function_name($node_language['handler']) : "") . (strlen($node_language['validator']) > 0 ? "VALIDATOR " . pgsql8::get_quoted_function_name($node_language['validator']) : "") . " ;\n";
     if (strlen($node_language['owner']) > 0) {
         $ddl .= "ALTER " . (strcasecmp(dbsteward::string_cast($node_language['procedural']), 'true') == 0 ? "PROCEDURAL " : "") . "LANGUAGE " . pgsql8::get_quoted_object_name($node_language['name']) . " OWNER TO " . xml_parser::role_enum(dbsteward::$new_database, $node_language['owner']) . " ;\n";
     }
     return $ddl;
 }
Example #4
0
 /**
  * Creates and returns SQL for creation of the view.
  *
  * @return string
  */
 public static function get_creation_sql($db_doc, $node_schema, $node_view)
 {
     // set replica set context for view
     if (pgsql8::set_context_replica_set_id($node_view) === -10) {
         // view doesn't specify one, set from for schema object
         pgsql8::set_context_replica_set_id($node_schema);
     }
     if (isset($node_view['description']) && strlen($node_view['description']) > 0) {
         $ddl = "-- " . dbsteward::string_cast($node_view['description']) . "\n";
     }
     $view_name = pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_table_name($node_view['name']);
     $ddl = "CREATE OR REPLACE VIEW " . $view_name;
     $ddl .= "\n\tAS " . pgsql8_view::get_view_query($node_view) . ";\n";
     if (isset($node_view['owner']) && strlen($node_view['owner']) > 0) {
         $ddl .= "ALTER VIEW " . $view_name . "\n\tOWNER TO " . xml_parser::role_enum($db_doc, $node_view['owner']) . ";\n";
     }
     return $ddl;
 }
Example #5
0
 /**
  * Creates and returns SQL for creation of the schema.
  *
  * @return created SQL
  */
 public static function get_creation_sql($node_schema)
 {
     if (strcasecmp('public', $node_schema['name']) == 0) {
         // don't create the public schema
         $ddl = '';
     } else {
         $schema_name = pgsql8::get_quoted_schema_name($node_schema['name']);
         $ddl = "CREATE SCHEMA " . $schema_name . ";\n";
         // schema ownership
         if (isset($node_schema['owner']) && strlen($node_schema['owner']) > 0) {
             // see dtd owner attribute enum: ROLE_OWNER, ROLE_APPLICATION, ROLE_SLONY
             // map ROLE_ enums to database->role->owner etc
             $ddl .= "ALTER SCHEMA " . $schema_name . " OWNER TO " . xml_parser::role_enum(dbsteward::$new_database, $node_schema['owner']) . ";\n";
         }
         // schema comment
         if (isset($node_schema['description']) && strlen($node_schema['description']) > 0) {
             $ddl .= "COMMENT ON SCHEMA " . $schema_name . " IS '" . pg_escape_string($node_schema['description']) . "';\n";
         }
     }
     return $ddl;
 }
 public static function get_sql($db_doc, $node_schema, $node_object, $node_permission)
 {
     $operations = mssql10_permission::get_permission_operations($node_permission);
     $roles = preg_split(dbsteward::PATTERN_SPLIT_ROLE, $node_permission['role'], -1, PREG_SPLIT_NO_EMPTY);
     $object_type = strtoupper($node_object->getName());
     $sql = '';
     switch ($object_type) {
         case 'SCHEMA':
             $object_name = mssql10::get_quoted_schema_name($node_schema['name']);
             for ($i = 0; $i < count($operations); $i++) {
                 // see http://msdn.microsoft.com/en-us/library/ms187940.aspx
                 if (strcasecmp($operations[$i], 'USAGE') == 0) {
                     // for schemas, translate USAGE into SELECT
                     $operations[$i] = 'SELECT';
                 }
                 if (strcasecmp($operations[$i], 'ALL') == 0) {
                     $operations[$i] = 'SELECT';
                     $operations[] = 'INSERT';
                     $operations[] = 'UPDATE';
                     $operations[] = 'DELETE';
                 }
                 // CREATE TABLE permission is database-wide
                 // so create it explicitly here in-line
                 // and then remove it from the list of operations to define
                 if (strcasecmp($operations[$i], 'CREATE TABLE') == 0) {
                     for ($j = 0; $j < count($roles); $j++) {
                         $sql .= "GRANT CREATE TABLE TO " . mssql10::get_quoted_object_name(xml_parser::role_enum($db_doc, $roles[$j])) . ";\n";
                     }
                     unset($operations[$i]);
                     $operations = array_merge($operations);
                     $i--;
                 }
             }
             break;
         case 'SEQUENCE':
             for ($i = 0; $i < count($operations); $i++) {
                 // for sequences, translate USAGE into INSERT
                 if (strcasecmp($operations[$i], 'USAGE') == 0) {
                     $operations[$i] = 'INSERT';
                 }
             }
             // give explicit DELETE permission for pseudo sequences, implemented as mssql10_bit_table
             if (!in_array('DELETE', $operations)) {
                 $operations[] = 'DELETE';
             }
         case 'TABLE':
         case 'VIEW':
         case 'FUNCTION':
             $object_name = mssql10::get_quoted_schema_name($node_schema['name']) . '.' . mssql10::get_quoted_table_name($node_object['name']);
             break;
         default:
             throw new exception("unknown object type encountered: " . $object_type);
     }
     for ($j = 0; $j < count($roles); $j++) {
         $with = '';
         if (isset($node_permission['with']) && strlen($node_permission['with']) > 0) {
             $with = "WITH " . $node_permission['with'] . " OPTION";
         }
         // treat sequences as tables, because that's how mssql10_bit_table created them
         if (strcasecmp($object_type, 'SEQUENCE') == 0) {
             $object_type = 'TABLE';
         }
         // translate pg to ms object type names that the GRANT ... CLASS :: specifier accepts
         $ms_object_type = $object_type;
         // in mssql a table is an object when doing grants
         if (strcasecmp($object_type, 'TABLE') == 0) {
             $ms_object_type = 'OBJECT';
         }
         // in mssql a view is an object when doing grants
         if (strcasecmp($object_type, 'VIEW') == 0) {
             $ms_object_type = 'OBJECT';
         }
         // in mssql a function and a procedure is an object when doing grants
         if (strcasecmp($object_type, 'FUNCTION') == 0) {
             $ms_object_type = 'OBJECT';
         }
         if (strlen($sql) > 0) {
             $sql .= "\n";
         }
         $sql .= self::compile_sql_statement(strtoupper($node_permission->getName()), implode(', ', $operations), $ms_object_type, $object_name, mssql10::get_quoted_object_name(xml_parser::role_enum($db_doc, $roles[$j])), $with);
     }
     return $sql;
 }
 /**
  * Creates and returns SQL command for creation of the sequence.
  *
  * @return created SQL command
  */
 public static function get_creation_sql($node_schema, $node_sequence)
 {
     if (isset($node_sequence['start']) && !is_numeric((string) $node_sequence['start'])) {
         throw new exception("start value is not numeric: " . $node_sequence['start']);
     }
     if (isset($node_sequence['inc']) && !is_numeric((string) $node_sequence['inc'])) {
         throw new exception("increment by value is not numeric: " . $node_sequence['inc']);
     }
     if (isset($node_sequence['max']) && !is_numeric((string) $node_sequence['max'])) {
         throw new exception("max value is not numeric: " . $node_sequence['max']);
     }
     if (isset($node_sequence['min']) && !is_numeric((string) $node_sequence['min'])) {
         throw new exception("min value is not numeric: " . $node_sequence['min']);
     }
     format::set_context_replica_set_id($node_sequence);
     $cache = 1;
     if (isset($node_sequence['cache'])) {
         if (!is_numeric((string) $node_sequence['cache'])) {
             throw new exception($node_schema['name'] . '.' . $node_sequence['name'] . " cache parameter for is not numeric: " . $node_sequence['cache']);
         }
         $cache = (int) $node_sequence['cache'];
     }
     if (isset($node_sequence['start'])) {
         $start = (int) $node_sequence['start'];
     } else {
         if (isset($node_sequence['min'])) {
             $start = (int) $node_sequence['min'];
         } else {
             $start = '1';
         }
     }
     if (isset($node_sequence['inc'])) {
         $inc = (int) $node_sequence['inc'];
     } else {
         $inc = '1';
     }
     $max_value = (int) $node_sequence['max'];
     if ($max_value > 0) {
         $max_value = 'MAXVALUE ' . $max_value;
     } else {
         $max_value = 'NO MAXVALUE';
     }
     $min_value = (int) $node_sequence['min'];
     if ($min_value > 0) {
         $min_value = 'MINVALUE ' . $min_value;
     } else {
         $min_value = 'NO MINVALUE';
     }
     $cycle = 'NO CYCLE';
     if (strlen($node_sequence['cycle']) > 0 && strcasecmp($node_sequence['cycle'], 'false') != 0) {
         $cycle = 'CYCLE';
     }
     $owned_by = '';
     if (strlen($node_sequence['ownedBy']) > 0) {
         $owned_by = " OWNED BY " . $node_sequence['ownedBy'];
     }
     $sequence_name = pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_object_name($node_sequence['name']);
     $ddl = "CREATE SEQUENCE " . $sequence_name . "\n\tSTART WITH " . $start . "\n\tINCREMENT BY " . $inc . "\n\t" . $max_value . "\n\t" . $min_value . "\n\tCACHE " . $cache . "\n\t" . $cycle . $owned_by . ";\n";
     // sequence ownership
     if (isset($node_sequence['owner'])) {
         $ddl .= "ALTER TABLE " . $sequence_name . " OWNER TO " . xml_parser::role_enum(dbsteward::$new_database, $node_sequence['owner']) . ";\n";
     }
     // sequence comment
     if (isset($schema['description']) && strlen($schema['description']) > 0) {
         $ddl .= "COMMENT ON SCHEMA " . $sequence_name . " IS '" . pg_escape_string($schema['description']) . "';\n";
     }
     return $ddl;
 }
 public static function equals($node_schema_a, $node_function_a, $node_function_b, $ignore_function_whitespace)
 {
     if (strcasecmp($node_function_a['name'], $node_function_b['name']) != 0) {
         return false;
     }
     $a_definition = pgsql8_function::get_definition($node_function_a);
     $b_definition = pgsql8_function::get_definition($node_function_b);
     if ($ignore_function_whitespace) {
         $a_definition = preg_replace("/\\s+/", " ", $a_definition);
         $b_definition = preg_replace("/\\s+/", " ", $b_definition);
     }
     $db_doc = $node_schema_a->xpath('..');
     $a_owner = xml_parser::role_enum($db_doc[0], $node_function_a['owner']);
     $b_owner = xml_parser::role_enum($db_doc[0], $node_function_b['owner']);
     $equals = strcasecmp(pgsql8_function::get_declaration($node_schema_a, $node_function_a), pgsql8_function::get_declaration($node_schema_a, $node_function_b)) == 0 && strcasecmp($a_definition, $b_definition) == 0 && strcasecmp($a_owner, $b_owner) == 0 && strcasecmp($node_function_a['type'], $node_function_b['type']) == 0;
     return $equals;
 }
 public static function get_permission_roles($db_doc, $node_permission)
 {
     return array_map(function ($o) use($db_doc) {
         return xml_parser::role_enum($db_doc, $o);
     }, preg_split(dbsteward::PATTERN_SPLIT_ROLE, $node_permission['role'], -1, PREG_SPLIT_NO_EMPTY));
 }
 public static function get_sql($db_doc, $node_schema, $node_object, $node_permission)
 {
     format::set_context_replica_set_id($node_object);
     $perms = pgsql8_permission::get_permission_operations($node_permission);
     $roles = preg_split(dbsteward::PATTERN_SPLIT_ROLE, $node_permission['role'], -1, PREG_SPLIT_NO_EMPTY);
     $object_type = strtoupper($node_object->getName());
     switch ($object_type) {
         case 'SCHEMA':
             $object_name = pgsql8::get_quoted_schema_name($node_schema['name']);
             break;
         case 'SEQUENCE':
         case 'TABLE':
         case 'VIEW':
             $object_name = pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_table_name($node_object['name']);
             break;
         case 'FUNCTION':
             $object_name = pgsql8_function::get_declaration($node_schema, $node_object);
             break;
         default:
             throw new exception("unknown object type encountered: " . $object_type);
     }
     $sql = '';
     for ($j = 0; $j < count($roles); $j++) {
         $with = '';
         if (isset($node_permission['with']) && strlen($node_permission['with']) > 0) {
             $with = "WITH " . $node_permission['with'] . " OPTION";
         }
         if (strcasecmp($object_type, 'VIEW') == 0) {
             // postgresql doesn't want you to name the view keyword when you grant rights to views
             $pg_object_type = '';
         } else {
             $pg_object_type = $object_type;
         }
         if (strlen($sql) > 0) {
             $sql .= "\n";
         }
         $sql .= self::compile_sql_statement(strtoupper($node_permission->getName()), implode(', ', $perms), $pg_object_type, $object_name, xml_parser::role_enum($db_doc, $roles[$j]), $with);
         // SCHEMA IMPLICIT GRANTS
         if (strcasecmp($object_type, 'SCHEMA') == 0) {
             // READYONLY USER PROVISION: grant usage on the schema for the readonly user
             if (strlen($db_doc->database->role->readonly) > 0) {
                 if (strlen($sql) > 0) {
                     $sql .= "\n";
                 }
                 $sql .= self::compile_sql_statement('GRANT', 'USAGE', 'SCHEMA', pgsql8::get_quoted_schema_name($node_schema['name']), $db_doc->database->role->readonly);
             }
         }
         // SEQUENCE IMPLICIT GRANTS
         if (strcasecmp($object_type, 'SEQUENCE') == 0) {
             // READYONLY USER PROVISION: generate a SELECT on the sequence for the readonly user
             if (strlen($db_doc->database->role->readonly) > 0) {
                 if (strlen($sql) > 0) {
                     $sql .= "\n";
                 }
                 $sql .= self::compile_sql_statement('GRANT', 'SELECT', 'SEQUENCE', pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_table_name($node_object['name']), $db_doc->database->role->readonly);
             }
         }
         // TABLE IMPLICIT GRANTS
         if (strcasecmp($object_type, 'TABLE') == 0) {
             // READYONLY USER PROVISION: grant select on the table for the readonly user
             if (strlen($db_doc->database->role->readonly) > 0) {
                 if (strlen($sql) > 0) {
                     $sql .= "\n";
                 }
                 $sql .= self::compile_sql_statement('GRANT', 'SELECT', 'TABLE', pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_table_name($node_object['name']), $db_doc->database->role->readonly);
             }
             // don't need to grant cascaded serial permissions to the table owner
             if (strcasecmp('ROLE_OWNER', $roles[$j]) == 0) {
                 continue;
             }
             // set serial columns permissions based on table permissions
             foreach ($node_object->column as $column) {
                 if (preg_match(pgsql8::PATTERN_TABLE_LINKED_TYPES, $column['type']) > 0) {
                     $col_sequence = pgsql8::identifier_name($node_schema['name'], $node_object['name'], $column['name'], '_seq');
                     $seq_priv = array();
                     // if you can SELECT, INSERT or UPDATE the table, you can SELECT on the sequence
                     if (in_array('SELECT', $perms) || in_array('INSERT', $perms) || in_array('UPDATE', $perms)) {
                         $seq_priv[] = 'SELECT';
                     }
                     // if you can INSERT or UPDATE the table, you can UPDATE the sequence
                     if (in_array('INSERT', $perms) || in_array('UPDATE', $perms)) {
                         $seq_priv[] = 'UPDATE';
                     }
                     // if you only have USAGE or SELECT
                     // then seq_priv is empty, and no grant should be issued
                     if (count($seq_priv) > 0) {
                         $with = '';
                         if (isset($node_permission['with']) && strlen($node_permission['with']) > 0) {
                             $with = "WITH " . $node_permission['with'] . " OPTION";
                         }
                         if (strlen($sql) > 0) {
                             $sql .= "\n";
                         }
                         $sql .= self::compile_sql_statement('GRANT', implode(',', $seq_priv), 'SEQUENCE', pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_table_name($col_sequence), xml_parser::role_enum($db_doc, $roles[$j]), $with);
                     }
                     // READYONLY USER PROVISION: grant implicit select on the sequence for the readonly user
                     if (strlen($db_doc->database->role->readonly) > 0) {
                         if (strlen($sql) > 0) {
                             $sql .= "\n";
                         }
                         $sql .= self::compile_sql_statement('GRANT', 'SELECT', 'SEQUENCE', pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_table_name($col_sequence), $db_doc->database->role->readonly);
                     }
                 }
             }
         }
     }
     return $sql;
 }
 public static function get_creation_sql($node_schema, $node_function)
 {
     $name = static::get_declaration($node_schema, $node_function);
     $definer = strlen($node_function['owner']) > 0 ? xml_parser::role_enum(dbsteward::$new_database, $node_function['owner']) : 'CURRENT_USER';
     // always drop the function first, just to be safe, and to be compatible with pgsql8's CREATE OR REPLACE
     $sql = static::get_drop_sql($node_schema, $node_function) . "\n";
     if (mysql5::$swap_function_delimiters) {
         $sql .= 'DELIMITER ' . static::ALT_DELIMITER . "\n";
     }
     $function_type = static::is_procedure($node_function) ? 'PROCEDURE' : 'FUNCTION';
     $sql .= "CREATE DEFINER = {$definer} {$function_type} {$name} (";
     if (isset($node_function->functionParameter)) {
         $params = array();
         foreach ($node_function->functionParameter as $param) {
             if (isset($param['direction']) && !static::is_procedure($node_function)) {
                 throw new exception("Parameter directions are not supported in MySQL functions");
             }
             if (empty($param['name'])) {
                 throw new exception("Function parameters must have names in MySQL. In function '{$node_function['name']}'");
             }
             $type = $param['type'];
             if ($node_type = mysql5_type::get_type_node(dbsteward::$new_database, $node_schema, $type)) {
                 $type = mysql5_type::get_enum_type_declaration($node_type);
             }
             $sparam = '';
             if (isset($param['direction'])) {
                 $sparam .= (string) $param['direction'] . ' ';
             }
             $sparam .= mysql5::get_quoted_function_parameter($param['name']) . ' ' . $type;
             $params[] = $sparam;
         }
         $sql .= implode(', ', $params);
     }
     $sql .= ")\n";
     // Procedures don't have a return statement
     if (!static::is_procedure($node_function)) {
         $returns = $node_function['returns'];
         if ($node_type = mysql5_type::get_type_node(dbsteward::$new_database, $node_schema, $returns)) {
             $returns = mysql5_type::get_enum_type_declaration($node_type);
         }
         $sql .= "RETURNS " . $returns . "\n";
     }
     $sql .= "LANGUAGE SQL\n";
     list($eval_type, $determinism) = static::get_characteristics((string) $node_function['cachePolicy'], (string) $node_function['mysqlEvalType']);
     $eval_type = str_replace('_', ' ', $eval_type);
     $sql .= "{$eval_type}\n{$determinism}\n";
     // unlike pgsql8, mysql5 defaults to SECURITY DEFINER, so we need to set it to INVOKER unless explicitly told to leave it DEFINER
     if (!isset($node_function['securityDefiner']) || strcasecmp($node_function['securityDefiner'], 'false') == 0) {
         $sql .= "SQL SECURITY INVOKER\n";
     } else {
         $sql .= "SQL SECURITY DEFINER\n";
     }
     if (!empty($node_function['description'])) {
         $sql .= "COMMENT " . mysql5::quote_string_value($node_function['description']) . "\n";
     }
     $sql .= trim(static::get_definition($node_function));
     $sql = rtrim($sql, ';');
     if (mysql5::$swap_function_delimiters) {
         $sql .= static::ALT_DELIMITER . "\nDELIMITER ;";
     } else {
         $sql .= ';';
     }
     return $sql;
 }