Database

Re: how to select last row on second CTE

29-Sep-14 5:26

If he is using HAL then he should have asked for HAL9000 compatible SQL Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Jörgen Andersson29-Sep-14 5:50

Jörgen Andersson

29-Sep-14 5:50

"I'm sorry, Eddy. I'm afraid I can't do that."

Wrong is evil and must be defeated. - Jeff Ello[^]

MS Server - Lock a record

hansoctantan26-Sep-14 3:48

hansoctantan

26-Sep-14 3:48

Hello Experts,

I have a table ID, Lock and other fields. I have a VB.NET program that gets the TOP 1 record that is not lock and lock that record to be keyed/entry. Multiple users will use this program, is it possible that more than one user can access the same record in this process? Its possible users will request a record at the same time.

Thanks a lot.

Revision
Table
ID | Lock |
------------
1 | False |
2 | False |
3 | False |

Lets say that 2 computer are using same program connecting to the same server database
Client:1 - Request Time: 10:30:00 will get the ID 1, ID 1 will lock(update to true)
Client:2 - Request Time: 10:30:05 will get the ID 2, ID 2 will lock(update to true) - cause ID 1 is lock

This is my question. same request
Client:1 & 2 - Request Time: 10:30:00
- Query is (SELECT TOP 1 * FROM Table WHERE Lock = False)
- Put ID to a variable
- In the above run time, Is the 2 client will get the ID 1 or the this query will stack on SQL Server will finish one query first before executing another query?

- Be who you are and say what you feel because those who mind don't matter and those who matter don't mind.
- Most of the problem in your life are due to two reasons: you act without thinking, or think without acting

modified 29-Sep-14 9:34am.

ZurdoDev26-Sep-14 4:22

ZurdoDev

26-Sep-14 4:22

I don't understand your question. How are you locking the record?

There are only 10 types of people in the world, those who understand binary and those who don't.

PIEBALDconsult26-Sep-14 4:24

PIEBALDconsult

26-Sep-14 4:24

I think you will need to write your own locking mechanism. No big deal.

Eddy Vluggen26-Sep-14 6:15

26-Sep-14 6:15

hansoctantan wrote:
is it possible that more than one user can access the same record in this
process?

Probably. Does not mean that it will be a problem.

There's a lot of stuff written on locking in SQL, with topics like lost updates[^] and dirty reads (no, not those magazines). You can add various hints[^] to the query. MSDN has dedicated a section[^] to consistency and concurrency.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

jschell26-Sep-14 10:44

jschell

26-Sep-14 10:44

What business case do you have that requires you to lock it?

Wendelius30-Sep-14 8:28

Protect my sql server database

30-Sep-14 8:28

If I understood your question correctly, you're wondering if session 1 can read the data that session 2 is locking. If this is the concern then the answer in many cases is no.

Consider the following scenario:

Session 1                                       Session 2
---------------------------------------------   -----------------------------------------------------
Select record A, field named SomeValue is 1

                                                Select record A, field named SomeValue is 1

Begin transaction

Update record A, 
   field named SomeValue is set to 2, 
   an eXclusive lock is taken

                                                Select record A, record is locked, session 2 waits

Some other modifications

                                                Session 2 still waits

Commit the transaction, lock is freed

                                                Session 2 now gets the answer, 
                                                   field named SomeValue is 2 
                                                   (the value session 1 updated)

Now the actual sequence varies depending if auto-commit is on and so on. Also you should know that if row versioning is in effect, then the behaviour is far different. For more information, read Data versioning in SQL Server using row versions[^]

dilkonika23-Sep-14 15:44

23-Sep-14 15:44

Hello !

I have created an application that use a sql server database.

But the problem is that a user can open sql server management studio and can do anything that I don't like , for example :

1) Can modify values inside tables.

2) can delete records

3) Can see and can get my database structure.....

......

