MySQL view definer

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

MySQL view definer

dw1970
Have a simple flex app, say FlexApp to retrieve two tables and a view from
MySQL DB in webhosting service provider, say A and B. Currently, FlexApp
runs in service provider A with no problem at all. The FlexApp can be seen
in http://www.bbrchk.com/testdb/testdb.html Because we need to move to a new
service provider B, therefore we recreate the two tables and the view and
install the same FlexApp in service provider B. But, the result is
different. The data in the view cannot be retrieved. Can be seen in
http://www.bbrclubhk.com/testdb/testdb.html

Have used charles webproxy, but it only say
flex.messaging.messages.ErrorMessage for my StaffviewpositionService (this
is the php service for retrieving the view in MySQL)

The SQL to create the view in both service provider is the same which is

CREATE VIEW staffviewposition AS SELECT
  s.iddStaff,
  s.txtLastName,
  s.txtFirstName,
  p.iddPosition,
  p.txtPosition
FROM staff AS s, position AS p WHERE s.iddPosition = p.iddPosition;
When I go to the phpMyAdmin to edit the view, I found the definer is
different, service provider A (one that works) gives the definer as myname@%
while in service provider B (one that does NOT work) give myname@localhost.
Is this difference caused the problem?

Both of the StaffviewpositionService.php are basically the same except the
IP address to the service provider, of course.

Please advise how to resolve the view unable to retrieve data problem
resided in service provider B.





--
Sent from: http://apache-flex-users.2333346.n4.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: MySQL view definer

Olaf Krueger
> Have used charles webproxy, but it only say
flex.messaging.messages.ErrorMessage for my StaffviewpositionService

Did you check out the php error log at server side?
It could provide some helpful details.
(Of course, error logging has to be enabled)

Olaf



--
Sent from: http://apache-flex-users.2333346.n4.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: MySQL view definer

dw1970
Have checked out php error log at server side (error logging has been
enabled) but it did not generate any error. (I am sure the error logging has
been enabled because I intentionally edit the $databasename wrongly, I can
trigger the php error log stating DB access denied)

So, even I put back everything in order (see below php code for the view
service that is generated directly by flex builder 4.6), I still cannot
retrieve data from the view service. This is a simple testing app and I have
it also running from another web hosting service provider and runs perfectly
see
http://www.bbrchk.com/testdb/testdb.html

But, for this same app running in another web hosting service provider, I
got no view data retrieved, see
http://www.bbrclubhk.com/testdb/testdb.html

Please anybody help?

Regards,
David

<?php

/**
 *  README for sample service
 *
 *  This generated sample service contains functions that illustrate typical
service operations.
 *  Use these functions as a starting point for creating your own service
implementation. Modify the
 *  function signatures, references to the database, and implementation
according to your needs.
 *  Delete the functions that you do not use.
 *
 *  Save your changes and return to Flash Builder. In Flash Builder
Data/Services View, refresh
 *  the service. Then drag service operations onto user interface components
in Design View. For
 *  example, drag the getAllItems() operation onto a DataGrid.
 *  
 *  This code is for prototyping only.
 *  
 *  Authenticate the user prior to allowing them to call these methods. You
can find more
 *  information at http://www.adobe.com/go/flex_security
 *
 */
class StaffviewpositionService {

        var $username = "bbrclubh_dwu";
        var $password = "fakepassword";
        var $server = "103.109.101.18";
        var $port = "3306";
        var $databasename = "bbrclubh_bookclub";
        var $tablename = "staffviewposition";

        var $connection;

        /**
         * The constructor initializes the connection to database. Everytime a
request is
         * received by Zend AMF, an instance of the service class is created and
then the
         * requested method is invoked.
         */
        public function __construct() {
          $this->connection = mysqli_connect(
          $this->server,  
          $this->username,  
          $this->password,
          $this->databasename,
          $this->port
          );

                $this->throwExceptionOnError($this->connection);
        }

