Re: Alternative to Dynamic SQL?

<sqlguru@xxxxxxxx> wrote in message news:1b0960b6-d228-40c0-9ee5-07004548bfc7@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Why would you deny a specific user access to the DB? Do users really
connect to your database and run queries??? This is a very poor
argument. If security is that much of a concern, shutdown your sql
servers.

You might deny a user specific access to your DB to keep them out. Happens all the time, all over the world, every single day.

However, I said deny a user access to a TABLE, which is obviously different from a DB.

This is commonly done to prevent users from directly modifying the contents of tables that should only be modified through a specific front end application, for instance.

Security is very much a concern for professional DBAs -- at least the ones who can't afford to shutdown their SQL servers and can't afford to have their data mindlessly destroyed by so-called "power users" or stolen by hackers.

I would highly recommend you Google up some free information on "ownership chaining" for a complete discussion of this particular topic, and the real-world implications to the security of the data in your SQL Server.

In the application, each database is suppose to have an application
side DAL where all applications depending on that database go through
that specific DAL. Stored procedures could be considered a "DAL" but
have you tried maintaining 1000s of SPs? Especially when theres
obsolete ones? Also, when there are modifications, how do you refactor
or do you even get intellisense? Do you just create another SP?

Have you tried maintaining 1000s of queries? You get Intellisense when you're assigning a query to a C# string variable, do you? Now you're talking the "same difference."

The idea that the application should just ask for data from the
database and let the database worry about retrieving it will have some
serious performance impact on the application. For an example, like
pagination and search interfaces where using static SQL or stored
procedures is just "cowboy coder" insane.

<< "The idea that the application should just ask for data from the database and let the database worry about retrieving it will have some performance impact on the application." >>

When you fire a SELECT query at the database, what exactly are you doing? This is something more than "asking for data from the database" and "letting the database worry about retrieving it"? This really makes no sense.

The pagination issue is a well-defined problem with literally hundreds of solutions all over the place. Search interfaces are also a well-defined problem, and can also be solved in a variety of ways. Not sure what your point is here...

--

========
Michael Coles
"Pro T-SQL 2008 Programmer's Guide"
http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X

.

Relevant Pages

  • Re: setting a password on a button on the switchboard
    ... Could you send me the sample database for the fourth option (4. ... > Security in an Access database can probably be broken down into two big ... > points about being easier than User Level Security, ... > What type of data are you trying to protect? ...
    (microsoft.public.access.forms)
  • Re: access 2003
    ... security in access 2003. ... The data will go on the server and the program database ... than the alternative of creating an mde file. ... MDW file from the written record. ...
    (microsoft.public.access.conversion)
  • Re: access 2003
    ... security in access 2003. ... The data will go on the server and the program database ... than the alternative of creating an mde file. ... MDW file from the written record. ...
    (microsoft.public.access.conversion)
  • Re: Is this possible??
    ... I understand Windows security but since I've not seen A2007 live, ... The backend is on the server in it's own file. ... database, but everyone does not need to have access to tblwage which is ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Is it safe to use social securty number as intranet username? (long)
    ... > they expect us to use our social security number as a username. ... by some database application ... ... The gateway router runs radius for authenticating ... ISPs perform internet connection authentication) ...
    (comp.security.misc)
Loading