Re: major security concern - any sql user with minimal permission can see code for all stored procs and triggers

From: Trayce Jordan (trayce@jordanhome.net)
Date: 05/23/02 

From: "Trayce Jordan" <trayce@jordanhome.net>
Date: Wed, 22 May 2002 18:59:13 -0500

I don't believe this is entirely true. by default - yes i agree because
public is granted to most if not all of these objects. if however you
remove select access (and execute access on selected stored procs in the
master and msdb databases) from certain systables (syscomments is where the
source code for procs, etc. is stored) - then a user won't have "full
access" to the meta data.

I'm currently working on what I can and can't remove to make all this work,
but I'm already restricting access for a user to only 3 user tables and the
sysusers table and that's it. it seems to work. --- Now that user CAN NOT
use enterprise manager anylonger because all of the necessary objects it
needs to run have been removed from public access.

If your goal is to give "customized" access for the user to "create"
things - and hence has enterprise manager - then this approach won't work.
If you just want to lock down a database, then I'd try my approace - lock
down everything and add back as necessary - it can be a real pain and I'm
not through with my experiments - but so far so good.

Definitely experiment in a test or development environment - and do it all
through scripts so that you have a reproducible environment to move into
production.

Trayce Jordan MCDBA, MCSD

"Richard Waymire [MS]" <rwaymi_ms@microsoft.com> wrote in message
news:OUMpMSd$BHA.1828@tkmsftngp05...
> no, you're not missing anything. Current design is that if you are a user
> (with any permissions at all) in a database then you can see all the
> metadata in the database. No supported way to change this.
>
> --
> Richard Waymire, MCSE, MCDBA
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Pankul Verma" <pankul@urbanwireless.com> wrote in message
> news:OQsnJ#D$BHA.2200@tkmsftngp02...
> > I created a SQL authenticated login to my sqlserver for a business
partner
> > who needed to execute an SP on my server at his location. gave him
> > permission only on 1 SP (no tables etc)
> >
> > before rolling out, I did my homework,
> > connected from a remote location to my SQL server at port 1433, and the
> > application worked
> >
> > now I used Enterprise Manager to add the SQL Server from my remote
> location,
> > which gave me the access to view each and every Stored Proc, trigger,
> table
> > DRI etc ...
> > infact i was able to Script my entire database using this.
> >
> > same results from query analyser ...
> >
> > obviously I cud'nt select data from tables or execute SPs that i did not
> > have access to, however ... this raises a big concern of security for
me,
> if
> > a guy can see all the source code, its not nice!
> >
> > Am I missing somehting?
> >
> > Pankul
> >
> >
> >
>
>

Relevant Pages

  • Re: Viewing default db role permissions
    ... > whether that user or role has access to various objects in the database ... > the database in Enterprise Manager, going to the "Role" tree item, ... > checkmarks representing what the role/user has access to and red X's ... > permissions of each of these roles from the SQL Server documentation, ...
    (microsoft.public.sqlserver.security)
  • SQL Server does not exist or access denied
    ... I get the error both in application code and from Enterprise Manager. ... The database is LOCAL. ... Microsoft SQL Server 2000 Developer Edition SP4 ... Once it fails, it will fail ...
    (comp.databases.ms-sqlserver)
  • SQL Server does not exist or access denied
    ... I get the error both in application code and from Enterprise Manager. ... The database is LOCAL. ... Microsoft SQL Server 2000 Developer Edition SP4 ... Once it fails, it will fail ...
    (comp.databases.ms-sqlserver)
  • Re: Maybe the Records Are There After All
    ... Run DBCC UPDATEUSAGE to correct the row count display in Enterprise Mangler. ... Senior Database Administrator ... I support the Professional Association for SQL Server ... > Enterprise Manager and it gave me the sensation that ALL of the data had ...
    (microsoft.public.sqlserver.server)
  • Re: major security concern - any sql user with minimal permission can see code for all stored procs
    ... > remove select access (and execute access on selected stored procs in the ... > things - and hence has enterprise manager - then this approach won't work. ... > If you just want to lock down a database, then I'd try my approace - lock ... > Trayce Jordan MCDBA, MCSD ...
    (microsoft.public.sqlserver.security)