Sql-server – How to test permissions on an empty database

permissionssql server

It's usual in an organization I work in that I need to request a database creation to the DBA group (I am a developer). As such, they will also create a user for me to use and a database dedicated to my application.

When they do so, they ask which types of permissions and I answer.

When the work is done, they request that I test that everything is fine as to give my approval on the configuration. However, at this point, the database is completely empty. I can easily test for connectivity and access permissions, but how should I test my own read/write/execute permissions into a specific database, if it´s just been created and it is completely empty, using a non-admin user?

If the database wasn't empty I would try selecting data from a table and updating it.

Best Answer

The question could use some clarification. SQL Server security is the same whether or not the table is empty. You have server roles, database roles, and explicit permissions. You are asking 'how can I test execute permissions on a database that has no objects to execute'.

Well the answer is you have nothing to test if the DB is empty, that should be obvious. create objects then test it, and then remove the objects. Test it on a dev box. If you're worried that you'll forget to remove all objects, backup the DB, then do your tests, and restore it. If you like, restore the DB as a different name, do all of your testing on there, then drop the newly created test DB. I'm not sure what you're asking.

Final answer remains that you cannot test permissions on objects if you have no objects to test.

I will provide a script or two at the end to help you with these steps.

Step 1: Look for users with SA rights.

What you would look for first would be SA equivelent users. Any SA user will have full access to every single object, and if they don't, they can grant it to themselves.

Step 2: Look for users with the DB_Owner role Users granted the DB_Owner role will be able to perform any task in the database. You could DENY them access to certain features but they can just GRANT themselves access to it again.

Step 3: Audit what users are in the database You will want to look at the 'security' settings of each user in the database. This could get very in depth as you can look for indvl permissions on each object and this would be required if you were taking over a database. In your case, there are no objects and you get to create them yourself, thus we don't have any object level permissions to go through. You will want to check what roles each user is in. DataReader will be able to read anything in the DB unless explicitly blocked. DataWriter will be able to write anything. Execute role lets them execute sprocs.

Step 4: Continue to monitor after objects are created Now comes the hard part. What happens when you go live and a sysadmin grants themselves rights to the machine/db? What if you have the option to have start up stored procs execute? If you're on 2008R2 and below, the NTAUTHORITY user can always be used as a backdoor. This is where real security auditing and security policies are needed but luckily it's out of scope of this question.

Check out: http://www.mssqltips.com/sqlservertip/1881/sql-server-security-audit-report/

His script will cover almost everything you're looking for.

Also this one is very good too, but test it out first: http://gallery.technet.microsoft.com/scriptcenter/SQL-SERVER-SECURITY-AUDIT-eb5b0fd5/view/Discussions

If you want to do some research on your own, search for "SQL Server Auditing" or "Object Level Auditing SQL Server".

Does this info help? Do you have any follow up questions?

Follow up edit to address permissions under the current user context:

In that case you'll want to use this:

SELECT * FROM fn_my_permissions(NULL, 'SERVER');


USE DBName;
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO

The first one will tell you your permissions on the server, the 2nd one will tell you your permissions on the database.

Click here for more easy to read info.

Related Solutions

SQL Server – Hierarchy of Permissions for Schema

Quick answer: For developers, you can GRANT CONTROL on that schema.

Background:

To see what can be GRANTed: GRANT Schema Permissions
To see what each permission means: Permissions Naming Conventions

The intersection of these two give the schema permission meanings:

CONTROL implies the rest and is the highest permissions of any securable
SELECT, DELETE, INSERT, UPDATE is DML on objects in that schema
EXECUTE to run scalar UDFs and Stored Procedures in that schema
ALTER, REFERENCES is DDL (CREATE, ALTER, DROP) on objects in that schema
VIEW DEFINITION lets folk see the code and definitions

Except for CONTROL, these are mostly orthogonal to each other: folk can EXECUTE code or SELECT, from a view but not see the definition (VIEW DEFINITION) of these.

You also have the database and server permissions which are implied higher up then CONTROL on schemas: see Permissions Hierarchy

PostgreSQL user permissions

This should work (as superuser):

CREATE ROLE rwc;    -- read/write/create user
CREATE SCHEMA foo;
ALTER  SCHEMA foo OWNER TO rwc;
-- GRANT ALL ON SCHEMA foo TO rwc;  -- alternatively

CREATE ROLE r;      -- read user
GRANT USAGE ON SCHEMA foo TO r;

CREATE ROLE rw;     -- read/write user

GRANT r TO rw;
GRANT rw TO rwc;

ALTER DEFAULT PRIVILEGES FOR ROLE rwc IN SCHEMA foo GRANT SELECT ON TABLES TO r;
ALTER DEFAULT PRIVILEGES FOR ROLE rwc IN SCHEMA foo GRANT USAGE ON SEQUENCES TO r;

ALTER DEFAULT PRIVILEGES FOR ROLE rwc IN SCHEMA foo
GRANT INSERT, UPDATE, DELETE ON TABLES TO rw;
ALTER DEFAULT PRIVILEGES FOR ROLE rwc IN SCHEMA foo
GRANT SELECT, UPDATE ON SEQUENCES TO rw;

You probably missed one of these steps.
You may need do adjust existing objects additionally since default privileges are only granted to new objects.

Best Answer

Related Solutions

SQL Server – Hierarchy of Permissions for Schema

PostgreSQL user permissions

Related Question