Prepared statement with SET PASSWORD in a stored procedure

#11

Robert Hairgrove wrote:

Quote:

Jerry Stuckle wrote:
Robert Hairgrove wrote:
Can you (or anyone else) contribute something towards answering my
original question now, please?

I am. It's just not the answer you want.

This is one line of code in PHP. You're making a mountain out of a
molehill.

Additionally, if this is a web application, there is no need to give
each user a MySQL id. I use a single id for web access to MySQL, and
a separate table for users and their privileges.

Well, I do appreciate what you are saying and taking the time to do so.
However, the setup is a little more complicated which means that (IMHO)
it is actually better to use different MySQL accounts for accessing this
data.

The application will be used to track student absentee data at my school
and send out an automatic notification to the home room teacher when a
student is absent from any class. Each teacher needs to be able to log
into the system, fill out the form and click to send the notification.
The data is stored in the process; students are allowed a quota of
absent marks each semester. There is one table of student data for all
the teachers, but each teacher only needs to see their own students. So
I have one view which filters the data according to the user which is
currently logged in. Each user gets SELECT privilege on the view, but
not on the table itself.

Which can easily be done with no views at all.

Quote:

It seems to me to be easier to manage separate user accounts for this
than setting up a different view for each user. I don't want to store
any login data in the PHP files because I have no control over the
server's file system and don't know who might be able to read my PHP
files. Besides, there have been several attempts to hack our school's
server in the past (fortunately, none were very successful, but you
never know when they will be).

If you have no control over the file system, you have virtually no
security. Security starts at limiting access to the file system, both
physically and logically.

Also, having multiple signons like you have is no more secure than a
single signon for everyone. It only provides the appearance of security.

And no views are required.

Quote:

Of course, there will have to be one user login stored in the PHP page
for changing the password because the regular user won't have their
password (i.e. will have forgotten it). But that PHP user only gets an
EXECUTE privilege on the SP which I am trying to set up. I want to use a
stored procedure for this because otherwise, I'd have to grant that user
too many privileges. And there are other mechanisms installed which make
it next to impossible to abuse that procedure, even if someone were to
read the username and password from the PHP file and log in with those
credentials through a terminal or with some tool such as phpMyAdmin.

I hope this makes things a little clearer. Perhaps there is a better
solution which guarantees the level of security we need here?

It makes it clearer, but your method has no additional security.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#12

Jerry Stuckle wrote:

Quote:

Robert Hairgrove wrote:
There is one table of student
data for all the teachers, but each teacher only needs to see their
own students. So I have one view which filters the data according to
the user which is currently logged in. Each user gets SELECT privilege
on the view, but not on the table itself.

Which can easily be done with no views at all.

I'm all ears...

Quote:

It seems to me to be easier to manage separate user accounts for this
than setting up a different view for each user. I don't want to store
any login data in the PHP files because I have no control over the
server's file system and don't know who might be able to read my PHP
files. Besides, there have been several attempts to hack our school's
server in the past (fortunately, none were very successful, but you
never know when they will be).

If you have no control over the file system, you have virtually no
security. Security starts at limiting access to the file system, both
physically and logically.

I'm not the server admin for the school, so *I* don't have control over
the file system security. But I agree, almost everything depends on that.

Quote:

Also, having multiple signons like you have is no more secure than a
single signon for everyone. It only provides the appearance of security.

It makes auditing a lot easier, though.

Quote:

And no views are required.

OK ... how?

In case you haven't noticed, I'm a big fan of implementing RI and
security in the database if possible. This data might be accessed by
many different applications in the future, so I don't believe that
security should be left entirely up to the application.

#13

Robert Hairgrove wrote:

Quote:

Jerry Stuckle wrote:
Robert Hairgrove wrote:
There is one table of student data for all the teachers, but each
teacher only needs to see their own students. So I have one view
which filters the data according to the user which is currently
logged in. Each user gets SELECT privilege on the view, but not on
the table itself.

Which can easily be done with no views at all.

I'm all ears...

The same way as you do it with a view - you use the teacher_id in the
WHERE clause of your SELECT statement. That's how it's done in
virtually every PHP web-based application - and for good reasons.

