$rbac->reset() does not set up correct links with root due to auto increment settings

[bblue]

(I could have some issues with my database so I need someone else to confirm this)

After running $rbac->reset() I get the following in my mysqli database as default values:

rolepermissions.RoleID = 1,
rolepermissions.PermissionID = 1
permissions.ID = y
roles.ID = x
userroles.RoleID = x

The auto increment number for Permissions.ID and Roles.ID are not reset to 1, the link between the Permissions and Roles in the rolepermissions table is therefore broken. X and Y should be equal to 1.

Seems not to cause any issue in the userroles table as this link is kept.

I suppose an easy fix would be to also reset the auto increment value, or to drop the table and recreate it completely. The former could have unforseen impacts on custom implementations of your library.

Edit: I am using v2.0.0 release

[jburns131]

Hello, and I apologize for the delay in a response.

I have a few questions:

• What version of PHP-RBAC are you using?
• Where did you download the version of PHP-RBAC you are using, and which link if there were multiple locations (like on github)?
• How was the database create?
• What database system are you using, and which version?
• Just to verify, are you using the 'mysqli' adapter in your 'database.config'?
• What database type are you using for the table columns:
  • permissions.ID
  • roles.ID
  • userroles.RoleID
• How did you get the results of the above data you are reporting?
  • Through a direct database query?
  • Through a method call using an Rbac object?
  • Using a GUI similiar to phpmyadmin?

All ID, Lft and Rght columns should have the database type of 'int(11)'. I don't know how those columns could contain alphabetical characters if the columns are integers.

If those columns are of the type 'int(11)' then there is a possibility the method used to query the above results is returning 'x' or 'y' for some reason.

If you look at the file 'PhpRbac/database/mysql.sql' you can see what the proper structure of the database tables should be.

Warning, the following is my personal opinion only. I don't think it has anything to do with this issue, although it might be good to look into the mysqli API to see if there is reason it would return the values 'x' or 'y'.*

My recommendation is to use the 'pdo_mysql' adapter.

Here is a comparison of the three MySQL API's (PDO, MySQLi, MySQL): https://php.net/manual/en/mysqlinfo.api.choosing.php

As you can see the syntax is similar. The PDO interface is strictly OO. PDO supports all MySQL 5.1+ functionality. PDO better supports Multiple Statements, and PHP-RBAC uses Multiple Statements in a few complicated queries.

And it's my opinion that it is a little more future-proof and easier to adapt if you (or we) want to support multiple database systems. I also think PHP itself is investing in PDO as the future of database connection abstraction.

[bblue]

• I am using 2.0.0, downloaded from github
• https://github.com/OWASP/rbac/releases/tag/2.0.0
• I actually can't recall, it's a while since I set it up. I believe your script did it automatically.
• mySQL 5.5
• confirmed, using the mysqli adapter
• innoDB
• db query via mysql workbench

I thought it was clear, but I am not getting actual letters returned. I wrote X and Y to signify that the values are variable, depending on the last auto-increment integer of the table. i.e. if I add 10 permissions to the database, and then call $rbac->reset(), Y would be equal to 12 (i.e. the next auto increment value).

I have been working directly with the db tables a lot, and I really could have messed something up, but I only got this issue directly after upgrading to 2.0.0.

[abiusx]

It seems like a valid bug.
Can you create a unit test, or a code snippet to reproduce this?
BTW, if you mess with a single entry in db tables of nested set model, the entire thing goes out of consistency and becomes useless.
-A

---

Notice: This message is digitally signed, its source and integrity are verifiable.
If you mail client does not support S/MIME verification, it will display a file (smime.p7s), which includes the X.509 certificate and the signature body. Read more at Certified E-Mail with Comodo and Thunderbird in AbiusX.com

On Apr 16, 2014, at 1:55 AM, Aleksander notifications@github.com wrote:

I am using 2.0.0, downloaded from github
https://github.com/OWASP/rbac/releases/tag/2.0.0
I actually can't recall, it's a while since I set it up. I believe your script did it automatically.
mySQL 5.5
confirmed, using the mysqli adapter
innoDB
db query via mysql workbench
I thought it was clear, but I am not getting actual letters returned. I wrote X and Y to signify that the values are variable, depending on the last auto-increment integer of the table. i.e. if I add 10 permissions to the database, and then call $rbac->reset(), Y would be equal to 12 (i.e. the next auto increment value).

I have been working directly with the db tables a lot, and I really could have messed something up, but I had this issue directly after upgrading to 2.0.0.

—
Reply to this email directly or view it on GitHub.

[jburns131]

Sorry, I'm either a space shot, or very specific (read anal-retentive lol).

A unit test and/or code snippet, along with steps to reproduce would be great.

In the mean time I'll try to reproduce locally.

[bblue]

I most definetely have been fiddling directly with single db entries, so this could still very much be a user error. Even if so, I would think that the reset() function should be altered to indeed be a full reset, not just a "soft" reset.

I will try to cook something up to reproduce.

[abiusx]

Dropping tables and creating them again is not convenient, it is very slow, requires a lot of DBMS activity, and is the lazy way of truncating the data and resetting metadata.
-A

---

Notice: This message is digitally signed, its source and integrity are verifiable.
If you mail client does not support S/MIME verification, it will display a file (smime.p7s), which includes the X.509 certificate and the signature body. Read more at Certified E-Mail with Comodo and Thunderbird in AbiusX.com

On Apr 16, 2014, at 2:30 AM, Aleksander notifications@github.com wrote:

I most definetely have been fiddling directly with single db entries, so this could still very much be a user error. Even if so, I would think that the reset() function should be altered to indeed be a full reset, not just a "soft" reset.

I will try to cook something up to reproduce.

—
Reply to this email directly or view it on GitHub.

