Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2014

    Unanswered: ms access with sql server back end - stored procedures vs linked odbc tables

    Hi, I have developed a database in SQL server and a front end in MS Access using linked tables via ODBC. Everything works great and it is just an internal database for around 20 customers.

    My colleague who is a SQL expert has informed me that I should not allow direct access to any tables and should use stored procs for everything. I take his point however I intend to lock it down so user has only access to forms and not tables AND using stored procs means I will need to control everything in code - adding records, retrieving records - dealing with subforms etc. To me this sounds like A LOT of extra work and I just want to get another opinion on the best practice - should I do as he says and rewrite all the standard database functions to use stored procs for everything? Is this the normal method?

    Many thanks for any help

    Jenny B

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    This is a lot of work, indeed.

    One first step to securize the system would consist in using linked views (which appears like tables in Access). At the SQL Server level, you forbid users access to the tables but grant access to the views.

    If you want to go further, you need to write a set of 4 stored procedures (at the minimum) for each table (Create, Read, Update, Delete). Using stored procedures in Access is not very difficult. You can use the ADODB library or the DAO library in which you use a Querydef for communicating with the server.

    Deeper in security, you render the stored procedures "anonymous" (kind of):
    1. For each table, the associated SPs receive a symbolic name (e.g. GetRow, GetList, InsertRow, UpdateRow, DeleRow).
    2. There is a table on the server that acts as a catalog. It associates each symbolic name with the actual name of the SP and the name of the class, form or module that is allowed to use it.
    3. The second part of this catalog is a SP that receives the name of the class, form or module as argument and returns the names of the set of procedures that the caller can use. The name of this SP is the only one that is hard-coded into the client application. A sophisticated version of this SP can also return that names and types of the arguments expected by the target SP. Here's an example:
    -- Database:     Reclamations
    -- Objet:        PROCEDURE [dbo].[Proc_Get_Procedures_Catalog]
    -- Author:       
    -- Date:         2012-11-25 11:50:42 (Replaces Proc_Get_Procedure_Names).
    -- Modification: 2012-12-01 18:39:00 (Argument is mandatory for Fn_Get_User_ID).
    --               2012-12-02 09:36:06 (Fn_Get_User_ID --> Fn_Get_Current_User_ID).
    --               2012-12-02 12:50:48 (Use Fn_Get_Procedure_Parameters).
    --               2012-12-03 23:01:07 (Use Tbl_Procedures_Catalog.[Parameters] when Is Not NULL,
    --                                    use Fn_Get_Procedure_Parameters otherwise).
    --               2012-12-19 19:40:11 (Check Credentials using [Fn_Get_User_Credentials]).
    --               2013-06-13 13:33:29 (Check Credentials using [Fn_Authorize]).
    --               2013-12-27 12:31:22 (Returns new column VectParams).
    -- Used by:      All applications
    CREATE PROCEDURE [dbo].[Proc_Get_Procedures_Catalog]
         @ApplicationName NVARCHAR(127)
       , @FormName NVARCHAR(127) = '%'
       , @Topic NVARCHAR(50) = '%'
        DECLARE @ErrorMsg NVARCHAR(2000) 
        DECLARE @ProcName NVARCHAR(128)   
        DECLARE @Status INT
        DECLARE @ErrID INT = 0
        DECLARE @ReturnValue INT
        DECLARE @UserCredentials INT
        BEGIN TRY
            IF (SELECT [dbo].[Fn_Authorize](@ApplicationName, NULL)) > 0
                    IF @FormName = '*' SET @FormName = '%'
                    IF @Topic = '*' SET @Topic = '%'
                    SELECT Tbl_Procedures_Catalog.Form_Name
                         , Tbl_Procedures_Catalog.Role_Name
                         , Tbl_Procedures_Catalog.[Procedure_Name]
                         , CASE WHEN ISNULL(Tbl_Procedures_Catalog.[Parameters], '') = ''
                                THEN [dbo].[Fn_Get_Procedure_Parameters](Tbl_Procedures_Catalog.[Procedure_Name]) 
                                ELSE Tbl_Procedures_Catalog.[Parameters]
                           END AS [Parameters]
                         , Tbl_Procedures_Catalog.VectParams  
                      FROM Tbl_Procedures_Catalog INNER JOIN
                           ( SELECT DISTINCT SPECIFIC_NAME
                             FROM INFORMATION_SCHEMA.ROUTINES
                           ) AS i ON Tbl_Procedures_Catalog.[Procedure_Name] = i.SPECIFIC_NAME  
                     WHERE ( (Tbl_Procedures_Catalog.Application_Name = @ApplicationName) OR
                             (Tbl_Procedures_Catalog.Application_Name = '<Any>') 
                           ) AND
                           (  Tbl_Procedures_Catalog.Form_Name LIKE @FormName AND
                              Tbl_Procedures_Catalog.Topic LIKE @Topic AND
                              Tbl_Procedures_Catalog.Inactive = 0
                     SET @ReturnValue = @@ROWCOUNT
                     SET @Status = -1
                    SET @ProcName = OBJECT_NAME(@@PROCID);
                    SET @ReturnValue = 70
                    SET @Status = 0
                    EXEC Proc_Log_ProcedureEvent @ProcedureName = @ProcName
                                               , @LogCode = @ReturnValue
                                               , @LogCredentials = @UserCredentials
                                               , @LogMessage = 'Permission denied'            
        END TRY
            SET @ProcName = OBJECT_NAME(@@PROCID);
            SET @ErrorMsg = ERROR_MESSAGE();
            SET @ReturnValue = ERROR_NUMBER();
            SET @Status = 0;
            EXEC Proc_Log_ProcedureEvent @ProcName, @ReturnValue , @ErrorMsg;
            SET @ErrID = IDENT_CURRENT('Tbl_Log_Procedures');
        END CATCH
        SELECT @ReturnValue AS ReturnValue, @Status AS [Status], @ErrID AS ErrID;
        RETURN @Status
    Have a nice day!

  3. #3
    Join Date
    Mar 2009
    Provided Answers: 14
    If you're satisfied with the security level gained form the change of linked tables to linked views, don't go further. Using stored procedures is a lot of work and need to extend your programming skills to T-SQL, which can be difficult as it means to learn a totally different programming language.

    You're welcome, by the way!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts