I'm pretty green to SQL security, so maybe someone can help me. We've got a database application that, when installed, by default grants the public role access to all the tables in the database. When you create an SQL user, it adds the user to the public role, so new users will have access to all tables.
We have a need to connect 3rd party ODBC apps to the database. We created a view that pulls from tables within the same database, and we want the the connection to only have access to the views created. Instead of trying to remove the public role from all of the tables, we decided to use Schemas to apply security.
All the default tables are in SCHEMA1. We placed the VIEW under SCHEMA2, and create USERA denying SELECT to SCHEMA1, and granted SELECT to SCHEMA2. Both SCHEMAS have the same owner.
When I test the ODBC using Access and try to open the VIEW, I receive the following error
The SELECT permission was denied on the object (table name in SCHEMA1)....
I assume it's because I have denied SELECT to SCHEMA1, which holds the referenced tables for the VIEW. If that is the case, how would you restrict access to only specific tables through ODBC?
After I posted this I started googling a little more about revoking the public role permissions from the schema as a possible option, but not tested, but also came across the 'application role'. I still need to read a little more, but wondering if this is the direction I should be heading in.
