Overview:
I am taking on a project to allow restricted access to 500 Microsoft Publisher files (simply a HTML page with synchronized Powerpoint + Audio presentation) online where customers will be granted directory access to certain reports that they purchased. Please note that MS Publisher is just a simple webpage that plays audio/powerpoint slides. Some users will have access to certain reports, others may have access to all reports. I want the admin of the site to be able to grant/remove access in the database to users when they purchase the report.
The key goal to the project is to have the user be able to login, see a list of the reports they purchased with a link to view them. Upon clicking to view a report, their login/key would be passed to the report page they are trying to access and pending on the database, would grant or deny access. A simple example of a directory with a report url would be http://website.com/report/report1/index.htm, http://website.com/report/report2/index.htn, etc. etc.
I have a few questions on database design and the best way to handle user privileges.
Database schema:
REPORTS
unique_id
company
description
report_url
USER
id
username
password
email
(?)permissions (?)
My Questions
1) What is the best way to link the USER table to the REPORT table? Meaning, should I create a seperate table called PERMISSIONS to handle which users are allowed to view which reports? Some users will have access to a few reports, others may have access to all of them. Will there be a list of 500 rows for each user with a '0' or '1' to keep a tally of what reports they have access to? Please advise?
2) Is .htaccess a good way to handle permissions, where a database would store permissions for each user and when they try to view a directory it would authenticate their access and allow or deny?
Any advice or suggestions on my database schema in regards to protected access or privelages would be appreciated! Thanks!
