Re: SP Permission Inheritance and Table Creation
From: Meir Simcha Kogan (mkogan_at_chabadonline.com)
Date: 07/27/03
- Next message: Giacomo: "Re: Unattended.iss"
- Previous message: John Bell: "Re: Unattended.iss"
- In reply to: Dan Guzman: "Re: SP Permission Inheritance and Table Creation"
- Next in thread: Dan Guzman: "Re: SP Permission Inheritance and Table Creation"
- Reply: Dan Guzman: "Re: SP Permission Inheritance and Table Creation"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
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
>
>
- Next message: Giacomo: "Re: Unattended.iss"
- Previous message: John Bell: "Re: Unattended.iss"
- In reply to: Dan Guzman: "Re: SP Permission Inheritance and Table Creation"
- Next in thread: Dan Guzman: "Re: SP Permission Inheritance and Table Creation"
- Reply: Dan Guzman: "Re: SP Permission Inheritance and Table Creation"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
