Re: SP Permission Inheritance and Table Creation

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

Date: Sun, 27 Jul 2003 13:15:42 -0400

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
"Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
news:ec%23vVJFVDHA.2364@TK2MSFTNGP09.phx.gbl...
> The same CREATE statement permissions apply regardless of how the CREATE
> statement is executed.  You can't restrict a statement permission so
> that is allowed only within a proc.
>
> An approach you might consider is to employ application roles.  This
> allows users to connect with their normal login yet allow the
> application to enable a more powerful security context that can only be
> used within the application.
>
> Can you elaborate on why users need to execute DDL from within your app?
> This is not a best practice and perhaps someone can suggest a better
> alternative.
>
> -- 
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
>
> "Meir Simcha Kogan" <mkogan@chabadonline.com> wrote in message
> news:OVQK3oAVDHA.2004@TK2MSFTNGP10.phx.gbl...
> > So is there anyway way to allow a user to create tables via a SP
> without
> > jeapordizing permissions on the whole database, i.e. can I restrict
> table
> > creations outside the SP?
> >
> > Thanks
> >
> > -- 
> > Meir Simcha Kogan
> > Chabad.org Development Team
> > "Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
> > news:OKrtJRxUDHA.1916@TK2MSFTNGP12.phx.gbl...
> > > Don't confuse statement permissions with object permissions.
> Statement
> > > permissions, like CREATE TABLE, give the user the right to create
> > > objects under their own userid.  A user needs to be a member of a
> more
> > > powerful role (ddl_admin or db_owner) to create objects owned by
> dbo.
> > >
> > > Ownership chaining applies only to object permissions (SELECT,
> INSERT,
> > > DELETE, UPDATE, EXECUTE and REFERENCES).  This is not inheritance.
> An
> > > unbroken township chain simply means that permissions on indirectly
> > > referenced objects are not checked.  A user only needs permissions
> on
> > > the directly referenced objects.  Cross database chaining extends
> the
> > > chaining concept to include inter-database access.
> > >
> > > -- 
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > -----------------------
> > > SQL FAQ links (courtesy  Neil Pike):
> > >
> > > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > > http://www.sqlserverfaq.com
> > > http://www.mssqlserver.com/faq
> > > -----------------------
> > >
> > >
> > > "Meir Simcha Kogan" <mkogan@chabadonline.com> wrote in message
> > > news:ulUV8jtUDHA.2204@TK2MSFTNGP12.phx.gbl...
> > > > Hi,
> > > >
> > > > We have an application that connects to our sql server with a
> limited
> > > access
> > > > account - we control permissions via stored procedures. That is
> the
> > > only
> > > > thing this user has access to and permissions are controlled from
> > > within the
> > > > stored procedure -- actions on the db are performed using
> permissions
> > > > inherited from the creator of the stored procedure using cross
> > > database
> > > > chaining....
> > > >
> > > > My question involves a stored procedure which is supposed to
> create
> > > and
> > > > update a table. The problem is that it seems that permissions do
> not
> > > get
> > > > inherited from the SP to create new tables...
> > > >
> > > > How can I allow this SP to create a new table without adding
> "Create
> > > Table"
> > > > permission for this user on the db. In addition, if it could
> create
> > > it, it
> > > > creates the table with this users name and I would like the table
> to
> > > be
> > > > created under the dbo as all our code is already referencing the
> > > tables with
> > > > dbo.tablename. I tried SP_ChangeObjectOwner - but it seems it does
> not
> > > have
> > > > permissions in the SP to do that - even though the SP creator is a
> > > db_owner.
> > > >
> > > > Thank you for your help.
> > > >
> > > >
> > > > -- 
> > > > Meir Kogan
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: SP Permission Inheritance and Table Creation
    ... The same CREATE statement permissions apply regardless of how the CREATE ... Can you elaborate on why users need to execute DDL from within your app? ... >> referenced objects are not checked. ...
    (microsoft.public.sqlserver.security)
  • Re: "REVOKE ALL FROM public" not working
    ... Statement permissions ... The REVOKE ALL only applies to the statement permissions, ... > INSERT, UPDATE, DELETE on all user tables and EXECUTE on all sprocs. ...
    (microsoft.public.sqlserver.security)
  • Re: Security - Stored Procedures
    ... > This is known as ownership chaining. ... permissions are only checked on directly referenced objects. ... You can change database ownership using ...
    (microsoft.public.sqlserver.security)
  • Re: view permissions
    ... Permissions on referenced objects ... keycolumn int NOT NULL ... col2 int NOT NULL, ...
    (microsoft.public.sqlserver.security)
  • Re: SP Permission Inheritance and Table Creation
    ... Don't confuse statement permissions with object permissions. ... A user needs to be a member of a more ... referenced objects are not checked. ... > stored procedure -- actions on the db are performed using permissions ...
    (microsoft.public.sqlserver.security)