How can I protect my database , so that users can modify this database only inside my application , and can do anything outside my application ? I don't know does exist a way to protect my database with a password or.... ??? for example when I was using Access for database , i have protected Access files with a password , and after only my application can open this database .
Is possible to include a protection in the sql server database file ( so even the file is imported to another computer to remain protected ?

I have a specific situation :

My application is designated to manage several "office".

In my application folder , i have a empty backup file ( offline) of my database called "Model". And inside my application a user can create "A new office " and when he do this the application restore a copy of "Model" inside sql server with a specific name (for example "Office1". Another time the user can create another "Office" using again a copy of "Model"

So inside Sql server may be different databases ( all of them with the same structure ) like "Model"

So in this situation how can i Protect my databases and the "Model" .

Because i think i should apply a Protection" inside the offline backup file "Model" ( if it's possible , all the databases that have created from "Model" will have the protection ) or ??????

What should i do in this case ?

Thank you !

modified 23-Sep-14 22:23pm.

Mycroft Holmes23-Sep-14 16:57

Mycroft Holmes

23-Sep-14 16:57

If your users have SA rights using windows authentication then you cannot stop them.

However you can restrict user access by implementing a reasonable security/password profile within SQL Server.

I hate to just dump a link but the subject is way too large for a forum discussion, do some research into SQL Server Security[^]

Never underestimate the power of human stupidity
RAH

http://blogs.msdn.com/b/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx[^]
http://sql-articles.com/articles/troubleshooting/how-to-recover-sa-password-when-you-forget-it-sql-2005/[^]

Richard Deeming24-Sep-14 1:27

Richard Deeming

24-Sep-14 1:27

Mycroft Holmes wrote:
If your users have SA rights using windows authentication then you cannot stop them.

If users have local administrator rights in Windows, but no rights in SQL, you still can't stop them. Smile | :)

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Eddy Vluggen23-Sep-14 21:10

23-Sep-14 21:10

dilkonika wrote:
What should i do in this case ?

Consider that they can open Explorer and do things you don't like. Or open the physical PC and do things.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

dilkonika24-Sep-14 3:13

24-Sep-14 3:13

is there a way to protect the mdf file ?

Eddy Vluggen24-Sep-14 6:21

24-Sep-14 6:21

Not really. The local admin has all the rights on the machine. The person that owns the server, owns the databases, and the data.

Buy a good server, install it SQL Server, put it in a large box, lock it, and hand that to the customer. That way you would be the local admin.

Or host the server from your place, and have them connect to your "SQL Cloud".

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

dilkonika24-Sep-14 13:30

24-Sep-14 13:30

if you have read my case , what can I do with "Model" file that is offline ? Someone can get and restore to another PC.
I read somewhere about encrypting the database.
Is this true and how can be done.Can this be a help in my situation ?

Eddy Vluggen25-Sep-14 1:53

25-Sep-14 1:53

You can encrypt the sprocs, and/or the data. Both will not protect your database-schema.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Corporal Agarn24-Sep-14 4:33

Corporal Agarn

24-Sep-14 4:33

You can setup the system to only be accessed through stored procedures that use "execute as".

Then just give the permissions that they need to execute the stored procedures. This may mean you need to create a role then grant execute.

dilkonika24-Sep-14 6:18

24-Sep-14 6:18

But what about the "Model" file that is offline ? Someone can get and restore to another PC.
I read somewhere about encrupting the database.
Is this true and how can be done.Can this be a help in my situation ?

data modeling guy29-Sep-14 6:58

data modeling guy

29-Sep-14 6:58

Dilkonika,

One approach is to NOT give users direct access to the machine where database system is running.

Have a middle tier server application(eg: tomcat) hiding your database from end users. Users may connect to middle tier server and invoke services. You may implement authentication using some standard framework within middle tier server application.

Best of luck!

Wendelius30-Sep-14 8:59

SQL Server : Multiple relationship for the same field

30-Sep-14 8:59

If I understood your question correctly, you're worried about several, different things. As Mycroft said, the topic is way too large to discuss extensively in a post. However, few things I'd like to point out:

Use proper security definitions on the server file system. No-one but admins should be let to operate with the files. Sql Server users don't need access to files
See: http://msdn.microsoft.com/en-us/magazine/cc982153.aspx[^]
Use either Sql Server or Windows authenticated logins along with proper database roles to limit the statements
See: http://msdn.microsoft.com/en-us/library/ms189121.aspx[^]
and http://msdn.microsoft.com/en-us/library/ms187965.aspx[^]
Use different logins for admins
If needed, encrypt the files
See: http://msdn.microsoft.com/en-us/library/bb934049.aspx[^]

dilkonika23-Sep-14 7:29

Re: SQL Server : Multiple relationship for the same field

23-Sep-14 7:29

Hello !

I have this situation :

Table : Article

Id

name

category

.........................

Table : Services

id

name

nr

value

-------------------------

Table : Sell_item

Id

item_id

quantity

price

value

----------------

Now the problem is this : The field Item_id on the sell_item table can be the id for an Article or a Service. So I need to create a double relationship for this field one with Article table an one with Service table.

Is this possible ?

If not , or if I'm wrong how can I solve this situation ?

Thank you.

Wendelius23-Sep-14 8:04

Re: SQL Server : Multiple relationship for the same field

23-Sep-14 8:04

If I understood your question correctly you would use the same field (item_id) for two separate purposes. If that's correct, in my opinion you have something wrong with the database model.

Concerning your example I'd suggest something like:

Product
-------
- Id
- name
- category
- IsService
- nr
- value

Sell_item
---------
- Id
- product (former item_id), foreign key to Product
- quantity
- price
- value

The product table would hold all sell able items and the fields are a combination from your both former tables.

Also I wouldn't store the value (at least not as a normal column) since it can always be calculated from quantity times price.

dilkonika23-Sep-14 8:14

Re: SQL Server : Multiple relationship for the same field

23-Sep-14 8:14

ok , but as I can see you have merged my 2 tables. Me to I have thinked this , but the problem is that in my post I have included only some of the fields from 2 tables. In reality these tables have 15 and 13 fileds , and do you think is a good choice to have a table with 28 fields where for each record only 15 or 13 fields will have the data and others are just unnecessary ?

Can be COALESCE a help in this situation ?

Wendelius23-Sep-14 8:33