Because the PUBLIC role has database and object level permissions it is imperative that any newly created SQL Server login that you wish to have read-only access to a database be placed in the db_denydatawriter database role in addition to the db_datareader database role to explicitly override INSERT, UPDATE, DELETE permissions granted to all database users via their membership in the PUBLIC role.

Note: Due to the way E1 grants object permissions via PUBLIC, it is not technically necessary to place a database user in the db_datareader role. However, doing so will make permissions viewing easier.

It is also important that a newly created SQL Server login be explicitly denied the create table permissions in the database at the user level if table creation is not desired.

Two ways to deal with this:

Continue to use Public security and

1) remove SQL Server guest user from each database (SQL 2000) or revoke CONNECT permission (SQL 2005/SQL 2008) and
2) be sure to place ad-hoc (non-E1 app) users in the db_datareader and db_denydatawriter roles for each database
3) Deny CREATE TABLE to new SQL Login

1 comment:

Jeff, I've always been curious about the default security setup in SQL. In Oracle we have the same issue, but there's no easy solution because you can't effectively remove an Oracle account from PUBLIC. What I ended up doing on the Oracle side is writing a security utility that sniffs the E1 database and removes the PUBLIC privileges. It amazes me how many clients don't ever remediate this huge security hole.