Oracle Customer Engineering & Advocacy Lab (CEAL) Blog covers Oracle Analytics Cloud, Oracle Analytics Server and...
-
On-premise /
OBIEE
January 28, 2014
BI Check with WLST - SQL Datasources part 2
This second post continues with the BIEE security checks using WLST and python. This time, the following information is gathered about BISQLGroupProvider:
* looking for SQLGroupProvider in the configured providers
* retrieving the SQL queries
* retrieving the JDBC source details
* testing the connection pool, for each server that uses it (make sure your BI server is up and running)
* testing the SQL queries directly against the database, with the test.user and test.group provided in the automation.properties file
To run the script:
- Download the files there: //cdn.app.compendium.com/uploads/user/e7c690e8-6ff9-102a-ac6d-e4aebca50425/f4a5b21d-66fa-4885-92bf-c4e81c06d916/File/7e5a142d4c03d650a0593804455afe0d/bicheckwithwlst_part2.zip
- Edit automation.properties and change the parameters.
- In a shell, set ORACLE_HOME and then launch %ORACLE_HOME%\oracle_common\common\bin\wlst.cmd (or .sh) SGPtesting.py
The output looks like this:
*** Reading properties file
WLPassword=password
datasource.database.password=password
test.user=User12
test.group=Group1
test.nbgroups=10
WLUrl=t3://server.us.oracle.com:7001
WLUsername=weblogic
datasource.database.user=MY
*** Connecting
Connecting to t3://server.us.oracle.com:7001 with userid weblogic ...
Successfully connected to Admin Server "AdminServer" that belongs to domain "bifoundation_domain".
Warning: An insecure protocol was used to connect to the
server. To ensure on-the-wire security, the SSL port or
Admin port should be used instead.
WebLogic Server 10.3.5.0 Fri Apr 1 20:20:06 PDT 2011 1398638
*** Reading SQLGroupProvider settings
SQLGroupProvider is configured
*** SQL queries
GetGroupDescription: SELECT GROUP_DESCRIPTION FROM MY.GROUPS WHERE GROUP_NAME = ?
GroupExists: SELECT GROUP_NAME FROM MY.GROUPS WHERE GROUP_NAME = ?
IsMember: SELECT USER FROM MY.GROUPMEMBERS WHERE MEMBER_OF = ? AND USER = ?
ListGroups: SELECT GROUP_NAME FROM MY.GROUPS WHERE GROUP_NAME LIKE ?
ListMemberGroups: SELECT MEMBER_OF FROM MY.GROUPMEMBERS WHERE USER = ?
*** JDBC Resource details
BIDatabaseGroupDS
jdbc/BIDatabaseGroupDS
jdbc:oracle:thin:@dbserver.us.oracle.com:1521:orcl
oracle.jdbc.OracleDriver
*** Connection pool test
server : AdminServer
dataSource : BIDatabaseGroupDS
DatabaseProductVersion : Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
State : Running
Pool test: OK
*** Testing SQL queries
SQLGetGroupDescription Group1
('Group One',)
SQLGroupExists Group1
('Group1',)
SQLIsMember Group1 User12
('User12',)
SQLListGroups first 10
('Group1',)
('Group2',)
('Group3',)
SQLListMemberGroups User12
('Group1',)
('Group2',)
Ending the script