I have setup replication from one SQL 2000 server to
another. All I am replicating is one single small DB.
The replication of the data seems to work fine. however,
permissions (both on public and a special user) are not
replicated.
I have been unable to find how/where to specify
replication op permissions. Any help on this will be
greatly appreciated.
Joe
use the post snapshot command, or you can always replicate it to all of your
subscribers by using
sp_addscriptexec.
What this proc will do it so replicate to and execute the script on all
subscriber.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Joe Bunker" <admin@.yearone.com> wrote in message
news:9bad01c478c4$b04cc1c0$a501280a@.phx.gbl...
> I have setup replication from one SQL 2000 server to
> another. All I am replicating is one single small DB.
> The replication of the data seems to work fine. however,
> permissions (both on public and a special user) are not
> replicated.
> I have been unable to find how/where to specify
> replication op permissions. Any help on this will be
> greatly appreciated.
> Joe
|||Hi Hilary,
I've had inconsistent results with sp_addscriptexec - running on some
subscribers but not others (no anonymous) - particularly if a subscriber was
not connected at the time the command was run.
Have you experienced this at all?
Cheers,
Mary
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:O5bzLYMeEHA.596@.TK2MSFTNGP11.phx.gbl...
> use the post snapshot command, or you can always replicate it to all of
your
> subscribers by using
> sp_addscriptexec.
> What this proc will do it so replicate to and execute the script on all
> subscriber.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Joe Bunker" <admin@.yearone.com> wrote in message
> news:9bad01c478c4$b04cc1c0$a501280a@.phx.gbl...
>
|||Hilary,
Do you have any more information on the post snapshot
command? And/or a sample permissions script to use with
either sp_addscriptexec or @.post_snapshot_script?
Thank you.
>--Original Message--
>use the post snapshot command, or you can always
replicate it to all of your
>subscribers by using
>sp_addscriptexec.
>What this proc will do it so replicate to and execute the
script on all[vbcol=seagreen]
>subscriber.
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Joe Bunker" <admin@.yearone.com> wrote in message
>news:9bad01c478c4$b04cc1c0$a501280a@.phx.gbl...
however,
>
>.
>
|||no, I have not. sp_addscriptexec is pooled in the distribution database and
it should be executed on ALL subscribers that are subscribed to the
publication.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Mary Bray" <reply@.tonewsgroup.com.NOSPAMPLEASE> wrote in message
news:OYlm2MVeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> Hi Hilary,
> I've had inconsistent results with sp_addscriptexec - running on some
> subscribers but not others (no anonymous) - particularly if a subscriber
was
> not connected at the time the command was run.
> Have you experienced this at all?
> Cheers,
> Mary
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:O5bzLYMeEHA.596@.TK2MSFTNGP11.phx.gbl...
> your
>
|||for a permissions script connect to your database using EM. Right click on
the publication database, and select all tasks, generate SQL Script. In the
general tab, select the objects whose permissions you want to replicate, and
in the options tab, select Script Object Permissions.
Then generate the script, and edit it to remove all object creation/drop
statements until all that is left is the Grant and Deny commands.
Then save this script and this is the script you should propagate to all
subscribers.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Joe Bunker" <anonymous@.discussions.microsoft.com> wrote in message
news:b78001c4797c$c1fe9890$a401280a@.phx.gbl...[vbcol=seagreen]
> Hilary,
> Do you have any more information on the post snapshot
> command? And/or a sample permissions script to use with
> either sp_addscriptexec or @.post_snapshot_script?
> Thank you.
> replicate it to all of your
> script on all
> however,
sql
Showing posts with label single. Show all posts
Showing posts with label single. Show all posts
Friday, March 23, 2012
Wednesday, March 7, 2012
Permission settings of Inventory database
I'm using a single database for inventory system. Inventory data of all
shops are stored in the same database with restricted permissions to their
own data. That means, users / programs can only access their own inventory
information.
But, this is problem, the entire database is required to be accessed during
internal processes and in stored procedures.
I wanna know if it is possible to have full-access permission on the
database for only inside stored procedures calls and keep restricting the
users from accessing other data.
LeonardHi,
You can implement "Application role" to implement the security strategy
mentioned. What you have to do is Create a Application role and
assign a password and give the required permission to the application role.
Steps:
1. Create a application role and set the password
2. Assign this application role to the database user
3. Enable this application role by giving the password inside your code (So
as users also will not know this password)
4. So after enabling this user will get the access inside application, But
they will get any prev. using query analyzer. This
will ensure that they cant do any Insert / update / delete using Query
analyzer or Enterprise manager.
Thanks
Hari
MCDBA
"Leonard Poon" <leonardpoon@.hotmail.com> wrote in message
news:ulnyZ1uEEHA.1988@.TK2MSFTNGP10.phx.gbl...
> I'm using a single database for inventory system. Inventory data of all
> shops are stored in the same database with restricted permissions to their
> own data. That means, users / programs can only access their own inventory
> information.
> But, this is problem, the entire database is required to be accessed
during
> internal processes and in stored procedures.
> I wanna know if it is possible to have full-access permission on the
> database for only inside stored procedures calls and keep restricting the
> users from accessing other data.
> Leonard
>|||Thanks, Hari,
I've already grouped users into application roles.
Be more specific, let me give an example:
First, we have 3 shops. Inventory records are kept separately by their
bookkeepers. The inventory of each shop can only be read/updated by its
bookkeeper, and shop manager, except for the upper management. The records
has to be kept as confidential as it could be. (may be exaggerated)
Suppose there is a user who is in Bookkeeper role. I granted SELECT,UPDATE
permissions to the tables in database for the role. After she logged in the
system, she can do anything that is permitted through the front-end program.
Let say, if she wants to browse inventory information, the program calls the
specific stored procedures/views which get data from her part of records. In
those stored procedures, we have to manually apply filters or 'WHERE'
clauses to queries.
Now, if she wants to update the inventory, the program will call the stored
proc as usual. But, this time those procedures may have to read entire
database in order to support the 'if-then-else' branches. Since the
permissions are already set for the user, there will be not enough
permission to finish the procedures.
How do I set the permission to resolve this? Or , did I misconceive the
whole thing from the very beginning.
Leonard
"Hari" <hari_prasad_k@.hotmail.com> glsD
:#7cnL$uEEHA.2308@.tk2msftngp13.phx.gbl...
> Hi,
> You can implement "Application role" to implement the security strategy
> mentioned. What you have to do is Create a Application role and
> assign a password and give the required permission to the application
role.
> Steps:
> 1. Create a application role and set the password
> 2. Assign this application role to the database user
> 3. Enable this application role by giving the password inside your code
(So
> as users also will not know this password)
> 4. So after enabling this user will get the access inside application, But
> they will get any prev. using query analyzer. This
> will ensure that they cant do any Insert / update / delete using Query
> analyzer or Enterprise manager.
> Thanks
> Hari
> MCDBA
>
> "Leonard Poon" <leonardpoon@.hotmail.com> wrote in message
> news:ulnyZ1uEEHA.1988@.TK2MSFTNGP10.phx.gbl...
their
inventory
> during
the
>|||Leonard, what Hari was describing was an Application Role. This is not the
same as a regular "Role." In an app role, there are no users who are
members. App roles are great for NT Security. Basically you take an NT
user, say CORP\TJones and add his security account to SQL Server
(sp_grantLogin), then you grant this user access to a database
(sp_grantDbAccess). Then you apply the specific permissions you want this
user to have. You can in fact use DENY SELECT INSERT UPDATE DELETE ON
<tablename> TO <user> and DENY EXEC ON <procedure> TO <user> for all of
your NT users. This prevents them from querying any tables either with QA,
Access, Excel, ODBC, etc. Then what you do is grant EXEC(UTE) rights to the
app role for the stored procedures in question. See, you're not putting
users in roles but you're giving rights to the roles.
Then as Hari said, in your front-end application, you connect to the
database as the user with a connection string that probably resembles
"SERVER=NAME;INITIAL CATALOG=database;INTEGRATED SECURITY=SSPI;" (the sspi
indicates you want to use NT authentication.) When you activate the
connection through code, the user's NT account is passed in and SQL Server
sees that the user has rights to nothing. Then from your connection you
execute the stored procedure sp_setapprole. You pass in only the app role
name and the app role's password. Since your application will be sending in
a password, your front-end applicaiton has to know it, so worst case you
compile it into your application, better case is you store it encrypted and
pull it via code.
When your application then calls the sp_setAppRole sproc, these steps happen
1) user connects as himself; permissions are applied
2) app role is initiated. ALL of the permissions set in step one are
TOTALLY FORGOTTEN.
3) The user that connected in step 1 now has the permissions that were given
to the app role.
4) when the user disconnects from the front-end app or the FE app closes the
connection, the NT user's permissions are demoted again.
Application roles are great for Windows Applications. They are not great
for Web application. Why? Connection pooling. You can't leave connections
in the pool that have permissions that you don't want to share with others.
But for the situation you've described, even in a web app turning off
connection pooling doesn't seem to be a performance killer. Bascially, app
roles just aren't scalable.
hth
Eric
"Leonard Poon" <leonardpoon@.hotmail.com> wrote in message
news:%23K1EfdwEEHA.1368@.TK2MSFTNGP11.phx.gbl...
> Thanks, Hari,
> I've already grouped users into application roles.
> Be more specific, let me give an example:
> First, we have 3 shops. Inventory records are kept separately by their
> bookkeepers. The inventory of each shop can only be read/updated by its
> bookkeeper, and shop manager, except for the upper management. The
records
> has to be kept as confidential as it could be. (may be exaggerated)
> Suppose there is a user who is in Bookkeeper role. I granted SELECT,UPDATE
> permissions to the tables in database for the role. After she logged in
the
> system, she can do anything that is permitted through the front-end
program.
> Let say, if she wants to browse inventory information, the program calls
the
> specific stored procedures/views which get data from her part of records.
In
> those stored procedures, we have to manually apply filters or 'WHERE'
> clauses to queries.
> Now, if she wants to update the inventory, the program will call the
stored
> proc as usual. But, this time those procedures may have to read entire
> database in order to support the 'if-then-else' branches. Since the
> permissions are already set for the user, there will be not enough
> permission to finish the procedures.
> How do I set the permission to resolve this? Or , did I misconceive the
> whole thing from the very beginning.
> Leonard
>
> "Hari" <hari_prasad_k@.hotmail.com> glsD
> :#7cnL$uEEHA.2308@.tk2msftngp13.phx.gbl...
> role.
> (So
But
all
> their
> inventory
> the
>
shops are stored in the same database with restricted permissions to their
own data. That means, users / programs can only access their own inventory
information.
But, this is problem, the entire database is required to be accessed during
internal processes and in stored procedures.
I wanna know if it is possible to have full-access permission on the
database for only inside stored procedures calls and keep restricting the
users from accessing other data.
LeonardHi,
You can implement "Application role" to implement the security strategy
mentioned. What you have to do is Create a Application role and
assign a password and give the required permission to the application role.
Steps:
1. Create a application role and set the password
2. Assign this application role to the database user
3. Enable this application role by giving the password inside your code (So
as users also will not know this password)
4. So after enabling this user will get the access inside application, But
they will get any prev. using query analyzer. This
will ensure that they cant do any Insert / update / delete using Query
analyzer or Enterprise manager.
Thanks
Hari
MCDBA
"Leonard Poon" <leonardpoon@.hotmail.com> wrote in message
news:ulnyZ1uEEHA.1988@.TK2MSFTNGP10.phx.gbl...
> I'm using a single database for inventory system. Inventory data of all
> shops are stored in the same database with restricted permissions to their
> own data. That means, users / programs can only access their own inventory
> information.
> But, this is problem, the entire database is required to be accessed
during
> internal processes and in stored procedures.
> I wanna know if it is possible to have full-access permission on the
> database for only inside stored procedures calls and keep restricting the
> users from accessing other data.
> Leonard
>|||Thanks, Hari,
I've already grouped users into application roles.
Be more specific, let me give an example:
First, we have 3 shops. Inventory records are kept separately by their
bookkeepers. The inventory of each shop can only be read/updated by its
bookkeeper, and shop manager, except for the upper management. The records
has to be kept as confidential as it could be. (may be exaggerated)
Suppose there is a user who is in Bookkeeper role. I granted SELECT,UPDATE
permissions to the tables in database for the role. After she logged in the
system, she can do anything that is permitted through the front-end program.
Let say, if she wants to browse inventory information, the program calls the
specific stored procedures/views which get data from her part of records. In
those stored procedures, we have to manually apply filters or 'WHERE'
clauses to queries.
Now, if she wants to update the inventory, the program will call the stored
proc as usual. But, this time those procedures may have to read entire
database in order to support the 'if-then-else' branches. Since the
permissions are already set for the user, there will be not enough
permission to finish the procedures.
How do I set the permission to resolve this? Or , did I misconceive the
whole thing from the very beginning.
Leonard
"Hari" <hari_prasad_k@.hotmail.com> glsD
:#7cnL$uEEHA.2308@.tk2msftngp13.phx.gbl...
> Hi,
> You can implement "Application role" to implement the security strategy
> mentioned. What you have to do is Create a Application role and
> assign a password and give the required permission to the application
role.
> Steps:
> 1. Create a application role and set the password
> 2. Assign this application role to the database user
> 3. Enable this application role by giving the password inside your code
(So
> as users also will not know this password)
> 4. So after enabling this user will get the access inside application, But
> they will get any prev. using query analyzer. This
> will ensure that they cant do any Insert / update / delete using Query
> analyzer or Enterprise manager.
> Thanks
> Hari
> MCDBA
>
> "Leonard Poon" <leonardpoon@.hotmail.com> wrote in message
> news:ulnyZ1uEEHA.1988@.TK2MSFTNGP10.phx.gbl...
their
inventory
> during
the
>|||Leonard, what Hari was describing was an Application Role. This is not the
same as a regular "Role." In an app role, there are no users who are
members. App roles are great for NT Security. Basically you take an NT
user, say CORP\TJones and add his security account to SQL Server
(sp_grantLogin), then you grant this user access to a database
(sp_grantDbAccess). Then you apply the specific permissions you want this
user to have. You can in fact use DENY SELECT INSERT UPDATE DELETE ON
<tablename> TO <user> and DENY EXEC ON <procedure> TO <user> for all of
your NT users. This prevents them from querying any tables either with QA,
Access, Excel, ODBC, etc. Then what you do is grant EXEC(UTE) rights to the
app role for the stored procedures in question. See, you're not putting
users in roles but you're giving rights to the roles.
Then as Hari said, in your front-end application, you connect to the
database as the user with a connection string that probably resembles
"SERVER=NAME;INITIAL CATALOG=database;INTEGRATED SECURITY=SSPI;" (the sspi
indicates you want to use NT authentication.) When you activate the
connection through code, the user's NT account is passed in and SQL Server
sees that the user has rights to nothing. Then from your connection you
execute the stored procedure sp_setapprole. You pass in only the app role
name and the app role's password. Since your application will be sending in
a password, your front-end applicaiton has to know it, so worst case you
compile it into your application, better case is you store it encrypted and
pull it via code.
When your application then calls the sp_setAppRole sproc, these steps happen
1) user connects as himself; permissions are applied
2) app role is initiated. ALL of the permissions set in step one are
TOTALLY FORGOTTEN.
3) The user that connected in step 1 now has the permissions that were given
to the app role.
4) when the user disconnects from the front-end app or the FE app closes the
connection, the NT user's permissions are demoted again.
Application roles are great for Windows Applications. They are not great
for Web application. Why? Connection pooling. You can't leave connections
in the pool that have permissions that you don't want to share with others.
But for the situation you've described, even in a web app turning off
connection pooling doesn't seem to be a performance killer. Bascially, app
roles just aren't scalable.
hth
Eric
"Leonard Poon" <leonardpoon@.hotmail.com> wrote in message
news:%23K1EfdwEEHA.1368@.TK2MSFTNGP11.phx.gbl...
> Thanks, Hari,
> I've already grouped users into application roles.
> Be more specific, let me give an example:
> First, we have 3 shops. Inventory records are kept separately by their
> bookkeepers. The inventory of each shop can only be read/updated by its
> bookkeeper, and shop manager, except for the upper management. The
records
> has to be kept as confidential as it could be. (may be exaggerated)
> Suppose there is a user who is in Bookkeeper role. I granted SELECT,UPDATE
> permissions to the tables in database for the role. After she logged in
the
> system, she can do anything that is permitted through the front-end
program.
> Let say, if she wants to browse inventory information, the program calls
the
> specific stored procedures/views which get data from her part of records.
In
> those stored procedures, we have to manually apply filters or 'WHERE'
> clauses to queries.
> Now, if she wants to update the inventory, the program will call the
stored
> proc as usual. But, this time those procedures may have to read entire
> database in order to support the 'if-then-else' branches. Since the
> permissions are already set for the user, there will be not enough
> permission to finish the procedures.
> How do I set the permission to resolve this? Or , did I misconceive the
> whole thing from the very beginning.
> Leonard
>
> "Hari" <hari_prasad_k@.hotmail.com> glsD
> :#7cnL$uEEHA.2308@.tk2msftngp13.phx.gbl...
> role.
> (So
But
all
> their
> inventory
> the
>
Subscribe to:
Posts (Atom)