Kbase P70813: How to grant access to a user who will act as an ODBC user in read only-mode
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
15/10/2008 |
|
Status: Verified
GOAL:
How to grant access to a user who will act as an ODBC user in read only-mode
GOAL:
Is there any way to run the SQL-92 engine via 4GL code?
GOAL:
How to run SQL statements and commit them
FACT(s) (Environment):
Progress 9.x
OpenEdge 10.x
CAUSE:
The 4GL and SQL are two different engines, as such, there are two different security models. So, in answer these questions, no, you cannot grant access to the ODBC user via the 4GL.
But what you can do is to grant SQL permissions via the cmd line in char mode as detailed below.
FIX:
Using the sports2000 database by way of example:
0) From a Proenv session, create a test database: prodb test sports2000
1) Start the database: proserve test -S 9000 -H localhost -N tcp
2) Create sysprogress and two other users to the _user table: prowin32 test -p _admin.p
Go to Admin: Security -> Edit user list -> add
sysprogress sysprogress sysprogress
user1 user1 user1
user2 user2 user2
[OK]
3) Grant privileges:
- create a file called 'sqlin' with the following content:
grant SELECT ON PUB.CUSTOMER to user1;
grant DBA to user2;
commit;
- Activate the privileges: sqlexp -db test -S 9000 -user sysprogress -password sysprogress -char -infile sqlin
4) Test:
START 2 SQL-Explorers, one for each users:
user1: select * from pub.customer; => result set OK
user2: select * from pub.customer; => result set OK
user1: select * from pub.family; => [JDBC Progress Driver]:Access denied (Authorization failed) (7512)
as expected since user1 only have granted select on pub.customer
user2: select * from pub.customer; => result set OK
as expected
Note that if you do not want to create a file for the SQL command you can GRANT privileges as follows:
sqlexp -db test -S 9000 -user sysprogress -password sysprogress -char -command "grant SELECT on pub.customer to user1"