By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,491 Members | 1,168 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,491 IT Pros & Developers. It's quick & easy.

User Access to MySQL Database

P: n/a
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.
Jun 2 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Tim

Bob Sanderson wrote:
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.


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

Jun 2 '06 #2

P: n/a
Tim wrote:
Bob Sanderson wrote:
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.


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..


But wouldn't the whoami run on the server, whilst the user is logged on to
the client?
Jun 2 '06 #3

P: n/a
Tim

Paul Lautman wrote:
Tim wrote:
Bob Sanderson wrote:
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.


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..


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


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

Tim

Jun 2 '06 #4

P: n/a
>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.
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.
However, I don't want to have to enter a password
every time I want to edit a record.
Sessions can let you enter the password once a session.
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.


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

Gordon L. Burditt
Jun 2 '06 #5

P: n/a
Bob Sanderson wrote:
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.
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
From the server side, you can make a settings,

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

Jun 3 '06 #6

P: n/a
Bob Sanderson wrote:
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.


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.
js*******@attglobal.net
==================
Jun 3 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.