Re: My sql Security
On Wed, 2010-04-28 at 13:58 +0530, Vikram A wrote: Hi all, I have some security issues. I would like to have your suggestions/solutions. I have winserver2003 with mysql 5.1.45. We have client serve application that allows multi-login system with various people. I am getting user name, password for database login when the try to use login [which is for Application]. By using DB the user name and the password, people who know the mysql are opening the DB using some GUI tools. How this can be avoided; because it is major issue right now in my work place. Please Can any one can help me? Thank you VIKRAM A 1.) Use MySQL connection limits to restrict the ways a client may impact performance. In the longer term look to limit table access with stored procedures or (when efficient) views. 2.) Restrict the database usernames/passwords by IP address and/or SSL client certificates and restrict access to the authorised client machines from the people causing a problem. 3.) If the clients and their credentials can't be restricted from the problem group, use MySQL proxy or its equivalent to filter exactly which queries may be applied so that only the actions already taken by the application may be performed by people using it's login credentials. 4.) If the problem is being caused by people on the authorised clients performing reasonable actions for those clients, then your problem can't be solved techincally aside from by seperating the application authentication credentails from the MySQL ones, or by scaling to allow the clients usage levels. Like Johan De Meersman I think the real problem you have is probably that the application uses MySQL access credentials as enduser credentials. Per-application user database users are unusual in my industry. If you need per-user access right granularity in database access it should still be disconnected from application login credentials. I'd probably do: field | application | mysql === username | current username | current username password | current password stored as hash | unique password stored encrypted by hash of (current password + salt) In this way only an application working on the user's behalf to which the user had submitted their password would be able to obtain their unique database password but wouldn't need to store the plaintext password in the user's session. HTH Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Pivot Query in
On Wed, 2010-04-28 at 15:24 +0530, VR Venugopal Rao wrote: I am working on Java-HTML-MS Access Backend database. Now office is removing all unlicensed softwares and they are removing microsoft applications also and hence the necessity to shift from MS Access to MySQL has arised. In MS Access I used to generate one pivot query which gets the following report from the database which contains the following fields: Date, ProjectCode Building, Number of Copies I want to get a Connsolidate Report of Project Code RD STP 1007304--04---04 (Group by Project Code)(Sumtotal Building wise). I have tried to execute the following code : select pcode, building, sum(ncopies) from request group by pcode,building It is giving the following repott 1007304--RD--04 1007304-STP--05 Here there is a repetition of pcode, How can I avoid this. Don't use unlicenced commercial software in the first place? I want to get the Building Name on top as Column Names and bottom I should get the Count. Unlike MS Access MySQL does not directly support 'pivot queries', the portable equivalent is a cross-tab query. In order to allow for the addition and removal of departments you'll want to dynamically generate the query each time in a client application or a stored procedure Crosstab queries in MySQL http://rpbouman.blogspot.com/2005/10/creating-crosstabs-in-mysql.html Crosstab queries and their native support in MS Access http://www.paragoncorporation.com/ArticleDetail.aspx?ArticleID=25 HTH Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: problem reading array data
On Wed, 2010-04-07 at 21:37 -0700, Karthick Subramanian wrote:
I need to read an array's content using a while loop and inside the loop
read another array's content. Using the variables from the two arrays I need
to execute a query. My problem is the inner while loop reads all records of
the array whereas the outer while loop exits after reading the first record.
That is after execution of the inner while loop finishes, the control does
not move to the outer while loop to read the next array element.
I am appending my code below. Please help me solve this problem
$arrdata = mysql_query(SELECT OldDeptCode, MajorCode FROM
tblolddeptcodemajorcode);
$result2 = mysql_query(SELECT SSN, DeptCode, ActCode FROM
tblapprovedactivitydetail);
while($info = mysql_fetch_assoc($arrdata))
{
$OldDeptCode = $info['OldDeptCode'];
$MajorCode = $info['MajorCode'];
while($row2 = mysql_fetch_assoc($result2))
{
$SSN = $row2['SSN'];
$DeptCode = $row2['DeptCode'];
$ActCode = $row2['ActCode'];
$query = INSERT INTO test1 (SSN, MajorCode, ActCode) VALUES
('$SSN', '$MajorCode', '$ActCode');
if($OldDeptCode != 'COAS' $OldDeptCode != 'CSS' $OldDeptCode
!= 'EC' $OldDeptCode != 'EECS' $OldDeptCode != 'FW' $OldDeptCode !=
'GEO' $OldDeptCode != 'SED' $OldDeptCode != 'VM' $OldDeptCode ==
$DeptCode)
{
mysql_query($query);
}
}
echo done;
}
echo all done;
Thank you
OK, at a 5 minute glance:
1.) I think the reason it isn't working is that MySQL/PHP don't support
reading from two buffered result sets concurrently: read from one then
the other.
2.) The code you've written is very inefficent. Databases are fast at
mangling database results whilst PHP isn't. You can filter and combine
both data set into the single query:
SELECT OldDeptCode, MajorCode, SSN, DeptCode, ActCode FROM
tblolddeptcodemajorcode old
INNER JOIN tblapprovedactivitydetail new on new.DeptCode=old.OldDeptCode
where old.OldDeptCode not in
('COAS','CSS','EC','EECS','FW','GEO','SED','VM')
If locking tables isn't a problem in your envrioment the entire loop can
be replaced with and insert into test1 SELECT statement. Use SQL
for record manipulation, that's its purpose. For maximum efficeny make
sure both tblolddeptcodemajorcode.OldDeptCode and
tblapprovedactivitydetail.DeptCode have indexes and the same format. Use
explain on the query to check its performance.
3.) If this is part of a long running script or library make sure you
use mysq_free_result() on the result set
4.) You might want some error handling for the queries failing.
HTH
Nigel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: subquery multiple rows
On Wed, 2010-04-07 at 11:37 -0400, kalin m wrote: hi all... i have a bit of a problem with this: table products: -- prod | category | -| boots | winter| boots | summer | boots | spring | shoes | spring | shoes | winter| shoes | fall | shoes | summer | -- when i do this: select distinct prod as m, (select category from products where email = m) as n from products; i get: ERROR 1242 (21000): Subquery returns more than 1 row i know that the subquery returns more than one rows. i hope so... what i'd like to see as result is: - m | n | - boots | winter, summer, spring | shoes | spring, winter, fall , summer | - I think you want: select prod as m, group_concat(category) as n from products group by prod; hth Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
