Kbase 19170: How to grant privileges to SQL-92 users from SQL explorer?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/13/2009 |
|
Status: Verified
GOAL:
How to grant privileges to SQL-92 users from SQL explorer?
GOAL:
Steps on how to grant privileges to SQL-92 users?
GOAL:
How to verify SQL-92 users privileges
FACT(s) (Environment):
Progress 9.x
Progress SQL92
All Supported Operating Systems
FIX:
For SQL-92 users, if privileges are not granted properly, you can receive the error:
[MERANT][ODBC PROGRESS driver][PROGRESS]Access denied
(Authorization failed)
after trying to connect with ODBC to a Progress Database with a user_ID
Firstly, you have to use GRANT command to give any kind of privileges to users (DBA,RESOURCE, SELECT, INSERT, etc..).
But there is a detail not clearly stated: make sure you have the "Auto commit transactions" option in the "Connection Control" window (View menu->Options) checked, otherwise you must run the COMMIT command after granting the users. It's not necessary to run it right after the grant command, but before closing the database connection. Otherwise, when you connect to the database again, you will figure out that the privileges are not set.
Below is an example how to use it:
GRANT DBA TO myuser;
GRANT SELECT ON customer TO myuser2;
COMMIT;
To make sure that the commands were properly executed, connect again to the database using SQL Explorer Tool and look at:
-SYSDBAUTH view from SYSPROGRESS schema if you ran grant DBA/RESOURCE:
SELECT * FROM sysprogress.sysdbauth;
-SYSTABAUTH view from SYSPROGRESS schema to specific table permissions. For example:
SELECT * FROM sysprogress.systabauth WHERE grantee = 'myuser2';