allow user to only read data including via store procedure

Hello,
I need your advice, I want to create a user that should not be able to cause
any data change to the database. How do I do that?
I have tried to put these on the user:
- public: ON
- Db_denydatawriter: ON
- Db_datareader: ON
With those settings, the user cannot execute any store procedures even
though the store procs only read data. If I add
- Db_owner: ON
...then the user will be able to excute store procedures that also modify
data!
I know that I can go to individual database and overwrite the setting for
each store proc but store procs are changed often, some of them are hugh -
it's not easy to control which of them don't modify data.
Have you run into this problem before? Please help!! Thanks!!You have to be a bit more dilligent in your security. There is no magic
wand that you can use to do this for you. Create a group, let say
read_only_users. Then simply grant execute rights on all procedures that
only read data. Then add the user to this group (only if you want to keep
them to only read access via procedures.)
Louis
--
----
--
Louis Davidson (drsql@.hotmail.com)
Compass Technology Management
Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)
"Zeng" <zzy@.nonospam.com> wrote in message
news:eUHYEeR4DHA.2448@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I need your advice, I want to create a user that should not be able to
cause
> any data change to the database. How do I do that?
> I have tried to put these on the user:
> - public: ON
> - Db_denydatawriter: ON
> - Db_datareader: ON
> With those settings, the user cannot execute any store procedures even
> though the store procs only read data. If I add
> - Db_owner: ON
> ...then the user will be able to excute store procedures that also modify
> data!
> I know that I can go to individual database and overwrite the setting for
> each store proc but store procs are changed often, some of them are hugh -
> it's not easy to control which of them don't modify data.
> Have you run into this problem before? Please help!! Thanks!!
>|||thanks for the response. Keeping track of store procedures' write and read
operations is not "simple" task, therefore separating them out to grant the
execute rights appropriately is not simple. It's very logical to design a
system that guard datareading and datawriting at the data level (to create
only one gate to guard) - if magic is needed for that - then everybody would
have to go test every other way that can modify or read data even after they
select Db_denydatawriter or Db_denydatareader option. For example, from
what I understand user functions are recently introduced in Sql Server, it's
buggy (but that's not the point), and because it can read and write data,
does that mean I have to go through each function to determine if they are
read-only and keeping track of them too? What if a store proc or a user
function starts out as a read-only and later got changed to include an
update and user forget to move it from one security group to another
group....
It's hard to believe....what I learn here...I'm relatively a newbie with
Sql Server, but the more I learn about it especially when I start doing
replication, the more it appears to me as a dinosaur
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:%23VGlOXS4DHA.1804@.TK2MSFTNGP12.phx.gbl...
> You have to be a bit more dilligent in your security. There is no magic
> wand that you can use to do this for you. Create a group, let say
> read_only_users. Then simply grant execute rights on all procedures that
> only read data. Then add the user to this group (only if you want to keep
> them to only read access via procedures.)
> Louis
> --
> ----
--
> --
> Louis Davidson (drsql@.hotmail.com)
> Compass Technology Management
> Pro SQL Server 2000 Database Design
> http://www.apress.com/book/bookDisplay.html?bID=266
> Note: Please reply to the newsgroups only unless you are
> interested in consulting services. All other replies will be ignored :)
> "Zeng" <zzy@.nonospam.com> wrote in message
> news:eUHYEeR4DHA.2448@.TK2MSFTNGP09.phx.gbl...
> > Hello,
> >
> > I need your advice, I want to create a user that should not be able to
> cause
> > any data change to the database. How do I do that?
> >
> > I have tried to put these on the user:
> > - public: ON
> > - Db_denydatawriter: ON
> > - Db_datareader: ON
> >
> > With those settings, the user cannot execute any store procedures even
> > though the store procs only read data. If I add
> > - Db_owner: ON
> > ...then the user will be able to excute store procedures that also
modify
> > data!
> >
> > I know that I can go to individual database and overwrite the setting
for
> > each store proc but store procs are changed often, some of them are
hugh -
> > it's not easy to control which of them don't modify data.
> >
> > Have you run into this problem before? Please help!! Thanks!!
> >
> >
>|||One thing to factor is that the ability for a user to be able to perform the
operations inside a stored procedure without having direct access to the
underlying objects is actually a security feature. This way, you can lock
down direct access to the objects and only grant the users EXEC permissions
to the stored procedures. I know it doesn't help you in your current
situation, I just want to give a perspective of the design. To be honest, I
think that this is the first case where I've seen this particular request,
but I'm sure that it is sensible in your environment. I can't come up with a
way to "mask" the modification permissions for the modifications that a
stored procedure performs when you grant the user EXEC permissions to the
stored procedure. You could roll your own, of course; have some code in the
proc which checks against a permissions table, but that might not be
feasible to you.
You might want to post this to sqlwish@.microsoft.com.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Zeng" <zzy@.nonospam.com> wrote in message
news:OsHN6CT4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> thanks for the response. Keeping track of store procedures' write and
read
> operations is not "simple" task, therefore separating them out to grant
the
> execute rights appropriately is not simple. It's very logical to design a
> system that guard datareading and datawriting at the data level (to create
> only one gate to guard) - if magic is needed for that - then everybody
would
> have to go test every other way that can modify or read data even after
they
> select Db_denydatawriter or Db_denydatareader option. For example, from
> what I understand user functions are recently introduced in Sql Server,
it's
> buggy (but that's not the point), and because it can read and write data,
> does that mean I have to go through each function to determine if they are
> read-only and keeping track of them too? What if a store proc or a user
> function starts out as a read-only and later got changed to include an
> update and user forget to move it from one security group to another
> group....
> It's hard to believe....what I learn here...I'm relatively a newbie with
> Sql Server, but the more I learn about it especially when I start doing
> replication, the more it appears to me as a dinosaur
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:%23VGlOXS4DHA.1804@.TK2MSFTNGP12.phx.gbl...
> > You have to be a bit more dilligent in your security. There is no magic
> > wand that you can use to do this for you. Create a group, let say
> > read_only_users. Then simply grant execute rights on all procedures
that
> > only read data. Then add the user to this group (only if you want to
keep
> > them to only read access via procedures.)
> >
> > Louis
> >
> > --
> ----
> --
> > --
> > Louis Davidson (drsql@.hotmail.com)
> > Compass Technology Management
> >
> > Pro SQL Server 2000 Database Design
> > http://www.apress.com/book/bookDisplay.html?bID=266
> >
> > Note: Please reply to the newsgroups only unless you are
> > interested in consulting services. All other replies will be ignored :)
> >
> > "Zeng" <zzy@.nonospam.com> wrote in message
> > news:eUHYEeR4DHA.2448@.TK2MSFTNGP09.phx.gbl...
> > > Hello,
> > >
> > > I need your advice, I want to create a user that should not be able to
> > cause
> > > any data change to the database. How do I do that?
> > >
> > > I have tried to put these on the user:
> > > - public: ON
> > > - Db_denydatawriter: ON
> > > - Db_datareader: ON
> > >
> > > With those settings, the user cannot execute any store procedures even
> > > though the store procs only read data. If I add
> > > - Db_owner: ON
> > > ...then the user will be able to excute store procedures that also
> modify
> > > data!
> > >
> > > I know that I can go to individual database and overwrite the setting
> for
> > > each store proc but store procs are changed often, some of them are
> hugh -
> > > it's not easy to control which of them don't modify data.
> > >
> > > Have you run into this problem before? Please help!! Thanks!!
> > >
> > >
> >
> >
>|||Hi,
Can you please remove the "ON" from db_denydatawriter role and try executing
the procedure.
Thanks
Hari
MCDBA
"Zeng" <zzy@.nonospam.com> wrote in message
news:eUHYEeR4DHA.2448@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I need your advice, I want to create a user that should not be able to
cause
> any data change to the database. How do I do that?
> I have tried to put these on the user:
> - public: ON
> - Db_denydatawriter: ON
> - Db_datareader: ON
> With those settings, the user cannot execute any store procedures even
> though the store procs only read data. If I add
> - Db_owner: ON
> ...then the user will be able to excute store procedures that also modify
> data!
> I know that I can go to individual database and overwrite the setting for
> each store proc but store procs are changed often, some of them are hugh -
> it's not easy to control which of them don't modify data.
> Have you run into this problem before? Please help!! Thanks!!
>|||give the user exec permissions on the stored procedures he needs to be able
to run.
give the user the datareader permissions also, so the user will be able to
read from all objects and have execute permissions on the procedures you
want
Regards,
Dandy Weyn
MCSE, MCSA, MCDBA, MCT
www.dandyman.net
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O1jt4ol4DHA.3416@.tk2msftngp13.phx.gbl...
> Hi,
> Can you please remove the "ON" from db_denydatawriter role and try
executing
> the procedure.
> Thanks
> Hari
> MCDBA
>
> "Zeng" <zzy@.nonospam.com> wrote in message
> news:eUHYEeR4DHA.2448@.TK2MSFTNGP09.phx.gbl...
> > Hello,
> >
> > I need your advice, I want to create a user that should not be able to
> cause
> > any data change to the database. How do I do that?
> >
> > I have tried to put these on the user:
> > - public: ON
> > - Db_denydatawriter: ON
> > - Db_datareader: ON
> >
> > With those settings, the user cannot execute any store procedures even
> > though the store procs only read data. If I add
> > - Db_owner: ON
> > ...then the user will be able to excute store procedures that also
modify
> > data!
> >
> > I know that I can go to individual database and overwrite the setting
for
> > each store proc but store procs are changed often, some of them are
hugh -
> > it's not easy to control which of them don't modify data.
> >
> > Have you run into this problem before? Please help!! Thanks!!
> >
> >
>