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
SCHEMA2, and create
SCHEMA1, and granted
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
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.