Database

5-Oct-06 6:42

Hi gurus,

I've created a few database programs using Access' mdb in the past. Now that I got a bigger project that may handles about 10 user (and more data in the same time), I am considering using a better solution. The problems I have with Access database are:
1. The password can be cracked quite easily.
2. It gets less responsive as the file get larger (and more likely to get corrupted, I heard).
3. It takes some time to save changes to the file, creating bottleneck and (sometimes) non-updated data for other users (I think this is the downside of file-based database, as processing is handled by each client).

However, what I like about it are:
1. All in one file, so it's easier to backup.
2. Password not linked to windows, so (supposedly) only I who can open it.
3. Free.

At first, I am considering using SQL Express 2005, but since it links the security to Windows login, I can't prevent users from opening the database (since the program I am creating will be run under Admin login).

So, I am asking for advice: what database should I use now? I have no experience with other database other than Access. I don't need fancy bells and whistles. Preferably:
1. Password is not linked to windows. So only I and the program who know the password.
2. Server-client type (processing is handled by the server machine), so that changes are saved instantly and network traffic can be reduced greatly.
3. Can handle at least 5 users concurrently.
4. Can be backed up easily.
5. Free (this might not be possible? Smile | :)

Thanks in advance for any input, it will be greatly appreciated.

Chris Buckett5-Oct-06 8:51

Chris Buckett

5-Oct-06 8:51

Afik, sql express is just a cut down sql server instance, in which case, you can create a user within the database, and give it a password. You then need to use this user/pw combination to access the data.

Also, end users can't just open an sql express data file, as there is no "front end" like access. They will need to either query it through access / excel / other database access method. The closest thing to a front end is the SQL Server Management Studio Express (which is a free download from MS). In which case read up on "user permissions"

I suppose it depends upon who you want to keep out of the data, if it's paying customers, then you need to think why they are not allowed open access to their data (I've made that mistake before with ms access passwords), if it's internal users (ie in a business environment), then you may have policies / procedures in place to prevent users from accessing data that's not within their remit (i.e. they get sacked for for hacking).

Anyway, other databases you may want to look at are interbase or mysql, but I'd say that sql server or sql express are probably the best way to go.

ChrisB

Joe Smith IX5-Oct-06 22:22

5-Oct-06 22:22

Thanks for replying. But I am still not convinced that SQL Express is the best choice here. Let me explain a bit.

Let say this program is for stock management in a retail store. There are several employees that need to use the program: the cashier for selling the goods, the stock boy for stocking the goods, and all employess to clock in/out.

I don't see the point of having different login for each user, all of them need to access the database, only different tables. So I plan to prompt the user a password (managed from the [USER] table) upon opening the program to determine which functions/dialogs they can access (sales dialog, inventory dialog, clock in/out dlg, etc), which all actions will be recorded in databases.

Now I want to prevent any employee to open the database and, lets say, edit the remaining stock of an item (for whatever reason, good or bad). I want to have all changes to the database can only be made thru the program. I don't want them to be able to view it either, because it has tables with confidential information that not all employees should see (passwords, profits, etc.)

Since all employees need to access and edit the database on daily basis (at least thru clocking in/out), they all will need full access to the database, correct? And because only several computer connected thru simple LAN (without internet), I don't see the point of managing several logins, they all can use the admin login.

Now, from what I understand, I CANNOT do this with SQL Express. Since I need to give full access, they can use SQL Management Studio to access the database directly. Is there no way to separate windows login and database login, like MS Access?

