Assigning Permissions in .NET framework

Creation PDF-417 2d barcode in .NET framework Assigning Permissions

Now that you ve created your table, you need to provide permissions for users to access it. As you learned in 2, Configuring SQL Server 2005, all objects in SQL Server are secured. Furthermore, SQL Server does not provide any access unless permission has been explicitly granted. A member of the sysadmin fixed server role has already been granted unlimited rights to any object within the SQL Server instance, so a member of this role can perform any operation on a table. A member of the database owner fixed database role has already been granted permission to perform any operation on any object within the database that is owned, so a member of this role can perform any operation on a table. Additionally, the owner of a table has already been granted explicit authority to perform any operation against a table that he or she owns. All other users must be assigned permissions to work with a table.

Security best practices dictate that you never grant permissions directly to a user. Therefore, you should add a Microsoft Windows login to a Windows group and the Windows group as a login to SQL Server. You then add this group as a user in a database. Next, create roles in a database corresponding to various job functions, and assign database users to the appropriate role. Finally, assign security permissions on objects in the database to the database role. It is assumed that for all examples regarding security, you are implementing security best practices.

3

There are seven permissions that you can assign for a table, as listed in Table 3-9.

Table 3-9

Purpose Gives the authority to create any table in the database. Gives the authority to change the structure of any table in the database. Allows rows to be retrieved from the specified table. Allows rows to be inserted in a specified table. Requires the SELECT permission to be granted as well. Allows rows to be modified in a specified table. Requires the SELECT permission to be granted as well. Allows rows to be deleted from a specified table. Requires the SELECT permission to be granted as well. Used with foreign key constraints; to be discussed in the next lesson.

You can use the special keyword ALL to grant every permission shown in the table to a specified role. However, you should always explicitly list each permission that you will allow. The general statement to assign permissions is the following:

GRANT { | [ [ ALL [ PRIVILEGES ] } permission [ ( column [ ,...n ] ) ] [ ,...n ] ON [ class :: ] securable ] TO principal [ ,...n ] WITH GRANT OPTION ] [ AS principal ]

The ON clause specifies the object that you are granting permission to, whereas the TO clause specifies the database role the permissions are assigned to. For tables, it is possible to grant permissions on a subset of the columns in the table. There is no facility to grant permissions to a subset of rows in a table. The WITH GRANT option enables you to grant permissions to a role whose members can then grant permissions to other users or roles. You should never use this option because it takes control of security out of the hands of the owner of the table.

For the CustomerAddress table, the command to grant SELECT, INSERT, UPDATE, and DELETE permissions to a role is as follows:

In this practice, you will create three additional tables Customer, StateProvince, and Country for use with the CustomerAddress table we created in this lesson.

The instructions for creating the CustomerAddress table are earlier in this lesson, under the heading Permanent Tables.

The Customer table will contain the customer name, a value for the customer s credit line, a value for the customer s outstanding balance, a computation for available credit, and the date the customer record was created. The StateProvince table will contain a text-based column that will store a list of the valid states or provinces recognized by this company. The Country table will contain a text-based column that will store a list of the valid countries. Remember to create a column to reference each of the rows the same way we did with the CustomerAddress table.