Kbase P67686: How to prevent SQL-92 clients from reading meta schema tables ?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
16/10/2008 |
|
Status: Unverified
GOAL:
How to prevent SQL-92 clients from reading metaschema tables ?
GOAL:
How to prevent SQL-92 users from listing the database tables ?
GOAL:
How to revoke select privilege on a metaschema table in SQL-92 ?
FACT(s) (Environment):
Progress 9.x
OpenEdge 10.0x
FIX:
By default, all SQL-92 (ODBC/JDBC) users can query metaschema tables (except the "_user" table that's only accessible for users with DBA privilege).
You can prevent SQL-92 clients from seeing any metaschema table, thus preventing them from listing the table names, by doing the following:
- Create the users in the database, including the SYSPROGRESS user (with a non-blank password).
- Connect with the SYSPROGRESS user account.
- Grant the required privileges to the other users.
- Now revoke the following privileges to prevent access to the metaschema tables:
REVOKE SELECT ON PUB."_file" FROM PUBLIC;
REVOKE SELECT ON SYSPROGRESS.SYSTABLES FROM PUBLIC;
REVOKE SELECT ON SYSPROGRESS.SYSTABLES_FULL FROM PUBLIC;
NOTE:
- Revoking privileges from the user would not be enough, you have to revoke them from "public". You can later grant the privilege back to selected users if you like.
- This only works when using the SYSPROGRESS user to Revoke the privileges.
- The SYSPROGRESS user should not be used under normal circumstances, only for special cases like this.
For general information on SQL-92 permissions and security, please see Solution 20143 .