User permissions with accessing some data, from a MySQL database, with PHP?

Question

I have this simple MySQL table, which I called data_table:

/--------------+------------------+-------------------\
+    data_id   +   data_content   +  addition_content |
+--------------+------------------+-------------------+
+    data_1    +  data_content_1  +   comment_for_1   |
+    data_2    +  data_content_2  +   comment_for_2   |
+    data_3    +  data_content_3  +   comment_for_3   |
+              +                  +                   |
+     ...      +       ...        +         ...       |
+              +                  +                   |
+    data_n    +  data_content_n  +   comment_for_n   |
\--------+-----+------------------+-------------------/

To help users can submit their data to my database, I have created a simple HTML form (with PHP core) for easier data submission.

For getting data from this table (data_table), I have used this query (from my_query.php):

// ...

$var_get = $_GET['url'];

$query_1 = mysql_query("SELECT * FROM `MyDB` WHERE `data_id` = '{$var_get}'");

while ($query_2 = mysql_fetch_array($query_1)) 

{

    echo $query_2['x_2'];


}

// ...

Anyone can see any result (from echo $query_2['x_2'];) when they access my_query.php page with a $_GET[]; value.

Now, I don't want to allow all people can access any data from my website. So, I decide to allow the access-permission to certain users only; and, I have an idea:

I will create a new MySQL table (user_table):

/--------------+------------------+-------------------\
+    user_id   +     user_name    +   user_password   |
+--------------+------------------+-------------------+
+    user_1    +    user_name_1   +     password_1    |
+    user_2    +    user_name_2   +     password_2    |
+    user_3    +    user_name_3   +     password_3    |
+              +                  +                   |
+     ...      +       ...        +         ...       |
+              +                  +                   |
+    user_n    +    user_name_n   +     password_n    |
\--------+-----+------------------+-------------------/

Then, I will add a new data column (data_owner) to existed table (data_table). Every record (data line) will be have a owner; this owner is the existed user (in user_table) who submitted their data to my website.
Finally, I will add a new data column (allowed_user) to existed table (data_table). Every record (data line) will be have some allowed users; these allowed users are existed users (in user_table) who are allowed to see result (from echo $query_2['x_2'];). If someone is not an allowed user (from certain record), they won't see the real data.

My idea is not bad?

Sorry, programming is not my job; so, there are some limitations in my programming skills. Can you give me an example, please?

Sounds OK if the user who has the allowed user attrubute set will have access to his data and only his data. No access from a user to data of others? — PaulH
– PaulH, Commented Jul 6, 2016 at 5:52
Be sure to protect against SQL injection in a public site stackoverflow.com/questions/60174/… — PaulH
– PaulH, Commented Jul 6, 2016 at 5:53
Instead of filtering in the while loop in PHP, consider adding the filter to the WHERE of your SQL statement, that will be more efficient. — PaulH
– PaulH, Commented Jul 6, 2016 at 5:57
don't write code using mysql_ , use either pdo or mysqli_, mysql_ is already fully deprecated which means it would stop working in a future php release. mysqli_ is almost the same, and just as easy to use conceptually, but has some slight differences so make sure you use the right syntax for it. Any online examples you find using mysql_ are old and should be considered obsolete. — Lizardx
– Lizardx, Commented Jul 19, 2016 at 23:20

Kevin Waterson · Accepted Answer · 2016-07-06 05:58:36Z

2

What you are describing is a basic login/auth system for users. https://www.phpro.org/tutorials/Basic-Login-Authentication-with-PHP-and-MySQL.html

With this in place, users should only be allowed to update their own records.

If you would like admin users, or moderators, you could set up a simple Access Control List. eg:

CREATE TABLE levels(
  id int not null primary key auto_increment'
  name varchar(20) not null
);
INSERT INTO levels(id, name) values (1,'admin');
INSERT INTO levels(id, name) values (2, 'moderator');
INSERT INTO levels(id, name) values (3, 'guest');

When a user logs in, you can check if they have moderator, or admin privileges to look at other user data.

There are also ACL implementations ready to go if you wish to use an existing solution.

Kev

answered Jul 6, 2016 at 5:58

Kevin Waterson

8481 gold badge9 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mike Robinson Over a year ago

Footnote: It's well worth considering that virtually this entire application "has already been done to death" ... a so-called "CRUD = Create Read Update Delete" web-site for a database somewhere, necessarily including some notion of user-privileges. "There are zillions of 'em, already." Before anyone starts writing yet-another one "today," they should spend some serious time e.g. at GitHub looking for what has already been done by someone else.

user27976 · Accepted Answer · 2016-07-06 06:28:16Z

0

You need to create one more field called role for example: user_id,user_name,user_password,user_role

while register you need to pass role type as (admin,superadmin(optional),publisher,guest) you can add whatever you need in the role.

based on role you will need to give permission.

answered Jul 6, 2016 at 6:28

user27976

1491 silver badge7 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:32:38Z

No, I don't think so! Your idea is not the best fix for the problem.

What you really need is an Access Control List just like Kevin Waterson said

I wouldnt advice you to implement your own ACL though. You could check out this ACL Library on GitHub, it's one of the best ACL Libraries for PHP out there, it doesnt have too much learning curve (if at all it has any) and it just works.

If you were to use the Samshal\Acl Library which I linked to above, the following snippet will help you protect your resources:

        $permissionManager = new \Samshal\Acl(); #<-- Instantiate the ACL Class

        #When you create a new data, add it to your ACL by doing
        $permissionManager->addResource('data_1'); #<- data_1 is the id of your newly created data

        #You need to also register users to the ACL
        $permissionManager->addRole('user_n'); #<- user_n is the id (probably the username) of your just created user

        #Then grant permissions by doing:
        $permissionManager->user_n->can->view('data_1');
        #Or deny permissions by doing
        $permissionManager->user_n->cannot->view('data_1');

Save a serialized$permissionManager in a database or file and voila! You have successfully just protected every resource.

The library has other cool features too like allowing you to grant edit, create, delete or even allow you to create and attach an entirely custom permission like approveDataContent permissions on your resources. You can visit https://github.com/Samshal/Acl/blob/master/README.md to learn more about the ACL library

Collectives™ on Stack Overflow

User permissions with accessing some data, from a MySQL database, with PHP?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related