        /**
         * Returns all the rows from the table.
         *
         * Add authroization or any logical checks for secure access to your data
         *
         * @return array
         */
        public function getAllStaffviewposition() {

                $stmt = mysqli_prepare($this->connection, "SELECT * FROM
$this->tablename");
                $this->throwExceptionOnError();
               
                mysqli_stmt_execute($stmt);
                $this->throwExceptionOnError();
               
                $rows = array();
               
                mysqli_stmt_bind_result($stmt, $row->iddStaff, $row->txtLastName,
$row->txtFirstName, $row->iddPosition, $row->txtPosition);
               
            while (mysqli_stmt_fetch($stmt)) {
              $rows[] = $row;
              $row = new stdClass();
              mysqli_stmt_bind_result($stmt, $row->iddStaff, $row->txtLastName,
$row->txtFirstName, $row->iddPosition, $row->txtPosition);
            }
               
                mysqli_stmt_free_result($stmt);
            mysqli_close($this->connection);
       
            return $rows;
        }

        /**
         * Returns the item corresponding to the value specified for the primary
key.
         *
         * Add authorization or any logical checks for secure access to your data
         *
         *
         * @return stdClass
         */
        public function getStaffviewpositionByID($itemID) {
               
                $stmt = mysqli_prepare($this->connection, "SELECT * FROM $this->tablename
where iddStaff=?");
                $this->throwExceptionOnError();
               
                mysqli_stmt_bind_param($stmt, 'i', $itemID);
                $this->throwExceptionOnError();
               
                mysqli_stmt_execute($stmt);
                $this->throwExceptionOnError();
               
                mysqli_stmt_bind_result($stmt, $row->iddStaff, $row->txtLastName,
$row->txtFirstName, $row->iddPosition, $row->txtPosition);
               
                if(mysqli_stmt_fetch($stmt)) {
              return $row;
                } else {
              return null;
                }
        }

        /**
         * Returns the item corresponding to the value specified for the primary
key.
         *
         * Add authorization or any logical checks for secure access to your data
         *
         *
         * @return stdClass
         */
        public function createStaffviewposition($item) {

                $stmt = mysqli_prepare($this->connection, "INSERT INTO $this->tablename
(iddStaff, txtLastName, txtFirstName, iddPosition, txtPosition) VALUES (?,
?, ?, ?, ?)");
                $this->throwExceptionOnError();

                mysqli_stmt_bind_param($stmt, 'issis', $item->iddStaff,
$item->txtLastName, $item->txtFirstName, $item->iddPosition,
$item->txtPosition);
                $this->throwExceptionOnError();

                mysqli_stmt_execute($stmt);
                $this->throwExceptionOnError();

                $autoid = mysqli_stmt_insert_id($stmt);

                mysqli_stmt_free_result($stmt);
                mysqli_close($this->connection);

                return $autoid;
        }

        /**
         * Updates the passed item in the table.
         *
         * Add authorization or any logical checks for secure access to your data
         *
         * @param stdClass $item
         * @return void
         */
        public function updateStaffviewposition($item) {
       
                $stmt = mysqli_prepare($this->connection, "UPDATE $this->tablename SET
iddStaff=?, txtLastName=?, txtFirstName=?, iddPosition=?, txtPosition=?
WHERE iddStaff=?");
                $this->throwExceptionOnError();
               
                mysqli_stmt_bind_param($stmt, 'issisi', $item->iddStaff,
$item->txtLastName, $item->txtFirstName, $item->iddPosition,
$item->txtPosition, $item->iddStaff);
                $this->throwExceptionOnError();

                mysqli_stmt_execute($stmt);
                $this->throwExceptionOnError();
               
                mysqli_stmt_free_result($stmt);
                mysqli_close($this->connection);
        }

        /**
         * Deletes the item corresponding to the passed primary key value from
         * the table.
         *
         * Add authorization or any logical checks for secure access to your data
         *
         *
         * @return void
         */
        public function deleteStaffviewposition($itemID) {
                               
                $stmt = mysqli_prepare($this->connection, "DELETE FROM $this->tablename
WHERE iddStaff = ?");
                $this->throwExceptionOnError();
               
                mysqli_stmt_bind_param($stmt, 'i', $itemID);
                mysqli_stmt_execute($stmt);
                $this->throwExceptionOnError();
               
                mysqli_stmt_free_result($stmt);
                mysqli_close($this->connection);
        }


        /**
         * Returns the number of rows in the table.
         *
         * Add authorization or any logical checks for secure access to your data
         *
         *
         */
        public function count() {
                $stmt = mysqli_prepare($this->connection, "SELECT COUNT(*) AS COUNT FROM
$this->tablename");
                $this->throwExceptionOnError();

                mysqli_stmt_execute($stmt);
                $this->throwExceptionOnError();
               
                mysqli_stmt_bind_result($stmt, $rec_count);
                $this->throwExceptionOnError();
               
                mysqli_stmt_fetch($stmt);
                $this->throwExceptionOnError();
               
                mysqli_stmt_free_result($stmt);
                mysqli_close($this->connection);
               
                return $rec_count;
        }


        /**
         * Returns $numItems rows starting from the $startIndex row from the
         * table.
         *
         * Add authorization or any logical checks for secure access to your data
         *
         *
         *
         * @return array
         */
        public function getStaffviewposition_paged($startIndex, $numItems) {
               
                $stmt = mysqli_prepare($this->connection, "SELECT * FROM $this->tablename
LIMIT ?, ?");
                $this->throwExceptionOnError();
               
                mysqli_stmt_bind_param($stmt, 'ii', $startIndex, $numItems);
                mysqli_stmt_execute($stmt);
                $this->throwExceptionOnError();
               
                $rows = array();
               
                mysqli_stmt_bind_result($stmt, $row->iddStaff, $row->txtLastName,
$row->txtFirstName, $row->iddPosition, $row->txtPosition);
               
            while (mysqli_stmt_fetch($stmt)) {
              $rows[] = $row;
              $row = new stdClass();
              mysqli_stmt_bind_result($stmt, $row->iddStaff, $row->txtLastName,
$row->txtFirstName, $row->iddPosition, $row->txtPosition);
            }
               
                mysqli_stmt_free_result($stmt);
                mysqli_close($this->connection);
               
                return $rows;
        }
       
       
        /**
         * Utility function to throw an exception if an error occurs
         * while running a mysql command.
         */
        private function throwExceptionOnError($link = null) {
                if($link == null) {
                        $link = $this->connection;
                }
                if(mysqli_error($link)) {
                        $msg = mysqli_errno($link) . ": " . mysqli_error($link);
                        throw new Exception('MySQL Error - '. $msg);
                }
        }
}

?>




--
Sent from: http://apache-flex-users.2333346.n4.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: MySQL view definer

Olaf Krueger
Hi David,

are you're using the same PHP and MySQL versions on both servers?
In order to ensure that your MySQL view is basically working on the new
server you could test it by running your view statement e.g. by using
PHPMyAdmin.

BTW:
I just took at quick look at your generated PHP file, but I can't find any
view select statements.
I may overlook something...?

Maybe this helps a bit...
Olaf






--
Sent from: http://apache-flex-users.2333346.n4.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: MySQL view definer

Olaf Krueger
>but I can't find any view select statements.

Ahh.. forget about it,  I just saw that 'staffviewposition' is the view
itself.



--
Sent from: http://apache-flex-users.2333346.n4.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: MySQL view definer

Javier Guerrero García
In reply to this post by dw1970
If the definer is different and you've set the security to apply to the definer of the view (instead of the invoker of the view), of course the results might be different since the definer might not have enough permissions to access the tables in the view:



Either change the definer when creating the view, or apply security to the invoker, or make sure the new definer can access the tables involved.

(if you SQLdumped the database on server A to recreate it on server B, this is quite usual. Review the "DEFINER" part on the "create view" statements in the dump file)

On Mon, Jan 13, 2020 at 1:00 PM dw1970 <[hidden email]> wrote:
Have a simple flex app, say FlexApp to retrieve two tables and a view from
MySQL DB in webhosting service provider, say A and B. Currently, FlexApp
runs in service provider A with no problem at all. The FlexApp can be seen
in http://www.bbrchk.com/testdb/testdb.html Because we need to move to a new
service provider B, therefore we recreate the two tables and the view and
install the same FlexApp in service provider B. But, the result is
different. The data in the view cannot be retrieved. Can be seen in
http://www.bbrclubhk.com/testdb/testdb.html

Have used charles webproxy, but it only say
flex.messaging.messages.ErrorMessage for my StaffviewpositionService (this
is the php service for retrieving the view in MySQL)

The SQL to create the view in both service provider is the same which is

CREATE VIEW staffviewposition AS SELECT
  s.iddStaff,
  s.txtLastName,
  s.txtFirstName,
  p.iddPosition,
  p.txtPosition
FROM staff AS s, position AS p WHERE s.iddPosition = p.iddPosition;
When I go to the phpMyAdmin to edit the view, I found the definer is
different, service provider A (one that works) gives the definer as myname@%
while in service provider B (one that does NOT work) give myname@localhost.
Is this difference caused the problem?

Both of the StaffviewpositionService.php are basically the same except the
IP address to the service provider, of course.

Please advise how to resolve the view unable to retrieve data problem
resided in service provider B.





--
Sent from: http://apache-flex-users.2333346.n4.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: MySQL view definer

dw1970
In reply to this post by Olaf Krueger
hi,

The two Service Providers (SPA and SPB) are using different versions of PHP
and MySQL, the details is as follow. One point to note is that the flex app
is called testdb which will retrieve data from a DB called bookclub which
has 2 tables called staff and position and 2 views called staffviewposition
and staffviewpositionnoiddposition. In *SPA*, all data from 2 tables and 2
views can be retrieved successfully and displayed in 4 separate datagrids
(see http://www.bbrchk.com/testdb/testdb.html) while in *SPB* the data from
the 2 tables can be retrieved successfully while view data cannot (see
http://www.bbrclubhk.com/testdb/testdb.html). in SPB, it occurred to me the
version may not be a problem because table data retrieval is OK but of
course it might has problem with view, I don't know. This kind of flex app
(using view) is very common and wonder how other developer deal with this.

FYI, I have used PHPMyAdmin to test the MySQL view staffviewposition and
worked perfect, all data can be viewed.


/Service provider A/

*Database server*
Server: 10.100.5.140 via TCP/IP
Server type: MySQL
Server connection: SSL is not being used
Server version: 5.6.36 - MySQL Community Server (GPL)
Protocal version: 10
User: dwu@10.100.5.130
Server charset: cp1252 West European (latin 1)

*Web server*
nginx/1.11.10
Database client version: libmysql - mysqlnd 5.0.12
PHP extension: mysqli curl mbstring
PHP version: 7.1.14

*phpMyAdmin*
Version information: 4.9.0.1

*Zendframework*
Version: 1.11.2


/Service provider B/

*Database server*
Server: Localhost via UNIX socket
Server type: MariaDB
Server connection: SSL is not being used
Server version: 10.2.30-MariaDB - MariaDB Server
Protocol version: 10
User: bbrclubh@localhost
Server charset: cp1252 West European (latin1)

*Web server*
cpsrvd 11.84.0.19
Database client version: libmysql - 5.6.43
PHP extension: mysqli curl mbstring
PHP version: 7.3.6

*phpMyAdmin*
Version information: 4.9.0.1

*Zendframework*
Version: 1.11.2

*Database server*
Server: Localhost via UNIX socket
Server type: MariaDB
Server connection: SSL is not being used
Server version: 10.2.30-MariaDB - MariaDB Server
Protocol version: 10
User: bbrclubh@localhost
Server charset: cp1252 West European (latin1)

*Web server*
cpsrvd 11.84.0.19
Database client version: libmysql - 5.6.43
PHP extension: mysqli curl mbstring
PHP version: 7.3.6

*phpMyAdmin*
Version information: 4.9.0.1

*Zendframework*
Version: 1.11.2



--
Sent from: http://apache-flex-users.2333346.n4.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: MySQL view definer

Olaf Krueger
You should follow Javier's hints, especially the part "Review the "DEFINER"
part on the "create view" statements in the dump file".

Take a look at the "Definer" section at the docs:
https://mariadb.com/kb/en/create-view/

If that doesn't help, enable the DB query log, something like this:
https://mariadb.com/kb/en/general-query-log

Olaf





--
Sent from: http://apache-flex-users.2333346.n4.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: MySQL view definer

dw1970
Have followed Javier's hints to edit view of staffviewposition by applying
security to the invoker instead of the definer. But still unable to solve
the problem ie unable to fetch view data. see
http://www.bbrclubhk.com/testdb/testdb.html as compared to the one in
another web hosting service provider that works.
http://www.bbrchk.com/testdb/testdb.html

Tried to enable DB query log but the web hosting provider disallow me to do
so because the service provided is a shared web hosting server which
enabling DB query log is prohibited.

Still stuck with the problem of fetching view data. Any suggestions
welcomed.

Regards,
David





--
Sent from: http://apache-flex-users.2333346.n4.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: MySQL view definer

Javier Guerrero García
myname@% and myname@localhost are totally different users. The first means
"myname connecting from anywhere", while the second means "myname only when
it connects locally". Review permissions on the whole thing (tables, views,
user grants, etc...).

https://stackoverflow.com/questions/11634084/are-users-user-and-userlocalhost-not-the-same

On Thu, Jan 23, 2020 at 2:05 PM dw1970 <[hidden email]> wrote:

> Have followed Javier's hints to edit view of staffviewposition by applying
> security to the invoker instead of the definer. But still unable to solve
> the problem ie unable to fetch view data. see
> http://www.bbrclubhk.com/testdb/testdb.html as compared to the one in
> another web hosting service provider that works.
> http://www.bbrchk.com/testdb/testdb.html
>
> Tried to enable DB query log but the web hosting provider disallow me to do
> so because the service provided is a shared web hosting server which
> enabling DB query log is prohibited.
>
> Still stuck with the problem of fetching view data. Any suggestions
> welcomed.
>
> Regards,
> David
>
>
>
>
>
> --
> Sent from: http://apache-flex-users.2333346.n4.nabble.com/
>
Reply | Threaded
Open this post in threaded view
|

Re: MySQL view definer

Javier Guerrero García
https://dev.mysql.com/doc/refman/8.0/en/connection-access.html (rtfm ;-)

On Thu, Jan 23, 2020 at 2:12 PM Javier Guerrero García <[hidden email]>
wrote:

> myname@% and myname@localhost are totally different users. The first
> means "myname connecting from anywhere", while the second means "myname
> only when it connects locally". Review permissions on the whole thing
> (tables, views, user grants, etc...).
>
>
> https://stackoverflow.com/questions/11634084/are-users-user-and-userlocalhost-not-the-same
>
> On Thu, Jan 23, 2020 at 2:05 PM dw1970 <[hidden email]> wrote:
>
>> Have followed Javier's hints to edit view of staffviewposition by applying
>> security to the invoker instead of the definer. But still unable to solve
>> the problem ie unable to fetch view data. see
>> http://www.bbrclubhk.com/testdb/testdb.html as compared to the one in
>> another web hosting service provider that works.
>> http://www.bbrchk.com/testdb/testdb.html
>>
>> Tried to enable DB query log but the web hosting provider disallow me to
>> do
>> so because the service provided is a shared web hosting server which
>> enabling DB query log is prohibited.
>>
>> Still stuck with the problem of fetching view data. Any suggestions
>> welcomed.
>>
>> Regards,
>> David
>>
>>
>>
>>
>>
>> --
>> Sent from: http://apache-flex-users.2333346.n4.nabble.com/
>>
>
Reply | Threaded
Open this post in threaded view
|

Re: MySQL view definer

dw1970
To simulate the problem in an environment that I can read the DB query log, I
joined a different plan in the same web hosting service provider. This plan
is called Managed VPS (let's called it planB as opposed to the shared web
hosting let's call say it planA). Th planA is having the programA that
cannot fetch data from a view in MySQL DB. (see
http://www.bbrclubhk.com/testdb/testdb.html)

So, I installed the same programA to planB (let's call this programB) and I
enabled DB query log in planB. When I execute programB in planB, I expected
to see the same problem. But, to my surprise, all view data are retrieved.
(see http://www.hkcnau.com/testdb/testdb.html). I thought the problem is
solved if I switch to a more expensive environment - Managed VPS vs shared
web hosting. But still, I don't understand why it works.

Subsequently, I installed the real program (propgramC) to this planB
(Managed VPS) and expect to launch it for my users. But,  problem of the
same nature occurred that one of the table data (table data not view data)
is unable to be fetched. This is telling me that problem does not lie in
view data nor the security issue of the view.

So, I enable the DB query log. Below is the log. Please note that
2020-02-07T13:40:58.798502Z, there is no Prepare and Execute. This Connect
is where the php connect that table data. Obviously that table data SELECT
was skipped.

2020-02-07T13:40:58.384118Z  616 Connect [hidden email] on
dwu_bookclub using TCP/IP
2020-02-07T13:40:58.384298Z  616 Query SET NAMES utf8
2020-02-07T13:40:58.384502Z  616 Prepare SELECT DISTINCT intQuizYear,
txtQuizLevel, txtSchoolCode FROM score
2020-02-07T13:40:58.384582Z  616 Execute SELECT DISTINCT intQuizYear,
txtQuizLevel, txtSchoolCode FROM score
2020-02-07T13:40:58.385643Z  616 Quit
2020-02-07T13:40:58.446744Z  617 Connect [hidden email] on
dwu_bookclub using TCP/IP
2020-02-07T13:40:58.446953Z  617 Query SET NAMES utf8
2020-02-07T13:40:58.447160Z  617 Prepare SELECT * FROM score
2020-02-07T13:40:58.447245Z  617 Execute SELECT * FROM score
2020-02-07T13:40:58.449551Z  617 Quit
2020-02-07T13:40:58.798274Z  618 Connect [hidden email] on
dwu_bookclub using TCP/IP
2020-02-07T13:40:58.798502Z  618 Query SET NAMES utf8
2020-02-07T13:40:58.800083Z  618 Quit

To prove the real program (programC) has no bug, I installed this programC
into planA. And, to my surprise, the table data can all be retrieved but a
different view data is not fetched. Really puzzled me.

Am willing to supply the cPanel password to anyone who wished to go deeper
to find out the problem. Please private message me at [hidden email]
Stuck in this problem for quite and hope to solve it asap.

David



--
Sent from: http://apache-flex-users.2333346.n4.nabble.com/