[bblue]

I found the error, and it's on my part. Apologies for taking up your time.

When reading through the source code I noticed the ALTER TABLE statement, and I realised that somewhere along the line I have removed permissions for my db user to do ALTER. This would of course not enable the auto_increment reset, and thus cause all my issues.

Issue can be closed. Again, sorry for the noise 👎

[abiusx]

Still its something we didn’t think of, and somebody might face it later. Lets keep this thread for that.
-A

---

Notice: This message is digitally signed, its source and integrity are verifiable.
If you mail client does not support S/MIME verification, it will display a file (smime.p7s), which includes the X.509 certificate and the signature body. Read more at Certified E-Mail with Comodo and Thunderbird in AbiusX.com

On Apr 16, 2014, at 2:48 AM, Aleksander notifications@github.com wrote:

I found the error, and it's on my part. Apologies for taking up your time.

When reading through the source code I noticed the ALTER TABLE statement, and I realised that somewhere along the line I have removed permissions for my db user to do ALTER. This would of course not enable the auto_increment reset, and thus cause all my issues.

Issue can be closed. Again, sorry for the noise

—
Reply to this email directly or view it on GitHub.

[jburns131]

No worries on that. It's usually the misplaced comma that messes us up, because we're too busy looking for big logical errors :-)

[websoftwares]

After going through this myself I looked this up and its normal behaviour i think,

I run the command from the mysql shell and all seems to be going ok,
looking up the table it doesnt reset auto increment even with administrator rights on the database.

After some searching it seems that in the manual for MySQL some more information is found.

http://dev.mysql.com/doc/refman/5.7/en/alter-table.html
Quoting manual:

To change the value of the AUTO_INCREMENT counter to be used for new rows, do this:

ALTER TABLE t2 AUTO_INCREMENT = value;
You cannot reset the counter to a value less than or equal to the value that is currently in use. 
For both InnoDB and MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, 
the value is reset to the current maximum AUTO_INCREMENT column value plus one.

[aadewojo]

Hi All, I am new to using phpRBAC. However I have been successful in using it. As I am using mssql and the example has just sqllite. I manually created the db and imported the library but when I run the program it gives me this error

Warning: mysqli::prepare() [mysqli.prepare]: Couldn't fetch mysqli in C:\xampp\htdocs\TenderSystem\template_contentII\DEVELOPER\PHP_version\PHP_HTML_Version\PhpRbac\src\PhpRbac\core\lib\Jf.php on line 148

Warning: Jf::sqlMysqli() [jf.sqlmysqli]: Couldn't fetch mysqli in C:\xampp\htdocs\TenderSystem\template_contentII\DEVELOPER\PHP_version\PHP_HTML_Version\PhpRbac\src\PhpRbac\core\lib\Jf.php on line 149

Notice: Unable to prepare statement: SELECT ID AS ID FROM phprbac_roles WHERE Title=? LIMIT 1, reason: in C:\xampp\htdocs\TenderSystem\template_contentII\DEVELOPER\PHP_version\PHP_HTML_Version\PhpRbac\src\PhpRbac\core\lib\Jf.php on line 149

Warning: call_user_func_array() expects parameter 1 to be a valid callback, first array member is not a valid class name or object in C:\xampp\htdocs\TenderSystem\template_contentII\DEVELOPER\PHP_version\PHP_HTML_Version\PhpRbac\src\PhpRbac\core\lib\Jf.php on line 159

Fatal error: Call to a member function execute() on a non-object in C:\xampp\htdocs\TenderSystem\template_contentII\DEVELOPER\PHP_version\PHP_HTML_Version\PhpRbac\src\PhpRbac\core\lib\Jf.php on line 160

Can you please point me to the right direction. Thanks.

[abiusx]

Just to be clear, have you been succesful or unsuccesful, and are you using MS SQL or MySQL?
-A

On Apr 29, 2015, at 11:19 AM, aadewojo notifications@github.com wrote:

Hi All, I am new to using phpRBAC. However I have been successful in using it. As I am using mssql and the example has just sqllite. I manually created the db and imported the library but when I run the program it gives me this error

Warning: mysqli::prepare() [mysqli.prepare]: Couldn't fetch mysqli in C:\xampp\htdocs\TenderSystem\template_contentII\DEVELOPER\PHP_version\PHP_HTML_Version\PhpRbac\src\PhpRbac\core\lib\Jf.php on line 148

Warning: Jf::sqlMysqli() [jf.sqlmysqli]: Couldn't fetch mysqli in C:\xampp\htdocs\TenderSystem\template_contentII\DEVELOPER\PHP_version\PHP_HTML_Version\PhpRbac\src\PhpRbac\core\lib\Jf.php on line 149

Notice: Unable to prepare statement: SELECT ID AS ID FROM phprbac_roles WHERE Title=? LIMIT 1, reason: in C:\xampp\htdocs\TenderSystem\template_contentII\DEVELOPER\PHP_version\PHP_HTML_Version\PhpRbac\src\PhpRbac\core\lib\Jf.php on line 149

Warning: call_user_func_array() expects parameter 1 to be a valid callback, first array member is not a valid class name or object in C:\xampp\htdocs\TenderSystem\template_contentII\DEVELOPER\PHP_version\PHP_HTML_Version\PhpRbac\src\PhpRbac\core\lib\Jf.php on line 159

Fatal error: Call to a member function execute() on a non-object in C:\xampp\htdocs\TenderSystem\template_contentII\DEVELOPER\PHP_version\PHP_HTML_Version\PhpRbac\src\PhpRbac\core\lib\Jf.php on line 160

Can you please point me to the right direction. Thanks.

—
Reply to this email directly or view it on GitHub #37 (comment).

[aadewojo]

I am using MS SQL.