You have control of the application - and what can be selected.

Quote:

It seems to me to be easier to manage separate user accounts for this
than setting up a different view for each user. I don't want to store
any login data in the PHP files because I have no control over the
server's file system and don't know who might be able to read my PHP
files. Besides, there have been several attempts to hack our school's
server in the past (fortunately, none were very successful, but you
never know when they will be).

If you have no control over the file system, you have virtually no
security. Security starts at limiting access to the file system, both
physically and logically.

I'm not the server admin for the school, so *I* don't have control over
the file system security. But I agree, almost everything depends on that.

If the file system is not secure (both physically and logically), the
system is not secure.

Quote:

Also, having multiple signons like you have is no more secure than a
single signon for everyone. It only provides the appearance of security.

It makes auditing a lot easier, though.

No, it makes things more complicated. Just one more thing which is
required.

Quote:

And no views are required.

OK ... how?

In case you haven't noticed, I'm a big fan of implementing RI and
security in the database if possible. This data might be accessed by
many different applications in the future, so I don't believe that
security should be left entirely up to the application.

I am, too - to a certain extent. However, you are not increasing
security. You are only increasing the appearance of security.

And you cannot just leave security to the database in this matter. You
must also have security in the application, or it will be hacked.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#14

Peter H. Coffin wrote:

Quote:

Jerry's asking why you are maintaining your application's security in
mysql's security, instead of in the application and its data tables,
where it belongs. And by "belongs", I mean "where it can get maintained
and backed up with all the *rest* of your application's data, and where
it ends up not being potentially intermingled with *other application's*
data". For example, you have a user of one application with the id of
"jsmith". A different jsmith wants to use the other application. Why bar
him from his usual user ID?

In this particular situation, "jsmith" would always be the same person
(user). Besides, they can't change their user IDs -- only the password.

