Security - Hit or Miss

Question

Security - Hit or Miss

Fraggle-805517

SSCrazy Eights

Points: 8907
More actions
June 16, 2011 at 7:23 am

#238174

Hey everyone, trying to figure out something with security.
Here is the code I am running on the user database.
CREATE ROLE [DatabaseRole] Authorization dbo;
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [ByDesignRole];
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [ByDesignRole];
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE TO ByDesignRole;
GRANT ALTER TO ByDesignRole;
DENY VIEW DEFINITION ON SCHEMA :: [SYS] TO ByDesignRole;
CREATE USER [DatabaseUser] FOR LOGIN [usrLogin] WITH DEFAULT_SCHEMA=[dbo];
EXEC SP_ADDROLEMEMBER N'DatabaseRole' , N'DatabaseUser'
EXEC sp_addsrvrolemember N'usrLogin', 'BulkAdmin'
The issue is that in some of the databases that I manage, this works perfect. Kills the user ability to see other databases and server level information. It also doesn't allow users to create or drop objects.
However, on other database, it doesn't work. Can't say why, it just doesn't. I can create and drop tables with the above security permissions, see sys.databases and other server information that other databases are not allowed.
The logins being used only have "Public" and "Bulk Admin" rights.
Any thoughts on where I can look to find out why this code works on some databases and not others. I know I have to be missing something, but I just cannot seem to see it.
Thanks,
Fraggle

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

1. Granting the ALTER to the schemas in question doesn't do anything. You really shouldn't change that.

2. Nothing in these permissions grants CREATE. Likely there are other permissions at play. If it's a Windows user, check to verify it isn't a member of a group with permissions to do these things. Also check explicit permissions assigned to the user.

3. Nothing here block the ability to look at sys.databases or any of the server information. Blocking VIEW DEFINITION on the sys schema is something that is already in place if the user is only a member of public and bulkadmin, so you haven't effectively done anything.

K. Brian Kelley
@kbriankelley

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 2

These are all for sql logins so I don't understand how I could possibly have inherited permissions. The statements that you see are the statements I ran across ever single database.

So why do some work and others don't? Is there a script that I can run that would allow me to see all of the rights that a user has in the database and all of rights of the login?

Thanks for the notes on the other two. The reason for the Alter is that I needed the Role to be able to Truncate some tables until we could alter/remove the code or until we make the decision to only grant rights to those tables.

Thanks,

Fraggle

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

I'm talking about ALTER on the db_datareader and db_datawriter schemas. There shouldn't be anything in them. Therefore, that set of permissions is unnecessary. I understand the general ALTER. However, realize that with that, they should have the potential to drop any object in the database unless you write a DDL trigger to stop them.

To see other permissions, query sys.database_permissions. Also look for permissions granted to the public role.

K. Brian Kelley
@kbriankelley

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 4

K. Brian Kelley (6/17/2011)
I'm talking about ALTER on the db_datareader and db_datawriter schemas. There shouldn't be anything in them. Therefore, that set of permissions is unnecessary. I understand the general ALTER. However, realize that with that, they should have the potential to drop any object in the database unless you write a DDL trigger to stop them.
To see other permissions, query sys.database_permissions. Also look for permissions granted to the public role.

I have taken a look through the system. Here is what I come up with.

Running the following command gives me the Principal_ID for both the DatabaseUser and DatabaseRole that have been created.

SELECT name, principal_ID

FROM sys.database_principals

WHERE name in ('DatabaseRole', 'Public', 'DatabaseUser')

0 = public

7 = DatabaseUser

9 = DatabaseRole

So then I run the next statement for each of the ID's above.

SELECT distinct grantee_principal_id, class_desc, permission_name, state_desc

FROM sys.database_permissions as p

WHERE grantee_principal_id in (0, 7, 9)

ORDER BY grantee_principal_id

Which gives me the following result set:

grantee_principal_idclass_desc permission_namestate_desc

0 OBJECT_OR_COLUMNDELETE GRANT

0 OBJECT_OR_COLUMNEXECUTE GRANT

0 OBJECT_OR_COLUMNINSERT GRANT

0 OBJECT_OR_COLUMNREFERENCESGRANT

0 OBJECT_OR_COLUMNSELECT GRANT

0 OBJECT_OR_COLUMNUPDATE GRANT

7 DATABASE CONNECTGRANT

9 DATABASE ALTER GRANT

9 DATABASE DELETE GRANT

9 DATABASE EXECUTE GRANT

9 DATABASE INSERT GRANT

9 DATABASE SELECT GRANT

9 DATABASE UPDATE GRANT

9 SCHEMA VIEW DEFINITIONDENY

So, If I read this correctly, then the DatabaseUser should not be able to CREATE anything. Yet I am able to create tables, functions, view, etc. I understand now why I can drop, but why can I create?

Thanks,

Fraggle