|
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? 
Thanks in advance for any input, it will be greatly appreciated.
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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.
"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
|
|
|
|
|
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,
|
|
|
|
|
change left join to LEFT OUTER JOIN
Mike Lasseter
|
|
|
|
|
A good idea, but it doesn't solve the problem 
I still get 0 rows back.
|
|
|
|
|
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
|
|
|
|
|
... 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 
|
|
|
|
|
If you care about rows that != a value, why are you specifing them in the where clause?
Mike Lasseter
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
Brilliant! Exactely what I needed to get the right results back.
Thank you 
|
|
|
|
|
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..
|
|
|
|
|
This is a very common approach. There should be no problems implementing this into a stored procedure (except you will have to remove the item from the datagrid in the code).
Mike Lasseter
|
|
|
|
|
Thanks for the reply. Appreciate it. I'll go figure it out.
Take care.
|
|
|
|
|
I would Imagine that you are populating your Datagrid in a method. If you do not have a separate method for this I would highly recommend it. When the user activates the UI control to delete the record from the datagrid I would also call the method that populates your datagrid. If you call this method after you delete the DB record your Datagrid will automatically re-populate with out the record.
Hope this helps.
|
|
|
|
|
I have the below query but would like to use a wild card value to do the search.
I need to pass a value like "on" and find, Jason or Preston. It would be nick if it is also not case sensitive....
This value is coming from VB.Net App and this is messing with me...
My Access (SQL) statement is:
SELECT *
FROM MainTB
WHERE myCustomer Like [@myVar];
But the LIKE needs to be [%myVar%] I think.
Feedback welcome please......
When people make you see red, be thankful your not colour blind.
|
|
|
|
|
SELECT *<br />
FROM MainTB<br />
WHERE myCustomer Like ('%myVar%');
|
|
|
|
|
Running this query from access it does not ask me for an input it is looking for a user of %myVar%
As it is in the ' ' string quotes... I have already found this and tryed it...
Any other thoughts.....
?
When people make you see red, be thankful your not colour blind.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
