Re: Identity Column

You didn't answer my question and again have totally missed the security hole your proposed method has.

So, the end use is using Crystal Reports - where is the magic?

The user will connect using Active Directory (kerborous) from their desktop to the database server or do please explain the magic....

secure tunnels, protocols are just that - encryption of the comms traffic from the client to the database server.

Using stored procedures you permission on the stored proc only; the user can only execute the stored procedure with the parameters defined, there is no select * from [users] to get all the information in your database.

From your answer (lack of understanding of connectivity, how you permission
proc execution ) it really does sound like you aren't that much of a Microsoft SQL Server guy afterall; what is it - Oracle? DB2?

Nesting views is not a way forward and I agree just complicates everything.

Sorry Daniel - try again....


"sqlguru" <sqlguru@xxxxxxxx> wrote in message news:7bea3a7f-eedf-4b33-9eb5-2276ad02bd8f@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Jun 15, 10:18 am, "Tony Rogerson" <tonyroger...@xxxxxxxxxx> wrote:
lets recap because you have totally missed the point which is the fact that
any user can just strike up and connect to your database server - a couple
of months ago a very widely effecting virus took advantage of EXACTLY how
you recommend people code.

i think you are heading in the right direction by stating "the end user
SHOULD NOT be "creating...."

given you have followed best practice and the middle tier doesn't use a
single sign on and that each individual user correctly authenticates with
the database resource using AD so that data modifications can be properly
audited just how have you truly hardened the database?

There is nothing to stop people just striking up any MS Office product or
adhoc crystal connection directly to the SQL Server and doing whatever they
want - I bet you've even given users insert/update/delete permissions too.

using stored procedures is well known best practice that the majority of
developers know and understand; not using them is actually a throw back to
the 80's and early nineties where we didn't have stored procedures and had
to embed everything in the program thus losing any sort of decoupling and
flexibility - I remember this from my pl/1 cics, db2 days.

the other problem that your proposed method faces is that you lose
encapsulation of change - instead of having a very small unit of change ie
the stored procedure you have to check the application component which makes
testing and change control a dam site more expensive - these are all well
known in the industry - I'm suprised someone like yourself who makes lots of
noise doesn't understand this.

"sqlguru" <sqlg...@xxxxxxxx> wrote in message

news:0d938100-e8f5-4f3e-bdc4-20edbbbcf2a1@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Jun 15, 8:37 am, "Tony Rogerson" <tonyroger...@xxxxxxxxxx> wrote:

> Interesting, so....

> >> string sql = "SELECT * FROM [users] WHERE id = @id;";
> >> IDataReader reader = DbUtility.GetReader(sql, "@id", id);

> When the users connects up to the database using Report Builder, Excel > or
> any other product that can execute queries they can get all the rows and
> columns back from the [users] table.

> Hell - what; now that really is secure!

> Stored procedures everytime where you need security.....

Tony, you let your lack of experience talk for you. I don't use "small
business tools" like Access, Report Builder, or Excel so I can't speak
for that.
We have enterprise applications that go through a standard DAL (dal
per database, we get full object oriented properties). We don't use
"small business report tools", we use enterprise industry standards
like crystal reports that already make use of already well defined DAL
methods. The reports are already well defined for the end-user, this
isn't Access...the end user should not be "creating" reports in an
enterprise environment.

The idea of using stored procedure for security is just a "cowboy
coder" technique. It's for beginner-level DBA's who have no idea about
server hardening or lockdowns so they resort to 1980s standards.
You're better off powering down your database servers.

Tony, are you aware of FIPS standards? I think not. Your definition of
security applies to small buisiness, not enterprises. You cannot use
Excel and connect to a database server, ever heard of secure tunnels
and other secure protocols? Why would a non-developer (aka end user)
have secure tunnel access? Do you actually give out the database
password to your end-users, Tony "Randy" Rogerson?

Also, let's say we use stored procedures. What is preventing a
developer/dba (there are hybrids right?) from bypassing all the stored
procedures? Again, your lack of experience and working with Access
constantly is showing.

Usually, most database structure changes require application changes
at some point. Sure you can use views on top of views to the nth-
degree causing mass confusion in documentation. Well, go work on your
access applications, the real developers/dbas are busy.

.

Relevant Pages

  • Re: Identity Column
    ... lets recap because you have totally missed the point which is the fact that any user can just strike up and connect to your database server - a couple of months ago a very widely effecting virus took advantage of EXACTLY how you recommend people code. ... using stored procedures is well known best practice that the majority of developers know and understand; not using them is actually a throw back to the 80's and early nineties where we didn't have stored procedures and had to embed everything in the program thus losing any sort of decoupling and flexibility - I remember this from my pl/1 cics, ... business tools" like Access, Report Builder, or Excel so I can't speak ...
    (microsoft.public.sqlserver.programming)
  • which one is correct. !
    ... You are a database developer for your company's SQL Server 2000 database. ... You update several stored procedures in the database that create new ... end-of-month reports for the sales department. ...
    (microsoft.public.sqlserver.clients)
  • Re: Identity Column
    ... END USERS DO NOT CONNECT TO YOUR DATABASE, THEY ARE NOT AWARE THERE IS ... I am not clear how this makes the use of stored procedures "useless"... ... Also in your ideal world maybe all the reports ... every company can afford a dedicated report writer, and sometimes the DBA is ...
    (microsoft.public.sqlserver.programming)
  • Re: Identity Column
    ... any user can just strike up and connect to your database server - a couple ... the 80's and early nineties where we didn't have stored procedures and had ... like crystal reports that already make use of already well defined DAL ... Excel and connect to a database server, ...
    (microsoft.public.sqlserver.programming)
  • Re: Complex Database Transactions
    ... My take is that stored procedures can still offer a performance improvement in many cases. ... "As a database is changed by such actions as adding indexes or changing data ... A much better option would be to have the database server compile the query, ...
    (microsoft.public.dotnet.framework.adonet)