|
Subject:
|
restricting database user access, stored procedrs
|
|
Posted By:
|
Oliver Dempsey
|
Post Date:
|
10/29/2004 7:44:07 AM
|
Hi folks,
Here's a hard one.
We are building a web application which provides a central administration facility for multiple websites. It is built with ASP and uses SQL Server as the database. The administration pages are stored on a seperate domain so it is safe and secure and can't be tampered with in any way. However, the front end of each website is stored on their own website and I am trying to find some way of restricting access within SQL Server to only those records that belong to the website that is accessing the database.
we know that if database users are created then Restricted Access on tables can be possible.
Also restricted access on a particular column for update and select is also possible.
But we are not sure about row level access.
For example :
"Select * from tablename where siteid ='1242121112122' "
Above query will return all records with siteid = 1242121112122
The problem is that if the website owner has FTP user access because the front end runs on their domain so they could make the following changes in above query
"Select * from tablename"
Then it will show all of the records i.e. for all sites.
This we can't stop them from doing this if they have access to the same table as everyone else.
But, if Restricted Access on tables can be possible, then can Restricted Access on stored procedures be possible as well and then we can have a different stored procedure for each site. stored procedure 1 would say , "show records in table x where siteid = 123456789101112", stored procedure 2 would say , "show records in table x where siteid = 123456789101113" If this is possible, would it be possible only to give access to execute the procedure but not to read the SQL code inside the procedure for that database user?
Can anyone help?
Regards
Oliver Dempsey
|
|
Reply By:
|
happygv
|
Reply Date:
|
10/30/2004 12:03:27 AM
|
I am not sure if you can restrict the users to select a certain set of rows. Instead I would say you can create VIEWs that would select the table based on just that SITEID. Give permission for the user to have access only on the view, not on the table. So that the user could access the table through view and not directly by any means.
Similarly, you can restrict the user to have just execute permission for any stored procs, but just an sp_helptext <stored_proc_Name> would show the code of the stored proc. I am not sure if you can restrict that too.
Hope that helps.
Cheers!
_________________________
- Vijay G
Strive for Perfection
|
|
Reply By:
|
Oliver Dempsey
|
Reply Date:
|
10/31/2004 9:33:50 AM
|
Hi Vijay,
thanks for your reply. can views accept parameters such as siteid or would you have to create a new view for each new site?
If new views had to be created for each site it would make the system less flexible.
Regards
Oliver
|
|
Reply By:
|
happygv
|
Reply Date:
|
11/1/2004 3:18:11 PM
|
Hi Oliver,
Views do not take parameters, but stored procedures accept parameters. In your case, Views will not be the right solution. Stored Procedures should be what you are looking for.
Revoke all permission for that db user on that database.Revoke all from testaccount Then create a stored proc that accepts parameter and code it the way you want and grant execute permission for that user on the procedure, not on the table.Grant Execute on PROC_NAME to testaccount Now, login as that user and trySelect * from ANY_TABLENAME_In_That_DB that results in error
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'TABLENAME', database 'DB_NAME', owner 'dbo'.
The testaccount user is now allowed to execute the PROC, which works fine, but cannot play around with data/table there. Still Sp_helptext PROC_NAME would show the code to the user. But that is harmless. That user can now do nothing with that.
Hope that helps.
Cheers!
_________________________
- Vijay G
Strive for Perfection
|
|
Reply By:
|
Oliver Dempsey
|
Reply Date:
|
11/2/2004 4:33:16 PM
|
Thanks happygv,
that makes sense alright
therefore, if we have a stored procedure that says (in english) allow [siteid_parameter] to add, edit or delete only records where siteid=[siteid_parameter], then we can allow access for that user only to those records
thanks a million!
Oliver
|
