April 23, 2012 at 10:30 am
I have never understood SQL Server security - despite reading loads of stuff on it.
I look at a SQL Server database in Sql Server Management Studio and I see a folder called Security with a Users folder and a Roles folder inside.
In the Users folder - if I right click on a User and select properties, on the General page of the window that opens I see:
UserName: CRReports
LoginName - radio button - ticked but grayed out
Certificate Name - radio button - grayed out
Key Name - radio button - grayed out
Certificate Name - radio button - grayed out
Without login - radio button - grayed out
Why are the radio buttons grayed out?
The User in question seems to have a Username and Password - which is being used in Crystal Reports to connect to Sql Server.
Where can you see and edit the username and password for a User? Where are they set up?
Then it says ... Default Schema dbo
What is the 'Default Schema'?
Underneath that it says Database Role Membership: ... and several roles are ticked.
So, a User can be a member of a Role?
A 'Role' is a collection of Users?
What is a 'Role'?
The second page shows Securables.
What are Securables?
If a Role has Select permission for a View - does that mean that any User who is a member of that Role has Select permission for that View?
If you can be bothered to answer any of those questions I'd be most grateful. I use Sql Server every day and I really ought to know about permissions ... but I read articles on it and the language used makes me glaze over. So, any answers phrased for an idiot please.
Edit: Just looking again ... there is a Role called db_owner and a Schema called db_owner? Is this right? Why do they have the same name?
April 23, 2012 at 10:43 am
Why are the radio buttons grayed out?
They're grayed out because you cannot change the properties either at all or from this window. This is the "database" user, not an SQL login. The user is associated with the login, but is more specific to the database.
Where can you see and edit the username and password for a User? Where are they set up?
On the security->logins for the instance
What is the 'Default Schema'?
This is the default schema for the user. A schema is a container for database objects.
So, a User can be a member of a Role?
yes
A 'Role' is a collection of Users?
No
What is a 'Role'?
It is a set of permissions that act like a template. You assign a user to a role and they get the permissions in that role.
The second page shows Securables.
What are Securables?
To be honest, I don't know because I have not had to use them and have not gotten to that part of my book yet. Plenty of info out there though.
If a Role has Select permission for a View - does that mean that any User who is a member of that Role has Select permission for that View?
exactly
Jared
CE - Microsoft
April 23, 2012 at 10:55 am
Yeah, the documentation leaves a bit to be desired. Lots of circular definitions, last time I checked.
A "role" is a set of permissions. For example, "db_datareader" is a role, and has permission to read data from tables (Select statements) but not to update/insert/delete data. Role "db_datawriter" has permission to update/insert/delete. Two roles. Some users (or groups, just to make it more confusing) are members of both of those roles, which means they can read and write data anywhere in the database.
A "securable" is an object in the database. A table, view, stored procedure, et al. Permissions can be granted on these. For example, could grant a user the right to execute certain stored procedures, but not do do anything else in the database.
You can create custom roles and grant them custom permissions, as well. I like to create a role "db_executor" and grant it the right to execute all stored procedures in the database. Very useful for keeping an account in the API (the procedures) without granting it any rights to the tables the procs work on. Good security for web applications and the like.
"Users" can either have specific permissions, or can be made members of roles. Groups of users can be created as well, and then either given specific rights or added to specific roles. For example, a "user" called "WebApplications" could be created, and made a part of the "db_executor" role, which would give it access to stored procedures but not to tables. That user would then be used by web applications, and would help prevent SQL injection and other attacks.
Schemas used to be considered a security item (in SQL 2000), but really weren't all that useful for that. What they do is separate objects in the database. I find them useful for separating multiple applications that are housed in the same database. Tables they will all use are in "dbo", but separate tables, procs, et al, are in separate schemas. Makes it easy to tell what application(s) use a stored procedure, and to sort objects by application. For example, I'm working with some devs who are building a multimedia gallery for our website, and all database objects directly related to that are in schema "MMG" (MultiMedia Gallery). Gives them access to the tables for the rest of the site, keeps the whole site in one database (nice for server migrations, deployments from QA, etc.), and works nicely for self-documentation.
You can grant permissions, to users or roles, at the schema level. I have one user with ad hoc access to a whole schema related to her department. She writes queries in Management Studio, has permissions to view (but not modify) code and table structures, and so on, in that one schema, but not for the rest of the database. The rest of the database doesn't even show up in management studio for her. Generally, this isn't a good idea, but it's been necessary and useful in this case.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 23, 2012 at 11:27 am
Securable allow us to save database objects that are otherwise given permissions (more like deny) by default. For Example, I can secure few of my tables from as user with db_ownner role which he will be having access otherwise.
SELECT * FROM DBO.ASPNET_USERS
EXECUTE AS USER = 'TEST'
SELECT * FROM DBO.ASPNET_USERS
REVERT
SELECT * FROM DBO.ASPNET_USERS
Output:
UserId
------------------------------------
6A8127BD-E56F-4732-9A65-2D708C339564
(1 row(s) affected)
Msg 229, Level 14, State 5, Line 3
The SELECT permission was denied on the object 'aspnet_Users', database 'My R&D', schema 'dbo'.
UserId
------------------------------------
6A8127BD-E56F-4732-9A65-2D708C339564
(1 row(s) affected)
April 23, 2012 at 12:40 pm
Thank you to everyone who has replied ... the fog clears a little and, it seems, my lack of understanding is not all down to me.
I wonder if I might indulge the forum by, over the next day or two, creating a new database and asking the security questions one at a time as I do it ... to try and really get this clear in my head.
So, to kick off ...
In SSMS I right click databases and select to create a new database. I am asked to give the database a name and to select the 'Owner'.
The box for the owner has 'default' in it and if I click to expand - it appears I have to select a 'Login'
If I go to Databases | Security | Logins ... I see myself listed there as MyDomain\MyUserName and others like, for example, MyDomain\SQLServer ... and NT Authority\System
So, I guess my questions here ar:
If I leave the owner as 'default' that makes me the owner?
The owner can set any and every permission to do with the database?
If I select the Login 'MyDomain\SQLServer' as the owner - what would that mean? That I would not be able to access any and every aspect of the database?
April 23, 2012 at 1:18 pm
If you select the default, that'll be you. Whatever credential you used to connect to SQL Server with. Usually, your Windows login.
That's a bad idea, because what happens if you leave the company and your domain account has to be deactivated?
So, pick either an account created specifically for that purpose (like a "MyDomain\DBA" account, not tied personally to you but to the job), or an SQL Server login (if you are using those).
Regardless of what account is the "owner", any account with Sys Admin rights will have full access to the database. FULL rights. Data access, security access, object access, you name it. If your account is a Sys Admin account, that includes you. Sys Admin is a "role", as discussed earlier, and it can do anything on the server at all.
If you aren't a Sys Admin, then what you can and can't do in a database that you don't own will depend on what role(s) you do have assigned to you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 23, 2012 at 1:26 pm
Also, look at the difference between server roles and database roles. There are different ones for each (some similar though). Obviously, one is at the server level, independent of the databases. The other is at the individual database level.
Jared
CE - Microsoft
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply