Security

Overview

Security should be purely an administrative task. The goal of the security model should be to maintain bullet-proof security while minimizing administrative maintenance requirements.

Ideally we need to secure more than just raw data: we need to secure the way the data is manipulated (i.e., secure the verbs as well as the nouns).

SQL Server Implementation

If possible, SQL Server should be configured with "integrated" security. This will allow client applications to leverage Windows NT security administration, and enable the users to have to remember one less password. Unfortunately, at the moment it requires that you have a Windows NT domain and are running named pipes. This restriction will become more relaxed in the future.

The guest login should be disabled. There are few reasons to allow guest access to the DBMS.

Stored procedures: single point of database entry

Stored Procedures

Users should be denied access to all SQL Server objects except stored procedures. The advantages of this include:

• Security is not dependent on the user's lack of systems knowledge. Even if the user knew how to code a front-end or use database tools, the user would still not be able to access any additional database functionality.

• End-user administration need only be performed on stored procedures, not client-side code or other database objects.

• MIS will have complete, centralized control of all code that runs on SQL Server. Business logic is "locked up" on the server.

• Developers will not have to "hard code" security checks into applications. When security administration changes, client-side code will not need to be recompiled.

Stored procedures should be categorized based on business functionality, and access assigned to the appropriate user groups.

sp_security

Client-side applications need to query the server for security information so they can enable and disable appropriate GUI functionality. It's inconvenient for a user to receive an error from the server because the user's logon doesn't have permission to execute a particular stored procedure.

The following procedure should be installed in all databases requiring client-side data access:

/* VBSEC.SQL */

/* This proc looks at the user_id of the current conn   */
/* and returns a list of stored procs in the current db */
/* that the user has permission to execute              */

/* This proc can be used by client application to retrieve  */
/* information to determine whether or not client-side      */
/* functionality should be enabled/disabled.                */

/* Craig Goren 01/08/95              */
/* Internet: cgoren@claritycnslt.com */
/* CIS:      72773.1062              */

IF EXISTS (SELECT * FROM sysobjects WHERE id =
    object_id('sp_security'))
begin
     DROP procedure sp_security
     print "Old procedure dropped."
end
GO

create procedure sp_security AS
begin
    /* if sa/dbo, the return everything */
    if USER_ID() = 1
    begin
            select        o.name 
            from        sysobjects o
            where        o.type = 'P'   /* only get SPs */
            order by    o.name 
                
            return 0                
    end

    /* begin "normal" user case */

    /* find everything you or your alias owns */
    select        o.name 
    from         sysobjects o,
                sysusers u
    where        o.uid = u.uid
             and     o.type = 'P'        /* only get SPs */
            and     o.uid = user_id()   /* current user */
    UNION

    /* find everything you or your alias has been */
     /* explicitly granted execute privliges on    */
    select         o.name 
    from         sysprotects p,
                    sysusers u,
                    sysobjects o
    where                 p.uid = u.uid        /* join */
                and     p.id = o.id         /* join */
                and     action=224             /* execute */
                and     protecttype=205        /* grant */
                and     p.uid = user_id()      /* current user */
    UNION

    /* find everything you or your alias' group has been */
    /* explicitly granted execute privliges on */
    select         o.name 
    from         sysprotects p,
                sysusers u,
                sysobjects o
    where                u.gid = p.uid         /* join */
                and     p.id = o.id            /* join */
                and     action=224             /* execute */
                and     protecttype=205        /* grant */
                and     u.uid = user_id()      /* current user */
    UNION

    /* find everything the PUBLIC group has been */
    /* explicitly granted execute privliges on   */
    select        o.name 
    from         sysprotects p,
                sysobjects o
    where         p.uid = 0              /* PUBLIC */
             and    p.id = o.id            /* join by object ID */
            and    action=224             /* execute */
            and    protecttype=205     /* grant */
    order by    o.name
end /* sp_security */
go
print "New procedure added."
go

/* Grant execute privliges to public group.  */
GRANT EXECUTE ON sp_security TO PUBLIC
go 
print "Execute privliges granted to PUBLIC."
go

This SQL script can also be found in the file VBSEC.SQL. When executed, the sp_security stored procedure will return a list of all stored procedures within the database that the user has permission to execute. The client-side application can use this list to predetermine what stored procedures a user can execute. Note that it doesn't take into account any ownership qualifications (i.e. it assumes that there won't be multiple stored procedures names with different owners within the same database).