Re: SP Permission Inheritance and Table Creation

From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 07/27/03 

Date: Sun, 27 Jul 2003 14:15:44 -0500

> Can you elaborate on your suggestion to use Application roles.... I
have
> heard of them but am not sure how they can help or how to implement
them in
> a way that limits the user from actually mimicking the application
from an
> application he creates on his own.

It seems to me the application role approach will work well here. You
can create an app role in your user database and add it to the db_owner
role so that you can execute sp_rename. For example:

EXEC sp_addapprole 'Maintenance', 'MaintenancePassword'
EXEC sp_addrolemember 'db_owner', 'Maintenance'
GO

CREATE PROC usp_PubStatic_Maintenance
AS
--create pub_static2
SELECT *
INTO dbo.pub_static2
FROM SomeTable

--apply changes
BEGIN TRAN
DROP TABLE pub_static
EXEC sp_rename 'dbo.pub_static2', 'pub_static'
COMMIT
GO

Since the user does not know the application role password, they can't
enable it outside your application. You can either hard-code the
password in your app or store it externally (encrypted). No execute
permissions on the proc are needed because the application role in this
example is a member of the db_owner role. Only db_owner members,
sysadmin role members or the dbo can execute the procedure.

To use the application role from your application, connect with
connection pooling disabled, enable the app role, execute your proc and
disconnect:. C# example:

try
{
    // establish connection with pooling off
    SqlConnection sqlConnection =
        new SqlConnection
            (
            "Data Source=MyServer;" +
            "Integrated Security=SSPI;" +
            "Initial Catalog=MyDatabase;" +
            "Pooling=False"
            );
    sqlConnection.Open();
    SqlCommand sqlCommand = new SqlCommand();
    sqlCommand.Connection = sqlConnection;
    sqlCommand.CommandType = CommandType.StoredProcedure;

    // enable app role
    sqlCommand.CommandText = "sp_setapprole";
    sqlCommand.Parameters.Add
        (
        new SqlParameter
            (
            "@rolename",
            "Maintenance"
            )
        );
    sqlCommand.Parameters.Add
        (
        new SqlParameter
            (
            "@password",
            "MaintenancePassword"
            )
        );
    sqlCommand.ExecuteNonQuery();

    // execute maintenance proc
    sqlCommand.Parameters.Clear();
    sqlCommand.CommandText = "usp_PubStatic_Maintenance";
    sqlCommand.ExecuteNonQuery();
    sqlConnection.Close();
}
catch (SqlException ex)
{
    MessageBox.Show(ex.ToString());
} 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Meir Simcha Kogan" <mkogan@chabadonline.com> wrote in message
news:eS$eXKGVDHA.532@TK2MSFTNGP10.phx.gbl...
> The reason we maintain a SP that deletes and creates a table is
because this
> table serves as a 'static index' that we generate for quicker access
to
> data. Basically it is a demoralized table that speeds up queries
against our
> data.
>
> Some changes that update the table require huge amounts of records to
be
> dropped and recreated and we found that the quickest way to do this
was to
> create another table, populate it with data, and only then drop the
real
> table and rename the new table to the real table name.
>
> I.e. the real table name is pub_static. The sp creates a table called
> pub_static2 and when done drops Pub_static and renames Pub_Static2 to
> Pub_Static. This minimizes downtime to the real table to a split
second,
> whereas dropping and creating rows in the table can take up to 4
minutes.
>
> ---
>
> Can you elaborate on your suggestion to use Application roles.... I
have
> heard of them but am not sure how they can help or how to implement
them in
> a way that limits the user from actually mimicking the application
from an
> application he creates on his own.
>
> -- 
> Meir Simcha Kogan
> Chabad.org Development Team

Relevant Pages

  • Re: Why no folder manipulation in Std C
    ... but I was a bit provocative because I was not able to find in the standard why the statement shouldn't hold and I wanted to trigger a clear answer like yours to clarify the idea. ... "The implementation shall be able to translate and execute at least one ... I find this sentence very confusing since the limits of the implementation for translation and the limits of the implementation for execution may not be the same. ...
    (comp.std.c)
  • Re: SP Permission Inheritance and Table Creation
    ... So what stops a user from 'sniffing' the app role password and then ... > role so that you can execute sp_rename. ... > EXEC sp_addapprole 'Maintenance', 'MaintenancePassword' ... > permissions on the proc are needed because the application role in this ...
    (microsoft.public.sqlserver.security)
  • Re: Performance Problem Using ADO and Stored Procs
    ... >I have a stored proc that executes in < 15 seconds through Query Analyzer. ... If I execute this proc ... > of CPU activity on the server hosting SQL server for the SQL server ... > Private Function RunProc(vntDB As Variant, strProcName As String, ...
    (microsoft.public.data.ado)
  • Re: GUI for access 2002
    ... 1.I execute a command button that loads the form with data from a members ...
    (microsoft.public.access.gettingstarted)
  • Re: Auditing SP Execute.
    ... > As long as the users only have execute permissions on the proc i.e. they ... This assumes that the app doesn't use a single ... > Jasper Smith (SQL Server MVP) ... >> particular sp was execute and by whom. ...
    (microsoft.public.sqlserver.security)