User Access to MySQL Database

[Bob Sanderson]

I have created a MySQL database for my company which is accessed by PHP
pages. I would like to permit some users to edit the records but allow
others read-only access. However, I don't want to have to enter a password
every time I want to edit a record. Is there a way to use our network login
to do this? If not, what is the easiest way to accomplish this? The
database is running on our server and is not accessible via the net.

Any help will be greatly appreciated.

[Tim]

Re: User Access to MySQL Database


Bob Sanderson wrote:[color=blue]
> I have created a MySQL database for my company which is accessed by PHP
> pages. I would like to permit some users to edit the records but allow
> others read-only access. However, I don't want to have to enter a password
> every time I want to edit a record. Is there a way to use our network login
> to do this? If not, what is the easiest way to accomplish this? The
> database is running on our server and is not accessible via the net.
>
> Any help will be greatly appreciated.[/color]

This is one way to do it

In mysql use CREATE USER and GRANT to set up a user account with read
only permissions, you probably have a user account with full
permissions to use but you may want to setup another user account with
limited write permissions to give to others.

In the php script call a system command to get the login name of the
current user. On *nix systems its 'whoami', on windows I think its
whoami.exe but don't quote me on that..

Check the result of whoami and change the user and password arguments
in mysql_connect accordingly.

Regards

Tim

[Paul Lautman]

Re: User Access to MySQL Database

Tim wrote:[color=blue]
> Bob Sanderson wrote:[color=green]
>> I have created a MySQL database for my company which is accessed by
>> PHP pages. I would like to permit some users to edit the records but
>> allow others read-only access. However, I don't want to have to
>> enter a password every time I want to edit a record. Is there a way
>> to use our network login to do this? If not, what is the easiest way
>> to accomplish this? The database is running on our server and is not
>> accessible via the net.
>>
>> Any help will be greatly appreciated.[/color]
>
> This is one way to do it
>
> In mysql use CREATE USER and GRANT to set up a user account with read
> only permissions, you probably have a user account with full
> permissions to use but you may want to setup another user account with
> limited write permissions to give to others.
>
> In the php script call a system command to get the login name of the
> current user. On *nix systems its 'whoami', on windows I think its
> whoami.exe but don't quote me on that..[/color]

But wouldn't the whoami run on the server, whilst the user is logged on to
the client?

[Tim]

Re: User Access to MySQL Database


Paul Lautman wrote:[color=blue]
> Tim wrote:[color=green]
> > Bob Sanderson wrote:[color=darkred]
> >> I have created a MySQL database for my company which is accessed by
> >> PHP pages. I would like to permit some users to edit the records but
> >> allow others read-only access. However, I don't want to have to
> >> enter a password every time I want to edit a record. Is there a way
> >> to use our network login to do this? If not, what is the easiest way
> >> to accomplish this? The database is running on our server and is not
> >> accessible via the net.
> >>
> >> Any help will be greatly appreciated.[/color]
> >
> > This is one way to do it
> >
> > In mysql use CREATE USER and GRANT to set up a user account with read
> > only permissions, you probably have a user account with full
> > permissions to use but you may want to setup another user account with
> > limited write permissions to give to others.
> >
> > In the php script call a system command to get the login name of the
> > current user. On *nix systems its 'whoami', on windows I think its
> > whoami.exe but don't quote me on that..[/color]
>
> But wouldn't the whoami run on the server, whilst the user is logged on to
> the client?[/color]

Yeah you're right, dont know what I was thinking. Ta

Tim

[Gordon Burditt]

Re: User Access to MySQL Database

>I have created a MySQL database for my company which is accessed by PHP[color=blue]
>pages. I would like to permit some users to edit the records but allow
>others read-only access.[/color]

Ok, decide what will enforce this: PHP or MySQL? MySQL permissions
do not easily handle requirements like "a user may only edit his own
record, but no others".

If MySQL permissions are used to enforce permissions, the user using
the web page enters his MySQL login and password. For convenience,
save these in a session variable so re-entering these on each access
is not needed.

If PHP permissions are used to enforce permissions, the PHP pages
need a login setup. You could use .htaccess, letting Apache enforce
the access and use $_SERVER['AUTH_USER'] as a basis for figuring
out who's logged in. The pages usually use a MySQL login owned by
the page itself, capable of making changes (and it's probably
embedded in the page. My recommendation is to put the login/password
combination in a PHP include file *outside* the document tree). If
PHP itself is doing the login logic (rather than Apache) there's
probably a database table for web page logins, passwords, and level
of privilege of each user. It's up to PHP to decide what MySQL
queries to allow to each web user. Web users and MySQL users are
different; a web user need not have a MySQL username at all.
[color=blue]
>However, I don't want to have to enter a password
>every time I want to edit a record.[/color]

Sessions can let you enter the password once a session.
[color=blue]
>Is there a way to use our network login
>to do this? If not, what is the easiest way to accomplish this? The
>database is running on our server and is not accessible via the net.[/color]

It had better be accessible to the server running PHP, or you're
not going to get very far.

Gordon L. Burditt

[lorento]

Re: User Access to MySQL Database

Bob Sanderson wrote:[color=blue]
> I have created a MySQL database for my company which is accessed by PHP
> pages. I would like to permit some users to edit the records but allow
> others read-only access.
> However, I don't want to have to enter a password
> every time I want to edit a record. Is there a way to use our network login
> to do this? If not, what is the easiest way to accomplish this? The
> database is running on our server and is not accessible via the net.[/color]

Make a different landing page for each user.
E.g:
if user permitted to edit records, landing to:

http://intranet/page1.php

if user read only, landing to: http://intranet/page2.php
[color=blue]
>From the server side, you can make a settings,[/color]
page1.php, only can be accessed from computer1, computer2
page2.php, only can be accessed from computer3, computer4

like that. Hope it will help you.

thanks,

Lorento
--

http://www.mastervb.net

http://www.padbuilder.com

http://www.immersivelounge.com

[Jerry Stuckle]

Re: User Access to MySQL Database

Bob Sanderson wrote:[color=blue]
> I have created a MySQL database for my company which is accessed by PHP
> pages. I would like to permit some users to edit the records but allow
> others read-only access. However, I don't want to have to enter a password
> every time I want to edit a record. Is there a way to use our network login
> to do this? If not, what is the easiest way to accomplish this? The
> database is running on our server and is not accessible via the net.
>
> Any help will be greatly appreciated.[/color]

It's not hard, Bob.

First o fall, it will be a lot easier to handle this in PHP. Just keep one
database password for all the operations.

When the user signs in with thiner own userid, determine if they can edit
records, and if so, which ones(s). Set the appropriate flag(s) in the $_SESSION
array and check them later.

Alternatively, if the choice is to edit all rows or no rows, you could give each
person their own MySQL logon/password and keep the information in the $_SESSION
array. If it is there, use it. If it's not there, ask them for it. But this
won't work if they can only edit a subset of records unless you implement a view
for each possible subset and grant appropriate permissions on the view.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===