Showing posts with label backend. Show all posts
Showing posts with label backend. Show all posts

Friday, March 23, 2012

I want to restrict the my clients to access database through EnterPrise Manger Or Quer ana

Hello All
I am using SQL server 2000 as the backend of my application but don't
want my clients tobe able to view or edit the database tables, stored
procedures , view etc using enterprise manager or query analyser (or
similar tools)How can this be done ?
I searched a lot for this but unable to get the correct answer is
there anybody to give full solution regarding this.
Please relpy me as early as possibleCheck 'Application roles' topic in BOL.

Igor

"Kavita" <kavitajt@.yahoo.com> wrote in message
news:b1112113.0403020411.7eb19264@.posting.google.c om...
> Hello All
> I am using SQL server 2000 as the backend of my application but don't
> want my clients tobe able to view or edit the database tables, stored
> procedures , view etc using enterprise manager or query analyser (or
> similar tools)How can this be done ?
> I searched a lot for this but unable to get the correct answer is
> there anybody to give full solution regarding this.
> Please relpy me as early as possible|||"Kavita" <kavitajt@.yahoo.com> wrote in message
news:b1112113.0403020411.7eb19264@.posting.google.c om...
> Hello All
> I am using SQL server 2000 as the backend of my application but don't
> want my clients tobe able to view or edit the database tables, stored
> procedures , view etc using enterprise manager or query analyser (or
> similar tools)How can this be done ?
> I searched a lot for this but unable to get the correct answer is
> there anybody to give full solution regarding this.
> Please relpy me as early as possible

Here are several things you can look at:

1. Do not give the users permission to create procedures, views etc.

2. Use application roles (see sp_setapprole in Books Online)

3. Only allow access to data through stored procedures, and only grant
EXECUTE permissions on the procedures instead of granting permissions on the
tables (this may not be 100% possible if you require dynamic SQL, however)

4. Code your application to set the application name when it connects, then
use APP_NAME() in stored procedures to check the user is using an
'authorized' application (this is easy to fake, though, so it will only stop
an accidental or 'casual' attempt to connect)

5. Use a middle tier to authenticate users and manage connections instead of
allowing them to connect directly to the database server

Simon

Wednesday, March 21, 2012

I Want to Lock One Record

Please help! I have a VB application used by multiple users. MS SQL 2000 backend.

When a user opens a record for editing, it want to mark the record as locked so that if other users try to open this record, it will not be available.

Currently, I'm using a programmatic method that basically sets a field 'lock" to 1 if the record is in use and 0 if not in use. But this has the side affact of locking the record if the user reboots their workstation. In addition, I don't believe that this is the best method and approach to achieve my goal.

Thanks very much for your help in advance. All ideas are welcome.

crownlogI think u can make use of SQL Server's locking mechanism.
example:
U can put a special feild as a semphemore.
when a user opens a record,the program begin a transaction and update the field to 1 immdiately.When the user press save button in front-program,the transaction will commit.When the locker workstation reboots,the transaction will auto-released right now,and the other waiting program will get the control immdiately.|||enhydraboy,

Thanks very much. Might you have more details or an example in using MSSQL2K locking mech.

Regards
crownlog|||Nobody?

This is a bad design. It's the path that leads to...the dark side..

You should desgin the table so that it has an update datetime column.

Every time a row is modified, update that column.

If you need to know if the row was modified, check that column again and compare the datetimes...if they are the same, good to go...if they're different, show the user the changes...you could even build something to allow them to merge the 2...then apply the update...

The my own opinion (MOO)|||crownlog ... listen to Brett's advice. What if your user, after locking the row, goes to lunch or home for the weekend or Aruba for the week or Australia after embezzling $1M? best to not lock the row until ready to update!|||should read...

"best to let sql server manage ALL of your locking"|||Actually, the issue is that I need to open a record and keep it opened the entire time it is being edited by user1 - could be minutes. I need to keep all the other users from trying to open it while it is being edited by User1. I only need that record locked during the edit.

What's the best way to do it?|||Why is a bad design?
Is A timestamp field good?
What I can confirm is that is not what the crownlog requests.|||Actually, the issue is that I need to open a record and keep it opened the entire time it is being edited by user1 - could be minutes. I need to keep all the other users from trying to open it while it is being edited by User1. I only need that record locked during the edit.

What's the best way to do it?

Let me tell my solution in detail,but u must suffer my bad english first.
Everybody knows that RDBMS uses locking to keep consistency of updating and reading during cocurrent sessions.
So when we program in VB/VC/Delphi,we needn't care anything because sqlserver engine will do it better.

crownlog's case is that he want find a solution that will synchronize transaction between different clients(according to per application).
So we must establish a synchronization rules,we need a center-locking control.
There are many solution u can choose:
1 Unique Application server,u must program by yourself
2 Let SQL Server became a center-locking control server,because database locking control is very perfect.What u need do is understanding it and making use of it.

About "best to let sql server manage ALL of your locking",I haven't read the book.But I know if I unstand sql server well,I am not fraid to using it and I well let sql server serve for me saftly.|||crownlog ... listen to Brett's advice. What if your user, after locking the row, goes to lunch or home for the weekend or Aruba for the week or Australia after embezzling $1M? best to not lock the row until ready to update!
It's the problem crownlog must think it over.
How to realize his requests in UI function.
Using locking or using timestamp,U will not avoid the problem that when one body go home for sleeping without quit his appliction,the others must wait he come back to finish his work.
Maybe u must need administrator to force one to go out.So It's not the problem of techniques.|||Maybe a combination of the two. A binary field to determine whether the record is being currently used, and a time limit of say 10 minutes, so another field with a timestamp. Assuming all the systems are using a level 2 time clock server. You could have a script running in the background to clear out any locked fields and set it to 0 if the time has lapsed comparative to the timestamp. Also setting a timer on the page of your application, and maybe a timestamp refresh button. It would force the user to pay attention to what they are doing. If I'm not getting my idea across, just drop me an e-mail, and I'll try to get some sort of flowchart together to explain it better.