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
>