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?

Answer 1

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

Answer 2

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.

Answer 3

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