As for Interbase and MySql, are their passwords strong enough or can be cracked by hacker as well? (Since they are not Microsoft's, I am assuming their password is not linked to Windows). Are they file-based or client-server (which need to be installed in a Window Server)?

Thanks a lot for any input.

Chris Buckett5-Oct-06 22:39

Chris Buckett

5-Oct-06 22:39

Joe Smith IX wrote:
don't see the point of having different login for each user, all of them need to access the database,

The application uses the database's username / password security to access the database - individual user authentication (possibly based upon a username / password) can be left to you to create in the database tables if you require it.

I would suggest you read up on "mixed mode"[^] authentication (this is where you install sql server (express) in such a way as to require a valid password to access the database (this password is part of the dsn - e.g.

"Data Source=MyServer;Initial Catalog=MyDatabase;User Id=myUsername;Password=myPassword;"

myUsername and myPassword is not the individual users (i.e. person's) username, but the username you specify in the database and your application uses to access the data. It sounds like you currently have sql express installed in "trusted mode".

If I were to then try to connect to the database from outside your application (e.g. to query the data in excel, I would be required to enter the same username / password).

Joe Smith IX wrote:
they can use SQL Management Studio to access the database directly

I would question why any user would have SQL Management Studio installed on their machine, but with the above, they would need to specify the username and password.

You can specify multiple usernames and passwords - possibly one username only allows access to specific views and stored procedures, andother username has greater access to the data. Perhaps the more restricted username would be user for a "web" interface to your application and the less restricted is used for the gui.

The point being, that usernames don't need to map directly to "users as in people."

Joe Smith IX wrote:
As for Interbase and MySql, are their passwords strong enough or can be cracked by hacker as well?

It's not their password that is strong enough, it's your password that is strong enough. If you use the password "password" or "letmein" I dare say someone will hack it fairly quickly. The same goes for SQL Server.

Hope that's of use.

ChrisB

Joe Smith IX6-Oct-06 9:27

6-Oct-06 9:27

Are you saying that SQL Express can be set/installed so that it will prompt for a username/password, regardless of what Windows login it's running on? Then it's exactly the solution what I am looking for.

However, after reading the Documentation on the installation using "Mixed Mode," and applying it (uninstall and re-install it), it didn't work. I think the "sa" login is just an alternative from Windows login, because I can now login to SQL Management Studio using sa/password under SQL authentication AS WELL AS login using windows authentication with no password (under admin login).

Am I missing something here? Thanks.

Chris Buckett7-Oct-06 7:17

Chris Buckett

7-Oct-06 7:17

Joe Smith IX wrote:
Am I missing something here? Thanks.

Only a little bit. You are just connecting to the database engine, not any particular database itself.

Look in the "Security\Logins" tab - this lets you see the users that sql server knows about. in a defauly install you have "Builtin\Administrators" this is the windows administrators group. You also have sa.

You can create a new user id in here, and give it a password (rather than associate it with a windows account / group). Also look at the user mappings tab.

Create a database, and go into the DatabaseName\Security\ section, this is where you grant access priveledges to individual user accounts. It may be that user1 can access db1 and db2, and user2 can access db2 and db3. They will all be able to connect to the database engine.

You can restrict access down to the table / view / stored procedure level (e.g. user1 may be able to access db1.table1, but not db1.table2).

And remember, it's the connection string in the application which provides the userID to the database engine. The engine will grant permission based upon the connection string.

I would suggest that if you're serious about preventing rogue / malicious access to you data, you invest in a good book on sql server security. (about 4 years ago I went on a 2 day course for sql2k, which was well worth it). Experiment. It has very robust security features that don't need to be linked with the windows login accoutn.

ChrisB

Joe Smith IX7-Oct-06 7:58

7-Oct-06 7:58

I think I finally understand a bit more about how these groups come into play. The easier solution for me now is just simply delete the builtin\administrators and builtin\user, leaving just sa and those ###groups. I believe this break the link to windows login all together, leaving only sa login as the possible SQL login.

Thanks a lot for all your help.

Jerry Hammond7-Oct-06 12:58

Jerry Hammond

7-Oct-06 12:58

Joe Smith IX wrote:
I believe this break the link to windows login all together, leaving only sa login as the possible SQL login.

Yikes! Bad, very bad, very very bad idea.

Without droning on about how bad an idea it is to leave your login to 'sa' I will direct you to a tut that shows you how to change it.

http://blogs.msdn.com/sqltips/archive/2005/08/27/457184.aspx[^]

http://blogs.msdn.com/sqltips/archive/2005/10/10/479268.aspx[^]

Good luck!

"We've all heard that a million monkeys banging on a million typewriters will eventually reproduce the entire works of Shakespeare. Now, thanks to the internet, we know this is not true." -- Professor Robert Silensky

Jerry Hammond5-Oct-06 9:08

Jerry Hammond

5-Oct-06 9:08

Joe Smith IX wrote:
At first, I am considering using SQL Express 2005, but since it links the security to Windows login, I can't prevent users from opening the database (since the program I am creating will be run under Admin login).

Incorrect. Take the time to RTFM. You'll be pleasently surprised at how powerful and full featured the **FREE** advanced feature SQL Server 2005 Express edition is.

Left Join & Group By

phimix5-Oct-06 4:40

5-Oct-06 4:40

Aaargghhh, I'm getting confused...

Here is my SQL statement:
SELECT DISTINCT(pb.id), name, file, alttext, votecount, showcount, rating, link, lastwin_year, lastwin_month, MAX(place) AS place FROM pb LEFT JOIN pv ON pb.id = pv.id WHERE pv.lastwin_month = $current_month AND pv.lastwin_year = $current_year GROUP BY pb.id, pv.lastwin_year, pv.lastwin_month ORDER BY rating DESC LIMIT 0, 3

The problem is that it returns 0 rows if the table pv is empty. How do I make it return 3 pb rows with highest ratings if pv is empty?

If there are 3+ rows in pv, the SQL statement works perfectly. The only fields that are unique in the two tables are pb.id and pv.id. pv.lastwin_year, pv.lastwin_month are not unique.

Thank you,

phi
When Will It Be? You Decide... OMG | :OMG:

mr_lasseter5-Oct-06 4:52

5-Oct-06 4:52

change left join to LEFT OUTER JOIN

Mike Lasseter

phimix5-Oct-06 5:17

5-Oct-06 5:17

A good idea, but it doesn't solve the problem Sigh | :sigh:

I still get 0 rows back.

phi
When Will It Be? You Decide... OMG | :OMG:

mr_lasseter5-Oct-06 5:34

5-Oct-06 5:34

pv.lastwin_month = $current_month AND pv.lastwin_year = $current_year

You must allow these two columns to be null as well example

(pv.lastwin_month = $current_month or pv.lastwin_month is null) and
(pv.lastwin_year = $current_year or pv.lastwin_year is null)

Mike Lasseter

phimix5-Oct-06 6:02

5-Oct-06 6:02

... that will retrieve some rows, but what about the rows where lastwin_month != $current_month and lastwin_month IS NOT NULL (and same for lastwin_year)? These rows are not retrieved Frown | :(

phi
When Will It Be? You Decide... OMG | :OMG:

mr_lasseter5-Oct-06 6:06

5-Oct-06 6:06

If you care about rows that != a value, why are you specifing them in the where clause?

Mike Lasseter

phimix5-Oct-06 7:01

Re: Left Join & Group By [modified]

5-Oct-06 7:01

What I want are the 3 rows in the pb table with the highest rating.

If these rows have a match (id) in the pv table, I want a row of pv information for each pb row as well. The information in pv is only relevant if lastwin_year and lastwin_month match the $current_year and $current_month parameters. No information should be retrieved from pv if no matching lastwin_year/lastwin_month can be found. Place should be as low as possible.

For example:

pb
---
id = 1, rating 56
id = 2, rating 63
id = 3, rating 42
id = 4, rating 70

pv
---
id = 2, lastwin_year 2006, lastwin_month 10, place 3
id = 2, lastwin_year 2006, lastwin_month 10, place 1
id = 3, lastwin_year 2006, lastwin_month 7, place 3
id = 4, lastwin_year 2005, lastwin_month 8, place 3
id = 4, lastwin_year 2005, lastwin_month 9, place 3

desired result ($current_year = 2006, $current_month = 10)
---
id = 4, rating 70, NULL
id = 2, rating 63, lastwin_year 2006, lastwin_month 10, place 1
id = 1, rating 56, NULL

phi
When Will It Be? You Decide... OMG | :OMG:

mr_lasseter5-Oct-06 7:44

5-Oct-06 7:44

I think this is what you are trying to do

select top 3 *
from pb left outer join
(select * from pv where lastwin_year = year and lastwin_month = month) b on pb.id = b.id
order by value

-- modified at 14:40 Thursday 5th October, 2006

Mike Lasseter

phimix5-Oct-06 19:34

5-Oct-06 19:34

Brilliant! Exactely what I needed to get the right results back.

Thank you Big Grin | :-D

phi
When Will It Be? You Decide... OMG | :OMG:

Record 'Deletion'

Dayekh5-Oct-06 3:56

Dayekh

5-Oct-06 3:56

Hi,

I currently have a stored procedure which is able to delete a record of my choice..

Instead of having the RECORD deleted, I would like the RECORD to disappear from the Datagrid, and have a boolean value of the 'Deleted' Field in that RECORD be set to 1...

Is this implemented in the Stored Procedure? Any ideas would be appreciated..

Re: Record 'Deletion'

mr_lasseter5-Oct-06 5:44