Besides that, there are no "other applications" that can access this
data (unless our IT admin decides otherwise, but I would expect that he
would contact me because it's my "sandbox").

#15

Jerry Stuckle wrote:

Quote:

Robert Hairgrove wrote:
In case you haven't noticed, I'm a big fan of implementing RI and
security in the database if possible. This data might be accessed by
many different applications in the future, so I don't believe that
security should be left entirely up to the application.

I am, too - to a certain extent. However, you are not increasing
security. You are only increasing the appearance of security.

And you cannot just leave security to the database in this matter. You
must also have security in the application, or it will be hacked.

I believe it is a question of: how many potential points of failure can
there be? If I have an omnipotent user whose login data is stored in
plain text in a PHP file, we are screwed the moment that file has been
read by a malicious person who is intent on accessing the database.

It is a lot harder if that isn't possible.

#16

Robert Hairgrove wrote:

Quote:

Jerry Stuckle wrote:
Robert Hairgrove wrote:
In case you haven't noticed, I'm a big fan of implementing RI and
security in the database if possible. This data might be accessed by
many different applications in the future, so I don't believe that
security should be left entirely up to the application.

I am, too - to a certain extent. However, you are not increasing
security. You are only increasing the appearance of security.

And you cannot just leave security to the database in this matter.
You must also have security in the application, or it will be hacked.

I believe it is a question of: how many potential points of failure can
there be? If I have an omnipotent user whose login data is stored in
plain text in a PHP file, we are screwed the moment that file has been
read by a malicious person who is intent on accessing the database.

It is a lot harder if that isn't possible.

No harder the way you have it than it is to change a password to
something the user knows. Or, if they have access to the machine,
starting MySQL with no protection at all, for instance.

You only have the appearance of additional security - not the reality.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#17

Robert Hairgrove wrote:

Quote:

Peter H. Coffin wrote:
Jerry's asking why you are maintaining your application's security in
mysql's security, instead of in the application and its data tables,
where it belongs. And by "belongs", I mean "where it can get maintained
and backed up with all the *rest* of your application's data, and where
it ends up not being potentially intermingled with *other application's*
data". For example, you have a user of one application with the id of
"jsmith". A different jsmith wants to use the other application. Why bar
him from his usual user ID?

In this particular situation, "jsmith" would always be the same person
(user). Besides, they can't change their user IDs -- only the password.

Besides that, there are no "other applications" that can access this
data (unless our IT admin decides otherwise, but I would expect that he
would contact me because it's my "sandbox").

He's talking about other applications. Not these.

You could very well have another "jsmith" doing something entirely
different on the database.

But you've already made up your mind that people with years more
experience don't know as much as you do. So have fun trying to solve
your problem.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#18

On Tue, 22 Jun 2010 23:42:48 +0200, Robert Hairgrove wrote:

Quote:

Jerry Stuckle wrote:

Robert Hairgrove wrote:

In case you haven't noticed, I'm a big fan of implementing RI and
security in the database if possible. This data might be accessed by
many different applications in the future, so I don't believe that
security should be left entirely up to the application.

I am, too - to a certain extent. However, you are not increasing
security. You are only increasing the appearance of security.

And you cannot just leave security to the database in this matter.
You must also have security in the application, or it will be hacked.

I believe it is a question of: how many potential points of failure
can there be? If I have an omnipotent user

You don't need one of those. You should have a role user for the web
application that's allowed to read and insert records in some tables,
just read in others, and maybe only read some columns of yet other
tables. Other things hooked up to the database get used for inserting
records in other tables, and changing records. Adding or updating
a student's entity record, for example, should not be part of the
day-to-day operation of the system, and there's thus zero reason to have
the web application allowed to ever do that.

Quote:

whose login data is stored in plain text in a PHP file,

Why plain text?

Quote:

we are screwed the moment that file has been
read by a malicious person who is intent on accessing the database.

To do what? read data?

Quote:

It is a lot harder if that isn't possible.

It's a apparantly a pain in the tuchis to write and administrate, else
you'd not have any problems with changing passwords.

--
23. I will keep a special cache of low-tech weapons and train my troops
in their use. That way--even if the heroes manage to neutralize my
standard-issue energy weapons useless--my troops will not be overrun
by a handful of savages armed with spears.

#19

On Tue, 22 Jun 2010 23:36:17 +0200, Robert Hairgrove wrote:

Quote:

Peter H. Coffin wrote:
Jerry's asking why you are maintaining your application's security in
mysql's security, instead of in the application and its data tables,
where it belongs. And by "belongs", I mean "where it can get maintained
and backed up with all the *rest* of your application's data, and where
it ends up not being potentially intermingled with *other application's*
data". For example, you have a user of one application with the id of
"jsmith". A different jsmith wants to use the other application. Why bar
him from his usual user ID?

In this particular situation, "jsmith" would always be the same person
(user). Besides, they can't change their user IDs -- only the password.

Besides that, there are no "other applications" that can access this
data (unless our IT admin decides otherwise, but I would expect that he
would contact me because it's my "sandbox").

Rule 6 of security: Never allow yourself to design things with the
assumption that a current limitation of the system will exist forever.
Or even through the end of the week. Always design as though it's a tool
your boss uses every day and his secretary wants you fired.

--
If sharing a thing in no way diminishes it, it is not rightly owned if
it is not shared. -- St. Augustine

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Prepared statement with SET PASSWORD in a stored procedure

comp.databases.mysql comp.databases.mysql

Re: Prepared statement with SET PASSWORD in a stored procedure - 06-22-2010 , 11:18 AM

Re: Prepared statement with SET PASSWORD in a stored procedure - 06-22-2010 , 11:35 AM

Re: Prepared statement with SET PASSWORD in a stored procedure - 06-22-2010 , 03:14 PM

Re: Prepared statement with SET PASSWORD in a stored procedure - 06-22-2010 , 04:36 PM

Re: Prepared statement with SET PASSWORD in a stored procedure - 06-22-2010 , 04:42 PM

Re: Prepared statement with SET PASSWORD in a stored procedure - 06-22-2010 , 07:11 PM

Re: Prepared statement with SET PASSWORD in a stored procedure - 06-22-2010 , 07:14 PM

Re: Prepared statement with SET PASSWORD in a stored procedure - 06-23-2010 , 05:25 PM

Re: Prepared statement with SET PASSWORD in a stored procedure - 06-23-2010 , 05:25 PM