July 1, 2004 at 6:33 pm
Hi all,
I have found in one of our organisations developers giving excess permission
for enusers who runs application. For eg. my company have a receipt program and several other apps and uses users windows permission group Domainname\users by giving "sys administrators role checked.
I dont understand why programmer code like that just to add new records and edit and print they have given permission to default windows group ( in win 2000 domain windows /users group added to sys admin group role)
but if remove by now program breaks at several levels.
Can anyone tell me how can i give a solution to our programmers and tell them not use excess and give more permission unnecessarily . I dont know how to code but there must be a way to do proper coding only to give edit ,modify and view permission only for internal windows users.
Can any one help me to find a relevant article to rsolve my problem or any suggestions how they should recode and modify thiere programs?
Thanks!!
cham
July 2, 2004 at 4:19 am
If I understand you correctly, you want to run GRANT on the tables.
e.g.
GRANT INSERT ON table TO AllUsers
If you use DENY then the users will not be able to access the table.
One way is to create Roles (e.g. Programmer, User, PowerUser, Admin)using EM, assign users to the Roles and then grant the Roles access to the tables. This will result in less admin than granting each user access to each table. If you get a new user or someone leaves the company, just add/delete them from the Role.
July 6, 2004 at 6:35 pm
Hi
Can you explain how I will create and use application role in sql and how do I use it thru my VB or asp coding?
Thanks
chem
July 7, 2004 at 3:58 am
Application role is something different to a SQL Roles. You can use one of the following in your VB program:
- Create a single SQL Login and your VB program can use that (the password will need to be coded in your VB program to don't give the login much permissions)
- Use NT authentication, but then each user needs a NT login created on your database. Create a SQL Role (see in EM), assign the Roles the necessary permissions and add your users NT logins to the Role. Lots of maintenance on this option.
- I have never used Application Roles, but when I read BOL, it seems like a good options.
From BOL:
"When an application role is activated for a connection by the application, the connection permanently loses all permissions applied to the login, user account, or other groups or database roles in all databases for the duration of the connection. The connection gains the permissions associated with the application role for the database in which the application role exists. Because application roles are applicable only to the database in which they exist, the connection can gain access to another database only through permissions granted to the guest user account in the other database. Therefore, if the guest user account does not exist in a database, the connection cannot gain access to that database.
There are several options for managing application role passwords without hard-coding them into applications. For example, an encrypted key stored in the registry (or a SQL Server database), for which only the application has the decryption code, can be used. The application reads the key, decrypts it, and uses the value to set the application role. Using the Multiprotocol Net-Library, the network packet containing the password can also be encrypted. Additionally, the password can be encrypted, before being sent to an instance of SQL Server, when the role is activated.
The use App Role:
Use sp_addapprole in QA to create the role.
Use EM, navigate to the Database you created the role in and go to the 'Roles'. Add the necessary permissions to the role you just created.
In your VB program, use ADO to execute sp_setapprole. This will destroy all accesses the current connection has, and use the Application Roles permissions for the remainder of the connection.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply