Sql-server – Restrict update on certain columns. Only allow stored procedure to update those columns

sql serversql-server-2012stored-procedurest-sqltrigger

I have sensitive price columns that I would like to have updated only through a stored procedure. I would like all code or manual attempts to alter values in these price columns to fail if it is not using the stored procedures designed to update it.

I am considering implementing this using triggers and a token table. The idea I am considering is to have a token table. the stored procedures will have to first insert values in the token table. Then update the price columns. The update trigger will check if the token exists in the token table for the updated row. If found, it would continue. if the token is not found, it will throw an exception and make the update transaction fail.

Is there a good/better way to implement this restriction?

Best Answer

SQL Server allows column-level permissions. Just for example:

GRANT UPDATE ON dbo.Person (FirstName, LastName) TO SampleRole;
DENY UPDATE ON dbo.Person (Age, Salary) TO SampleRole;

QUESTION #1

What is error in this procedure?

You seem to have some scope confusion on the variables

ANSWER TO QUESTION #1

PROBLEM : Your parameters have identical names to column names in the tables. This could produce some unpredictable results.

SOLUTION : Change the names of the parameters so that they are distinct from the column names

create procedure accountstatus

(

    IN  inmode              varchar(27),
    IN  given_AccountStatus_id    int,
    IN  given_AccountStatus       varchar(255),
    IN  given_CreatedOn           datetime,
    IN  given_CreatedBy           varchar(255),
    IN  given_UpdatedOn           datetime,
    IN  given_UpdatedBy           varchar(255),
    IN  given_is_active           bit)

    Begin

    if inmode   =   'insert'
    then
        insert into accountstatus
        (AccountStatus_id, Account_Status, CreatedOn, CreatedBy, UpdatedOn, UpdatedBy, is_active)
        values
        (given_AccountStatus_id, given_Account_Status, given_CreatedOn, given_CreatedBy, given_UpdatedOn, given_UpdatedBy, given_is_active);
    end if;

    /*update*/

    if inmode   =   'update'
    then
        update accountstatus acc
        set
--      acc.AccountStatus_id    =   given_AccountStatus_id,  <- Not Needed for UPDATE
        acc.Account_Status      =   given_Account_Status,
        acc.CreatedOn           =   given_CreatedOn,
        acc.CreatedBy           =   given_CreatedBy,
        acc.UpdatedOn           =   given_UpdatedOn,
        acc.UpdatedBy           =   given_UpdatedBy,
        acc.is_active           =   given_is_active
        where
        acc.AccountStatus_id    =   given_AccountStatus_id;

    end if;

    /*delete*/

    if inmode   =   'delete'
    then
        update  accountstatus acc
        set
--      acc.AccountStatus_id    =   given_AccountStatus_id, <- Not Needed for DELETE
        acc.is_active           =   0
        where
        acc.AccountStatus_id    =   given_AccountStatus_id;
    
    end if;

    /*select*/

    if inmode   =   'select'
    then
        select * from accountstatus acc
        where
        acc.AccountStatus_id    =   given_AccountStatus_id;
    end if;
end

CAVEAT : Please note that I commented out two lines

QUESTION #2

Is there any other ways to implement this procedure?

ANSWER TO QUESTION #2

You could use triggers

Aug 15, 2011 : Performance of a Trigger vs Stored Procedure in MySQL
Apr 06, 2012 : How to speed up audit trigger?

QUESTION #3

How it will effect the performance of the database?

ANSWER TO QUESTION #3

Doing bulk operations can make the MySQL server process to tedious work and bog it down. Here is other posts showing how to use SQL efficiently to replace a trigger and stored procedure, why too many triggers can be bad, and how as little code as possible

Aug 11, 2011 : Policies RE database triggers in well-designed applications?
Aug 12, 2011 : Performance of a Trigger vs Stored Procedure in MySQL
Dec 23, 2011 : How to set up triggers in MySQL for copying a field

EPILOGUE

The simpler the code in the Stored Procedure or Trigger, the less impact on performance, especially on bulk INSERTs, UPDATEs, and DELETEs.

Please consider the Storage Engine and its locking characteristics (using MyISAM) when using triggers and the autocommit behavior (if using InnoDB).

Mysql – Stored Procedure – MySQL

In my opinion, a procedure should do one thing and one thing only. The logic should be performed in the application code. Secondly, a procedure should not return a result set. It should do work and then possibly return a status e.g. success, failure or something similar. Also, the name of your procedure is meaningless. Choose a descriptive name.

So, break it up into three procedures (insert, update and delete). Move the select statement into a view rather than a procedure. Let your programmers add the logic in their code to determine which one to call. Make sure you put index on the table.

Never do a select *. Only select the columns you need. It may be that you add columns later on but you will still SELECT *. Your SELECT * will then store all (incl unnecessary columns) columns in memory and, in worst case, run out of memory and be swapping to disk.