Re: Security by proc

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 03/16/04 

Date: Tue, 16 Mar 2004 21:05:23 +0100

Yes, denying permissions (or not granting in the first place) on tables and
grating on procs that uses those tables is one of the major advantages of
using procs. 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Url Onz" <anonymous@discussions.microsoft.com> wrote in message
news:e3c401c40b8d$cee78470$a401280a@phx.gbl...
> I have a table from which I want my users to get
> information that is applicable to them but nothing else. I
> just tried this on my test server. I denied my test user
> all permissions to the table. I wrote a proc that has one
> parameter that will limit the data selected to the data
> for that person. Another parameter lets them select the
> date of the information. My front end will call the proc
> and they will enter one parameter to get the kind of data
> they want and the other parameter will be hard coded to
> give them permissions to only their data. I will give them
> explicit permissions to execute the proc.
> In my test my test user could not select from the table
> but he could run the proc and get his data. My fear is
> that is just too darn easy. Does this sound like it will
> work?
>

Relevant Pages

  • Security by proc
    ... My front end will call the proc ... give them permissions to only their data. ... In my test my test user could not select from the table ... that is just too darn easy. ...
    (microsoft.public.sqlserver.programming)
  • Re: user defined Role - HELP
    ... Are you using dynamic SQL within the proc? ... SQL Server MVP ... permissions on the underlying table. ...
    (microsoft.public.sqlserver.security)
  • Re: Security by proc
    ... What is "just too darn easy?" ... It sounds as though you have correctly set permissions on the base table as well as the stored procedure. ... My front end will call the proc ... > In my test my test user could not select from the table ...
    (microsoft.public.sqlserver.programming)
  • Re: sa privileges and roles
    ... permissions of that account. ... Another option is to create a dbo-owned user proc in an sa-owned user ... in your user database and grant execute permissions only on your user proc. ... SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Urgent: Permissions Problem with Dynamic SQL
    ... I have 2 databases residing on the same server and an ... having problems with permissions when using dynamic sql. ... CREATE PROC a2 AS ...
    (microsoft.public.sqlserver.security)