Kbase P161042: SQL: How to enable users to execute SQL queries against the _aud-audit-data table?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
11/22/2010 |
|
Status: Verified
GOAL:
SQL: How to enable users to execute SQL queries against the _aud-audit-data table?
GOAL:
How to enable a user to successfully SELECT * FROM PUB."_aud-audit-data" table?
GOAL:
How to GRANT the AUDIT_INSERT, AUDIT_ARCHIVE , AUDIT_ADMIN and the SELECT privileges to users?
GOAL:
How to create a database AUDIT Administrator,Inserter or Archiver using SQL?
FACT(s) (Environment):
Windows
OpenEdge 10.2x
FIX:
To allow a user to query the PUB."_aud-audit-data" table, GRANT him/her both the AUDIT_ARCHIVE privilege and the SELECT privilege. The following step by step procedure demonstrates how to do that against the sports2000 demo database. Although a Windows machine and an ODBC driver are used in the following procedure, a UNIX machine and a JDBC driver may be used by simply replacing the ODBC WinSQL client with the JDBC client of your choice:
1. Login to a Windows machine that has OpenEdge 10.2x installed.
2. Copy the Audit.st, CreateUsers.sql, CreateUsers.p and the GrantPrivileges.sql files attached in the notes of this solution into your working directory.
3. Start a Proenv session:
Start > Programs > OpenEdge > Proenv
4. Create a copy of the sports2000 database:
prodb sports2000 sports2000
5. Add the audit storage areas to the database:
prostrct add sports2000 Audit.st
6. Audit-enable the database:
proutil sports2000 -C enableauditing area AuditData indexarea AuditIndex
7. Serve the sports2000 database:
proserve sports2000 -H localhost -N tcp -S 12345
8. Either execute the SQL statements in the CreateUsers.sql file as the default DBA who created the database or execute the 4GL/ABL procedure in the CreateUsers.p file to create the initial set of users: sysprogress, administrator, inserter, archiver, reporter and JohnDoe:
prowin32 sports2000 -p CreateUsers.p
9. Create a system DSN for the database using the Windows ODBC Data Source Administrator with the following values:
a. Driver: Progress OpenEdge 10.2B Driver
b. Data Source Name: sports2000
c. Description: sports2000
d. Host Name: Localhost
e. Port Number: 12345
f. Database Name: sports2000
g. User ID: sysprogress
h. Password: sysprogress
i. Test the connection to ensure the DSN definition is valid using 'sysprogress' as password.
10. As the DBA sysprogress user, execute the SQL statements in the GrantPrivileges.sql file.
11. Now, any of the following users: administrator, archiver, reporter and JohnDoe can successfully execute the SQL statement: SELECT * FROM PUB."_aud-audit-data"
12. Note that neither the sysprogress nor the inserter users can execute: SELECT * FROM PUB."_aud-audit-data" because neither has been granted the SELECT and the AUDIT-ARCHIVE privileges.
13. To allow additional users to query the PUB."_aud-audit-data" table, simply have a DBA user GRANT them both the AUDIT_ARCHIVE and the SELECT privileges on that table.