Limiting table access using stored procedure.

[acw]

On a SQL Server 2000 db I would like to setup a stored procedure that
accesses couple tables and runs the extended stored procedure
xp..cmdshell. The goal is to grant users with limited privileges the
right to run the stored procedure but not the rights to directly
access either the referenced tables or the extended stored procedure.

TIA!

[JT]

Re: Limiting table access using stored procedure.

There are three reasons why this probably won't be your solution:
1. I'm working with SQL Server 2005.
2. I haven't finished getting it to work.
3. Because the data access object I use opens and closes a DB
connection for each query it performs, it looks like I can't use the
Application Role approach (I don't know if that exists in 2000).

What I'm doing is creating a role that has restricted rights,
including removing access to specific tables with the intention that
no one can just execute actions on those tables, even in Query
Analyzer. All the users that will have access to the database will be
given rights through this role. I'll create stored procedures that
receive a password as a parameter. I'll store that password,
encrypted, in my application and pass it into the stored procedures so
that only the application can perform those actions when the stored
procedure validates the password. The role that I created will also
prevent access to the stored procedure for viewing and modification
(if that's necessary), but will allow executing it. Inside the stored
procedure, I'll need to perform an "Execute As" and specify a
"principal" that has rights to the table and most likely perform a
"Revert" when the procedure is done. Like I said, I haven't completed
this process yet, so there are probably holes in it. You can look at
the thread that got me going in this direction by going to
http://groups.google.com/group/micro...86aa731d8e168d,
which also has a link to using the application role (may not be
availale in 2000).

Reasons why I think the Application Role approach is more desirable:
1. The application role enforces a complex password.
2. There's a built-in procedure called sp_setapprole that takes
parameters for the application role, the application role password,
encryption type (ODBC or none), a boolean for allowing creating a
cookie for maintaining the maintaining the increased rights across
connections (I wasn't able to get that to work), and a cookie. This
ostensibly handles the setting and resetting of rights by causing all
actions performed during that connection session to be performed by
the application role.

So my approach will just be a different way of performing the same
thing and performing the rights adjustment right in each stored
procedure, allowing me to continue closing the connection after each
action. I look forward to having people help me improve my approach
or figure out how to make the Application Role approach work for me.
Let us know what ends up working for you.

On May 9, 11:45 am, acw <acwom...@yahoo .comwrote:

On a SQL Server 2000 db I would like to setup a stored procedure that
accesses couple tables and runs the extended stored procedure
xp..cmdshell. The goal is to grant users with limited privileges the
right to run the stored procedure but not the rights to directly
access either the referenced tables or the extended stored procedure.
>
TIA!

[Dan Guzman]

Re: Limiting table access using stored procedure.

On a SQL Server 2000 db I would like to setup a stored procedure that

accesses couple tables and runs the extended stored procedure
xp..cmdshell. The goal is to grant users with limited privileges the
right to run the stored procedure but not the rights to directly
access either the referenced tables or the extended stored procedure.

If you tables are owned by 'dbo', you can accomplish this with
cross-database chaining as follows.

1) ensure your user database is owned by 'sa': EXEC sp_changedbowne r 'sa'

2) enable the 'db chaining': EXEC sp_dboption 'MyDatabase', 'db chaining',
true

3) Configure the SQL Agent proxy account by unchecking the 'only users with
sysadmin ...' checkbox in Enterprise Manager under SQL Server
Agent-->Properties-->Job System. Make sure the proxy account has the
Windows permissions needed by the task xp_cmdshell performs.

Importantly, you should enable cross-database chaining in an sa-owned
database when only sysadmin role members can create dbo-owned objects. See
the Books Online for more information.


--
Hope this helps.

Dan Guzman
SQL Server MVP

http://weblogs.sqlteam.com/dang/

"acw" <acwomble@yahoo .comwrote in message
news:a41dacbd-df89-434b-83ed-1061e0380314@x3 5g2000hsb.googl egroups.com...

On a SQL Server 2000 db I would like to setup a stored procedure that
accesses couple tables and runs the extended stored procedure
xp..cmdshell. The goal is to grant users with limited privileges the
right to run the stored procedure but not the rights to directly
access either the referenced tables or the extended stored procedure.
>