connection roles: "Add User", phpmyadmin 2.5

[BEFOR]

I've consulted with my host, on the "Add User" feature for phpmyadmin2.5

The upper tier tech I spoke with told me that this is mainly a tool for developers, in relation to working with site code. Not intended as a connection for site browsers.

I wanted to use this to tighten up site security, so that most users have a connection for "Read" only. There is no reason they need write, or have admin. role priviledges. Especially for table select statements.

Is my host giving me the runaround? they were quick to point out that if I was interested in site security I should be checking into mySQL injection techniques. This a diversion? (another "less experienced tech" there had started to say otherwise)

According to documentation for the "Database Manager", the interface w/phpmyadmin it says, under "Adding Users" ...
"User Accounts can be mapped to specific people (e.g., an employee responsible for updating a product table ) or to the general public (e.g. visitors to your website who browse the product table). Each database has its own set of users.
Is this right? (according to the ImPAKT software I'm using for login/restricted access/custom triggers, etc. the connections have to be defined prior to the pages, details of the connections can be changed but connection types cannot. This is my concern since I'll have 12 - 15+ insert/update pages made in this way. Apparantly changing connections with it isn't so easy)
Is there a consensus on how this "Add User" should/can be used w/mysql?

[bastien]

Site security is comprised of many parts. As a hosted service, the security of the server is out of your control. You will only be able to affect user connections to the database and coding practices to prevent sql injection attacks...

Adding users with limited abilities is certainly useful, though I find that PHPMyAdmin is not the best tool for the job. Grants on tables have a lot of options and the tool simply doesn't cover them all.

What those grants are is a application dependant, in ours, where I am tightening up security (currently all users have root access to the db (yeah I know, but I just started here and the security issue was never looked at when they started on the app)) where I created three users based on the roles those users play in the application...

some have select only
some have select, update and insert
a few have select update, insert, delete and create table privs

The other main thing to look at is sql injection, if a user puts some sql into a text box and it executes what are the results...
a common one is

username : bob 
for pass word you type in:  ' or 1=1 

//your standard sql statement is 

select userid, name from users where name='$username' and pass='$password'"; 

//this translates to... 
select userid, name from users where name='bob'and pass='' or 1=1'"; 

//this gives automatic access because although the password name is technically an empty string ( '' ), the value 1=1 always evaluates to true and therefore the 'user' is granted access 

You should cover all aspects of security where you have control. The code needs to validate all user inputs and the connections should limit the scope of what that user can do. Here is an article that looks at this from an ASP perspective, but its the same for php as the sql is just a relevant.

hth

[BEFOR]

Thanks bastien,

Much appreciate the hand.

I tried that injection on my login and was glad to find that it wouldn't pass through.

About the phpmyadmin " Add User" different roles; is this for just while being logged on as root user? Will it actually work for those logged on as "client" or no? Because I'm pretty sure my host is running a shared database.

I've also been wondering - for backing up tables, to create a second database. Is it very difficult for a hacker to "cross databases? I have about eight tables and while I dump structure/data individually with cut and paste to notepad, I'm looking for a way to backup entire database simultaneuously. Preferably to floppy but also on second database on server. Making things easier and having more timely backups.

I look forward to hosting my own sites. But I'm too wondering what's involved from a security standpoint. My present host contracts out to a third party consultant to troubleshoot infiltration by hackers, their specialty.

Are there any good single sources out there that cover php/mysql site security? Now if I can only get these margins to contract!

[bastien]

PHPMyAdmin is usually given root access to the database engine (which gives the ability to create new databases) though for a hosted site, you may be restricted to the database itself (thru a usesr account with more privileges)...You will need be logged in this account to make changes / additions to the users (creating new limited users)

When you actually release your application (website ) onto the unsuspecting world, you will specify the new limited accounts as the user to do the connecting...

Back ups are very simple with PHPMyAdmin, simply choose the "Export" tab and follow the simple form...produces the entire data base (structure, data as you require) into a flat file in very little time.

Hosting your owns sites can be a lot of fun. Note that I would recommend a change to a LINUX/ Apache system, MS/ISS is down too often to get patches applied and too full of holes to be effective as a web server...

Take care to stay on top of security updates and patches for whatever OS you choose. Get a machine with a lot of RAM (2 gigs) to deal with high load situations...

Check your local computer book store for books on hacking, the only way to combat the enemy is to learn how they do stuff.

Security for webservers is usually broken down into three parts

physical --> where is the server and who has access to the machine?

operating software --> are the latest patches and security fixes applied regularly? is the regular account holder(s) on that machine given the minimum limited access to the system to do its job effectively?

applications --> are the apps coded with security in mind? passwords encrypted? variables declared? data validated?

hth

[BEFOR]

My host tells me I am logged on with root access.

Not trying to add confusion but there does seem to be some about adding databases. Several days ago I contacted the host, asking how long it would take to add a database -- after I added through my interface, that is -- seeing it then as a menu option. (tech support wasn't even aware I could do this)

Now however, they seemed to have removed this option.

Sorry, but are you saying that by "creating limited users" -- it doesn't matter if I am logged in as root, or the "client" is browsing the site with this connection? By adding "read" user, the read only will be maintained throughout? This is my main concern since I have quite a few pages to make & need have defined connections before doing so.

This is a shared database. The Added Users are connecting to my domain name, not localhost as is the main admin connection.

Am using LINUX/ Apache

What do you think?

[bastien]

The limited read users would be defined by the username and password account that you define when creating the user.

ie

Grant select on mydb.* [or tablename] to bob@'%' identified by 'somePassword'

The above would grant only select access on the database named mydb to username bob. The @'%' mean from anywhere, and the rest is self-explanitory

So then when you create the script to connect to the db

$user = "bob";
$pass = "somePassword";

$conn = mysql_connect(localhost, $user,$pass)or die("Can't connect");

This is the mysql account used by the script.
Read here it may help explain it more clearly than I can