/    Sign up×
Community /Pin to ProfileBookmark

PHP and MySQL User / Security question

Copy linkTweet thisAlerts:
Feb 24.2006

This perhaps better belongs in the SQL forum… not sure.

Say I want to set up a user system, with different levels of access. For instance, say I have the following tables:
T1, T2, T3
and want to set up the following privelege levels:
root: (Sys admin – All priveleges)
user: Select on T1, T2, T3
contributor: Select, Update, Insert on T1, T2, T3
Admin: Select, Update, Insert on T1, T2, T3; Delete on T1, T2

What is the best way to achieve this, keeping in mind that I want to allow an arbitrary number of users?

Should I create 3 MySQL users (root already existing), plus an additional users table in my app database, with the user level listed, and the appropriate priveleges set? or should I create a separate MySQL user with the appropriate privaleges?

How does this affect possibly running multiple applications from the same MySQL server?

Thank you in advance for any help.

to post a comment
PHP

4 Comments(s) ↴

Copy linkTweet thisAlerts:
@chazzyFeb 24.2006 — you're approaching it wrong. you need to differentiate your database users and your application users. mysql doesn't have user roles like you would find in sql server and oracle. you need to divy out privileges one at a time (or create a shell script that does it)
Copy linkTweet thisAlerts:
@aaronbdavisauthorFeb 24.2006 — you're approaching it wrong. you need to differentiate your database users and your application users. mysql doesn't have user roles like you would find in sql server and oracle. you need to divy out privileges one at a time (or create a shell script that does it)[/QUOTE]
I looked at the MySQL documentation and saw that they had a GRANT, REVOKE system, like Oracle, but seeing as I don't totally understand the Oracle user system, I didn't see a big difference. also, differentiating between MySQL and App users make sense, as each app (assuming more than one exists on the machine) would likely have a different user set.

I guess my question then becomes this: When a user logs into the application, how should they be logged into MySQL? I have seen other people, and currently use myself, a system that loads a db_config.inc.php file, with the root username and password stored as variables, but that seems unsafe. If someone manages to get past the php level security, they could send any SQL they want and reak havoc if they are connected as root.
Copy linkTweet thisAlerts:
@chazzyFeb 24.2006 — Oracle has grant/revoke but that's never used and is more of a legacy/backend thing.

you can define user profiles. various users can use the same profile for that.

At my last job, I was on a MySQL integration team. This is what we decided:

  • - Reporting user, select only for a particular database

  • - Query and Update, this is SELECT, INSERT, UPDATE, DELETE on a particular database

  • - schema owner - all privileges on their database (schema in oracle)

  • - dba/super user - all privileges on all databases


    • typically, applications will have the query/update type of user. don't give anyone *.* or mysql.* privileges, as that lets anyone go into your mysql.users table and have fun.
    Copy linkTweet thisAlerts:
    @aaronbdavisauthorFeb 24.2006 — Thank you. That is very helpful. I am not sure I totally get it, but seeing as we haven't gotten to user administration and security in my Oracle class, and the DB I run right now has minimal info and gets minimal traffic, I won't really worry about understanding it completely too much right now.

    thanks for your help.
    ×

    Success!

    Help @aaronbdavis spread the word by sharing this article on Twitter...

    Tweet This
    Sign in
    Forgot password?
    Sign in with TwitchSign in with GithubCreate Account
    about: ({
    version: 0.1.9 — BETA 5.13,
    whats_new: community page,
    up_next: more Davinci•003 tasks,
    coming_soon: events calendar,
    social: @webDeveloperHQ
    });

    legal: ({
    terms: of use,
    privacy: policy
    });
    changelog: (
    version: 0.1.9,
    notes: added community page

    version: 0.1.8,
    notes: added Davinci•003

    version: 0.1.7,
    notes: upvote answers to bounties

    version: 0.1.6,
    notes: article editor refresh
    )...
    recent_tips: (
    tipper: @AriseFacilitySolutions09,
    tipped: article
    amount: 1000 SATS,

    tipper: @Yussuf4331,
    tipped: article
    amount: 1000 SATS,

    tipper: @darkwebsites540,
    tipped: article
    amount: 10 SATS,
    )...