Kbase P17037: Getting access denied when trying to select from a SQL92 view created in another users ownership are
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
23/01/2008 |
|
Status: Verified
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.1x
OpenEdge 10.x
SYMPTOM(s):
Error 7512 accessing a SQL-92 view
Select from a SQL-92 view created in another users ownership area gets error:
Access denied(Authorization failed) (7512)
[JDBC Progress Driver]:Access denied(Authorization failed)
CAUSE:
The user that is trying to access the view does not have sufficient privilege. Ownership of an area within the SQL-92 schema is tightly linked with privilege.
Example:
User TMP wants access to the Human Resources table for information on employee addresses.
The DBA does not want the salary of the employee visible to the user TMP so the DBA decides to make a view for user TMP that does not include salary.
If the DBA creates a view within the ownership space of user TMP and TMP does not have select privilege on the HR table then TMP will get an access denied warning if trying to use the view.
If the DBA creates a view within the DBA ownership space and then grants select privileges to user TMP on the view then user TMP can see exactly what the DBA chooses through the view even though user TMP does not have any privilege on the underlying table.
The inherent rights of the user are thus tied to all the tables and views within that users ownership area.
Since user TMP never had any privileges on the HR table even though the DBA created a view for the user TMP, because the view was stored in the TMP ownership area that view immediately downgrades to the rights of user TMP.
FIX:
Create views in your own ownership area and grant permissions to other users to use your views or Grant the user sufficient privilege.
1) The user has to have select permission on the view itself.
"GRANT SELECT ON <owner>.<viewname> TO <username>".
It is not enough (and not required) to have permission on tables included in the view definition.
2) Owner of the view (if explicitly defined in "CREATE VIEW <owner>.<viewname>") has to have select permission on all tables included in the view definition.
For example, if a view is created with statement "CREATE VIEW user1.myview AS SELECT * FROM table1, table2", then "user1" is the owner of the view.
If you want "user2" to be able to query this view, you have to grant privileges as follows:
GRANT SELECT ON myview TO user2;
GRANT SELECT ON table1 TO user1;
GRANT SELECT ON table2 TO user1;
COMMIT;
To grant the SELECT privilege to ALL:
GRANT SELECT ON OwnerName.ViewName TO PUBLIC;
COMMIT;