Solved

High-level description of SQL and its security

Posted on 2015-01-19
6
67 Views
Last Modified: 2015-02-17
I have a 2 page description of Working With Sql Server attached (about 380 words).

Could you please do some sanity check on its content to comment as to how I could improve it.

Note: This is bird-eye view and introductory to SQL Serever prepared for person with little programming skills and interest in the subject.
WorkingWithSqlServer2.docx
0
Comment
Question by:Mike Eghtebas
6 Comments
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 200 total points
Comment Utility
eghtebas, your security section still not right. The analogy with a restaurant still not good.
The authentication methods are two only (you can see that if the security properties of an instance) and ALL of them needs privileges at instance level and database level, otherwise you wouldn't be able to access data.

I recommend you to start with the definition of a Database Management System, followed by the definitions of Instance, Database, Login, User and Schema. I think when you have these concepts in your mind then will be more easy to write the rest of the document.

Good luck.
0
 
LVL 33

Author Comment

by:Mike Eghtebas
Comment Utility
re:> your security section still not right
I may have many things wrong. What will be helpful is take a sentence or two and write it they way it is right. By just saying it is not right, is not helpful at all.

re:> The analogy with a restaurant still not good.
I intend to keep this analogy but modify it in such a manner that makes sense. You are welcome to help me on that if you can come up with a better way of saying it. Or, make a better analogy.

re:> The authentication methods are two only
Yes, my bad. They are:
1. Windows level.     <-- authentication
2. Sever Level.          <-- authentication
3. Database Level.   <-- what you will name this? permission?  privilege?

What is the a common word (or category) for all three?

re:> definition of a Database Management System?
What about it. What is that you want me focus about this? Do you have a particular point you want me to focus on it?

re:> followed by the definitions of Instance, Database, Login, User and Schema.
The same as above.

re:> when you have these concepts in your mind
I think I have them in my mind hence this write-up. I probably miss some crucial aspects of them but you never give a clue in your post. I just wonder what exactly you mean. It will be helpful to give me something to go by.

I know that is not probably your style, but you are not telling me what is the problem just hinting there is problem.

Regards,

Mike
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 200 total points
Comment Utility
Let me begin from the end.

I know that is not probably your style, but you are not telling me what is the problem just hinting there is problem.
It's your document and should be you to write it and that's why I'm avoiding to give you the fish. I prefer to show you how to fish.

What is that you want me focus about this? Do you have a particular point you want me to focus on it?
The document is for you or for others? I had the idea that the document should be a startup for someone that want to know more about SQL Server.

1. Windows level.     <-- authentication
 2. Sever Level.          <-- authentication
 3. Database Level.   <-- what you will name this? permission?  privilege?
Ok. Here is some kind of confusion and maybe mine. I was talking about SQL Server authentication only as you can see from the Server Properties:
SQLAuthenticationMode.PNG
Or, make a better analogy.
The analogy that an EE gave you in your previous question was good (building access).
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 200 total points
Comment Utility
Hi Mike,

I also do not know why you cling to your restaurant example? Normally there is no kind of authentication in a restaurant and other things which do not fit with this analogy I explained in my last answer to you.

If you tell someone about SQL Server authentication you cannot mix up authentication levels of Windows and SQL Server. You say in your document "Typical SQL Server Authentication Modes" - and that's wrong. As Vitor already told you, there are really only two variants to login to a SQL Server, The Windows method and the mixed mode, where you can use SQL Server login additionally to Windows login.

The only difference between these two is that the Windows login don't need to enter login data again - but at some point you need to enter them: when you login to Windows. The Active Directory creates a security ticket (simply spoken) and this is forwarded to SQL Server if you want to login there, so SQL Server asks Active Directory if that is a valid ticket and if yes, it lets you in - BUT ONLY (!!) if your Windows account was inserted at the instance level as a valid login for THIS instance. If you remove any Windows user from the instance level and you do not have an alternate "sa" SQL Server login nobody would be able to login into SQL Server anymore (although I do not believe that SQL Server lets you remove the last account, but only to show you the difference).

So in case of SQL Server login the only difference is that you additionally have a user maintenance on the SQL Server itself which is not managed by Active Directory and not known by Active Directory. If you need the same username on another instance you would need to create a new user with the same name, but they have nothing to do with each other, although they are named the same. In opposite the Windows account is globally known in the Active Directory, you don't create a new user in SQL Server, you only add a known account managed by Active Directory. So if you lock the account in AD, it is locked in all SQL Servers which uses it. If you lock a SQL Server user in SQL Server, it is only locked on this specific instance, not anywhere else.

You ALWAYS need a user on the instance level, otherwise this user can't do anything else on this SQL Server instance. You additionally need a database login assignment to get access to this specific database. To make it even more complex, you can also add a pure database user without login rights which only exists on database level but not on instance level, this can be used i.e. for "WITH EXECUTE AS" stored procedures. But this user would not be able to login to the database as he doesn't exist on instance level and has no login rights - you see, SQL Server authentication levels are complex, try to find a restaurant where you can sit in without going through the entrance...;-)

"SSMS is always is installed on the local computer." - that's of course also wrong. You CAN install SSMS during installation process on your local computer, but you don't need to do that, and all network users of a SQL Server of course do not have SSMS to access it - that's the normal case.

Indeed you would normally have a database developer which creates the tables and views (=saved queries), this one would give access to this views to the external users and then a user can select one of this views and executes it - or create a dynamic SQL string to query such view in the limits the view offers. The user is normally not allowed to create own views or access tables directly - and he also should not be allowed to work with SSMS in most cases. But in a good secured SQL Server he can also install SSMS and get only the views/other resources he is allowed to use.

I think what Vitor said above is that you should try to get a second Windows account in your network and an independent computer from yours and then try to access your SQL Server again with the other Windows account on this computer. You will see that this is not possible. If you try to configure SQL Server in the way that you can access SQL Server with this account from this computer you'll see how that works.

Cheers,

Christian
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 100 total points
Comment Utility
Hi,

Please clarify the intended audience - if no interest in programming, why the detailed explanation of SSMS? If no interest in programming then I wouldn't be letting them loose with SSMS myself.

How will this user interact with SQL? Via an application? Via assistants as this individual is a c-level executive and doesn't touch a computer, but need them to sign off on the budget? I think that you are trying to do too much in too little space. Your paper seems designed towards either someone from a different database flavour (or really old SQL) starting work on a modern version and needs to know where all the tools are etc.

"SSMS is always installed on the local computer."

This is a sweeping generalisation that needs expansion and clarification. Such as: In our shop, we install SSMS on the local workstation as needed, and try to avoid using the one installed on the server; or As our servers use Sever Core installations, the server lacks a gui to support SSMS, and so we need to run SSMS from our workstation.

HTH
  David
0
 
LVL 33

Author Comment

by:Mike Eghtebas
Comment Utility
Who are the users for this writing are:
- Myself to make sure I understand it.  <<< mainly this
- My college kid and friends.
- Number of my ex-project managers (now friends) who could really use some knowledge about SQL Server.
- All above who also need to use some basic T-SQL.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now