Permissions

All,
How can we grant the permissions to the user to SELECT from some view on one
database which is pulling the data from several tables on another database
but hide the structure of these tables for this user? The problem is the
following, if we don't grant the permission to SELECT from these tables then
the user can't run these views, but if the user can select from these tables
then he sees the table structure, that's what we wanted to avoid.
The view itself is encrypted, so it's safe to expose it to the user.
Any good ideas?
Just D.Might consider creating the view in the database where the tables exist and
then reference the view as database.owner.view Also, you might consider
using a stored procedure to encapsulate the SELECTs if more logic or
auditing is required. And TMK, I don't think SQLs built-in WITH ENCRYPTION
is completely secure.
HTH
Jerry
"Dmitri Shvetsov" <no@.spam.please> wrote in message
news:CL3dg.20313$XV5.19438@.fed1read10...
> All,
> How can we grant the permissions to the user to SELECT from some view on
> one database which is pulling the data from several tables on another
> database but hide the structure of these tables for this user? The problem
> is the following, if we don't grant the permission to SELECT from these
> tables then the user can't run these views, but if the user can select
> from these tables then he sees the table structure, that's what we wanted
> to avoid.
> The view itself is encrypted, so it's safe to expose it to the user.
> Any good ideas?
> Just D.
>|||If you're using SQL 2000 SP3 or newer, you can enable cross-database
ownership chaining. Check out BOL for implementing and security caveats.
HTH
Vern Rabe
"Jerry Spivey" wrote:

> Might consider creating the view in the database where the tables exist an
d
> then reference the view as database.owner.view Also, you might consider
> using a stored procedure to encapsulate the SELECTs if more logic or
> auditing is required. And TMK, I don't think SQLs built-in WITH ENCRYPTIO
N
> is completely secure.
> HTH
> Jerry
> "Dmitri Shvetsov" <no@.spam.please> wrote in message
> news:CL3dg.20313$XV5.19438@.fed1read10...
>
>|||Dima
Add the user to the "source database" and GRANT them SELECT permissions on
VIEW
"Dmitri Shvetsov" <no@.spam.please> wrote in message
news:CL3dg.20313$XV5.19438@.fed1read10...
> All,
> How can we grant the permissions to the user to SELECT from some view on
> one database which is pulling the data from several tables on another
> database but hide the structure of these tables for this user? The problem
> is the following, if we don't grant the permission to SELECT from these
> tables then the user can't run these views, but if the user can select
> from these tables then he sees the table structure, that's what we wanted
> to avoid.
> The view itself is encrypted, so it's safe to expose it to the user.
> Any good ideas?
> Just D.
>|||Uri,
Thanks for your answer but that doesn't work, the SQL engine barks when I
try to select anything from these views explaining that the user has no
permissions on the target DB or if even if it doesn't complaint then it
doesn't show anything, just column names and no data at all. So it doesn't
work. That was the very first idea that I tried. Adding the SP and granting
EXEC permissions to these SP is not very good because it's not flexible
enough, although we can add some arguments to these SPs to restrict the data
to avoid pulling the whole DB.
Btw, I found this article below after I posted my question to this
newsgroup.
http://www.databasejournal.com/feat...10894_2246271_2
Maybe this is the way to go, we'll try it tomorrow. It's a little bit
similar to what we were doing, but who knows, there are always some ways
ahead.
Granting the permissions per column for each table will kill me, I tried to
think about it but this is the worst way reserved for "just in case".
Just D.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:epcKo37fGHA.452@.TK2MSFTNGP02.phx.gbl...
> Dima
> Add the user to the "source database" and GRANT them SELECT permissions
> on VIEW
>|||Yes, have you read my post?
I said you need to add the user to the taget database and GRANT to them
SELECT PERMISSION on VIEW
ps . create a view on target database as well
"Just D" <no@.spam.please> wrote in message
news:97ddg.20579$XV5.8839@.fed1read10...
> Uri,
> Thanks for your answer but that doesn't work, the SQL engine barks when I
> try to select anything from these views explaining that the user has no
> permissions on the target DB or if even if it doesn't complaint then it
> doesn't show anything, just column names and no data at all. So it doesn't
> work. That was the very first idea that I tried. Adding the SP and
> granting EXEC permissions to these SP is not very good because it's not
> flexible enough, although we can add some arguments to these SPs to
> restrict the data to avoid pulling the whole DB.
> Btw, I found this article below after I posted my question to this
> newsgroup.
> http://www.databasejournal.com/feat...10894_2246271_2
> Maybe this is the way to go, we'll try it tomorrow. It's a little bit
> similar to what we were doing, but who knows, there are always some ways
> ahead.
> Granting the permissions per column for each table will kill me, I tried
> to think about it but this is the worst way reserved for "just in case".
> Just D.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:epcKo37fGHA.452@.TK2MSFTNGP02.phx.gbl...
>