Re: SP Permission Inheritance and Table Creation

From: Meir Simcha Kogan (mkogan_at_chabadonline.com)
Date: 07/27/03 

Date: Sun, 27 Jul 2003 16:01:34 -0400

This helps a lot.. one question though....

we went at all lengths to avoid hardcoding (or even storing it externally
encrypted) because we found that a slick user could read the memory space on
his computer and find the line that actually makes the connection. --
therefore we limited all permissions to SPs which themselves enforce
security.

So what stops a user from 'sniffing' the app role password and then
connecing on his own?

Thanks 

-- 
Meir Simcha Kogan
Chabad.org Development Team
"Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
news:eghe7NHVDHA.212@TK2MSFTNGP12.phx.gbl...
> > 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: 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: 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)
  • Re: SP Permission Inheritance and Table Creation
    ... > a way that limits the user from actually mimicking the application ... role so that you can execute sp_rename. ... CREATE PROC usp_PubStatic_Maintenance ... sysadmin role members or the dbo can execute the procedure. ...
    (microsoft.public.sqlserver.security)
  • 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 service ... > the stored procedure from enterprise manager or query analyser (others ...
    (microsoft.public.sqlserver.security)
  • Disk Suite
    ... metareplace d23 c3t0d0s7 ... insert the new drives, partition it, and execute the commands or am I ... State: Needs maintenance ...
    (comp.unix.solaris)