Once OEAG is enabled, _user table is not used to authenticate users. Below example illustrate how SQL permission works on an OEAG enabled database:
Database: _user is empty, OEAG is enabled.
To better illustrate this the default dba account "sysprogress" will be used in this example and sysprogress is an OEAG authenticated user.
Log in as default SQL DBA and OEAG authenticated user "sysprogress":
proenv>sqlexp sp -S 4500 -user sysprogress@<domain> -password <password>
SQLExplorer>select * from sysprogress.sysdbauth;
GRANTEE DBA_ACC RES_ACC GRANTEE_DOMAINID
-------------------------------- ------- ------- --------------------
hzhu y y 0
SYSPROGRESS y y 0
Now we try create an user from SQL to illustrate that _user table is not used once OEAG enabled:
SQLExplorer>create user 'sqluser1','sqluser1';
SQLExplorer>commit;
SQLExplorer>grant dba to sqluser1;
SQLExplorer>commit;
SQLExplorer>SELECT * FROM sysprogress.sysdbauth;
GRANTEE DBA_ACC RES_ACC GRANTEE_DOMAINID
-------------------------------- ------- ------- --------------------
hzhu y y 0
SQLUSER1 y 0
SYSPROGRESS y y 0
Now login as newly created sqluser1:
proenv>sqlexp sp -S 4500 -user sqluser1 -password sqluser1
Error: [DataDirect][OpenEdge JDBC Driver][OpenEdge] Access denied(Authorisation failed). (8933)
Above has illustrated the authentication.
Below illustrate that authorization works the same as before:
Log in as an OEAG authenticated user but no DBA rights:
proenv>sqlexp sp -S 4500 -user test@<domain> -password <password>
SQLExplorer>select * from sysprogress.sysdbauth;
[DataDirect][OpenEdge JDBC Driver][OpenEdge] Access denied (Authorization failed) (7512)
Test can log in, because it is authenticated by OEAG, but it does not have have dba privilege.
Use dba to grant user test dba privilege:
SQLExplorer>grant dba to 'test@<domain>';
SQLExplorer>commit;
Now user test should be a dba:
proenv>sqlexp sp -S 4500 -user test@<domain> -password <password>
SQLExplorer>SELECT * FROM sysprogress.sysdbauth;
GRANTEE DBA_ACC RES_ACC GRANTEE_DOMAINID
-------------------------------- ------- ------- --------------------
hzhu y y 0
SQLUSER1 y 0
SYSPROGRESS y y 0
TEST y 0
NOTE:
For default SQL DBA privilege either create sysprogress under the authprovider or grant a OEAG authenticated user <user>@<domain> DBA privilege.
Example:
test@<domain> is an OEAG authenticated user as shown above.
Before enabling OEAG on database.
grant dba to 'test@<domain>';
commit;
