ADO.NET With Microsoft Visual C#:

	Microsoft Visual C#: Data Selection and Permissions

Introduction

Data selection is the most fundamental and the most common operation performed on a database. This is because it allows a user to at least see the records of a table. This is also the most basic permission you can give to a user. As it happens, many permissions may depend on, or need, the ability to open a table. That's why the right to select records must sometimes be added to other permissions.

Controlling Data Selection

The formula to programmatically grant data selection to a user is:

GRANT SELECT [, Permission1,Permission2, Permission_n]
ON [ OBJECT :: ][ schema_name ].object_name [ (Column1, Column2, Column_n ] ) ]
TO Login1, Login2, Login_n ]

The formula to programmatically deny data selection is:

DENY SELECT [, Permission1,Permission2, Permission_n]
ON [ OBJECT :: ][ schema_name ].object_name [ (Column1, Column2, Column_n ] ) ]
TO Login1, Login2, Login_n ]

The SELECT permission can be used by itself but some other rights must be combined with it:

SELECT: When granted, this permission allows a user to open a table and see the records in it. If this permission is denied, the user cannot even open the table and therefore cannot see the records. Here is an example:

using (SqlConnection cnnExercise = new SqlConnection("Data Source=(local);" +
                                                     "Database='Exercise1';" +
                                                   "Integrated Security=yes;"))
{
    SqlCommand cmdDenySelect = new SqlCommand("DENY SELECT " +
                                    "ON OBJECT::Employees TO [Orlando Perez];",
                                              cnnExercise);

    cnnExercise.Open();
    cmdDenySelect.ExecuteNonQuery();
}

If you grant only the SELECT permission, the user can neither add new records nor change anything about the existing records

INSERT: The INSERT permission allows a user to create new records. If this permission is granted by itself, because it doesn't imply the SELECT permission, the user cannot open the table.

This means that you should (must) (always) combine the INSERT with the SELECT permissions. Here is an example:

private void btnOpenEmployees_Click(object sender, EventArgs e)
{
    using (SqlConnection cnnExercise =
    	new SqlConnection("Data Source=(local);" +
                          "Database='Exercise1';" +
                          "Integrated Security=yes;"))
    {
        SqlCommand cmdDenySelect = new SqlCommand("GRANT SELECT, INSERT " +
                                                  "ON OBJECT::Employees " +
                                                  "TO [Orlando Perez];",
                                                  cnnExercise);

        cnnExercise.Open();
        cmdDenySelect.ExecuteNonQuery();
    }

    using (SqlConnection cnnExercise =
    	new SqlConnection("Data Source=(local);" +
                          "Database='Exercise1';" +
                          "Integrated Security=no;" +
                          "User ID=operez;PWD=P@ssword1;"))
    {
        string strSelect = "SELECT * FROM Employees;";

        SqlCommand cmdExercise = new SqlCommand(strSelect, cnnExercise);
        SqlDataAdapter sdaExercise = new SqlDataAdapter(cmdExercise);
        BindingSource bsExercise = new BindingSource();

        DataSet dsExercise = new DataSet("EmployeesSet");
        sdaExercise.Fill(dsExercise);

        cnnExercise.Open();
        bsExercise.DataSource = dsExercise.Tables[0];

        dgvEmployees.DataSource = bsExercise;
    }
}

UPDATE: This permission allows a user to change one or more records on the table. If used alone, the UPDATE permission has no effect: the user cannot even open the table. This means that this right should (must) be combined with another. If the UPDATE permission is combined with the SELECT right, the user becomes able to open a table and change its record(s). Among the other ways you can use this right, if you grant it and grant SELECT but deny INSERT, the user would be able to change existing records but cannot add new ones

As mentioned during data entry, if you want to give a user the ability to grant or deny data selection to other accounts, add the WITH GRANT OPTION expression.


Home	Copyright © 2010-2016, FunctionX