PostgreSQL user permissions

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.

It could be as simple as:

GRANT SELECT ON SCHEMA::[sitename] TO [user];

If there are procedures and functions you will also need:

GRANT EXECUTE ON SCHEMA::[sitename] TO [user];

The full list of permissions applicable at the schema level is listed in the documentation:

• GRANT Schema Permissions (Transact-SQL)

You can also set the default schema for any user, which may be helpful in some scenarios (for example, if they create tables without explicitly specifying the sitename schema):

ALTER USER [user] WITH DEFAULT_SCHEMA = [sitename];

Note that DENY overrides GRANT (and also overrides role membership rights, including db_owner), so check to be sure someone hasn't inadvertently blocked access to the schema(s). You can start your investigation if you find any rows here:

SELECT * FROM sys.database_permissions
  WHERE class_desc = N'SCHEMA' AND [state] = 'D';

Note that permissions can apply to a user indirectly (e.g. through AD group membership or database roles), so make sure you follow the bread crumbs for any database principal with a relevant DENY...