Part I: Configuring Cognos to use OpenLDAP Authentication
Environment: Cognos 8.3 on Windows 2003 server
Objective: use external OpenLDAP server on Linux for Cognos web authentication.
Steps:
1 OpenLDAP Installation
Download the latest stable release of OpenLDAP stable release.
./configure
make
make test
make install
2. configuring /usr/local/etc/openldap/slapd.conf
[root@ldap1 scripts]# cat ../slapd.conf| grep -v ^# | grep -v ^$
include /usr/local/etc/openldap/schema/core.schema
pidfile /usr/local/var/run/slapd.pid
argsfile /usr/local/var/run/slapd.args
include /usr/local/etc/openldap/schema/corba.schema
include /usr/local/etc/openldap/schema/cosine.schema
include /usr/local/etc/openldap/schema/inetorgperson.schema
include /usr/local/etc/openldap/schema/java.schema
include /usr/local/etc/openldap/schema/misc.schema
include /usr/local/etc/openldap/schema/nis.schema
include /usr/local/etc/openldap/schema/openldap.schema
# assume your company name is 'dev'. Firstly create database for your own company
database bdb
suffix "dc=dev,dc=com"
rootdn "cn=root,dc=dev,dc=com"
rootpw secret
directory /usr/local/var/openldap-data-dev
index objectClass eq
# access control part
access to attr=userPassword
by self write
by anonymous auth
by dn="cn=Manager,dc=dev,dc=com" write
by * none
access to *
by dn="cn=Manager,dc=dev,dc=com" write
by users read
database bdb
suffix "dc=client1,dc=com"
rootdn "cn=Manager,dc=client1,dc=com"
rootpw {SSHA}gFuMY3m3Cb0P4px3TNuf4o7sG30jHcwgi3urEA==
directory /usr/local/var/openldap-data-client1
index objectClass eq
# access control part
access to attr=userPassword
by self write
by anonymous auth
by dn="cn=Manager,dc=client1,dc=com" write
by * none
access to *
by dn="cn=Manager,dc=client1,dc=com" write
by users read
Note:
a. use slappasswd to generate rootpw, the default is to use SSHA encryption.
b. create directory /usr/local/var/openldap-data-dev and /usr/local/var/openldap-data-client1 fist before restarting slapd.
c. for configuring slapd.conf, please refer to http://www.openldap.org/doc/admin24/slapdconfig.html
3. source file to import
[root@ldap1 scripts]# more batchuser.ldif
# entry-id: 1
dn: dc=dev,dc=com
dc: dev
objectClass: top
objectClass: domain
# entry-id: 2
dn: ou=Special Users,dc=dev,dc=com
objectClass: top
objectClass: organizationalUnit
ou: Special Users
description: Special Administrative Accounts
# entry-id: 3
dn: ou=People,dc=dev,dc=com
objectClass: top
objectClass: organizationalunit
ou: People
# entry-id: 4
dn: ou=Groups,dc=dev,dc=com
objectClass: top
objectClass: organizationalunit
ou: Groups
dn: cn=admin,ou=Groups,dc=dev,dc=com
cn: admin
objectClass: top
objectClass: groupofuniquenames
ou: Groups
uniqueMember: uid=jephe,ou=People,dc=dev,dc=com
uniqueMember: uid=zhitan,ou=People,dc=dev,dc=com
# entry-id: 5
dn: uid=zhitan,ou=People,dc=dev,dc=com
uid: zhitan
objectClass: inetorgperson
givenName: Zhitan
sn: Wu
cn: Zhitan Wu
userPassword: {SSHA}h7HBuirlNhYJl1TwVEtKqJlJVCb53cqm
# entry-id: 6
dn: uid=jephe,ou=People,dc=dev,dc=com
uid: jephe
objectClass: inetorgperson
givenName: Wu
sn: Jephe
cn: Jephe Wu
userPassword: {SSHA}EWo+m4UrXE0yjgefxa4yJ54hz845B3xz
4. commands used
4.1 ldap search everything
ldapsearch -x -b 'dc=dev,dc=com' '(objectclass=*)'
4.2 ldap add user
ldapadd -x -D 'cn=Manager,dc=dev,dc=com' -W -f jephe.ldif
Enter LDAP Password:
adding new entry "uid=jephe,ou=People,dc=dev,dc=com"
root@app1 scripts]# more jephe.ldif
# entry-id: 7
dn: uid=jephe,ou=People,dc=dev,dc=com
uid: jephe
givenName: Wu
objectClass: inetorgperson
sn: Jephe
cn: Jephe Wu
userPassword: {SSHA}hfJXE/3c8zK42rD6FL7mZB6SxG1DA2o+
4.3 ldap delete user
ldapdelete -x -D 'cn=Manager,dc=dev,dc=com' -W uid=jephe,ou=people,dc=dev,dc=com
Enter LDAP Password:
4.4 ldap change password for user
a. ldappasswd -D "cn=manager,dc=dev,dc=com" -x -W -s abcd1235 "uid=jephe,ou=people,dc=dev,dc=com"
b. ldappasswd -D "cn=manager,dc=dev,dc=com" -x -W -S "uid=jephe,ou=people,dc=dev,dc=com"
New password: (new password for user)
Re-enter new password: (new password for user again)
Enter LDAP Password: manager password
Result: Success (0)
4.5 ldap add group
ldapadd -x -D 'cn=Manager,dc=dev,dc=com' -W -f group1.ldif
Enter LDAP Password:
adding new entry "cn=testgroup,ou=Groups,dc=dev,dc=com"
[root@app1 scripts]# more group1.ldif
dn: cn=testgroup,ou=Groups,dc=dev,dc=com
cn: testgroup
objectClass: top
objectClass: groupofuniquenames
ou: Groups
uniqueMember: uid=jephe,ou=People,dc=dev,dc=com
uniqueMember: uid=zhitan,ou=People,dc=dev,dc=com
4.6 ldap delete group
ldapdelete -x -D 'cn=Manager,dc=dev,dc=com' -W cn=testgroup,ou=Groups,dc=dev,dc=com
Enter LDAP Password:
4.7 ldap modify/delete groupmember
ldapmodify -x -D 'cn=Manager,dc=dev,dc=com' -W -f b
Enter LDAP Password:
modifying entry "cn=admin,ou=Groups,dc=dev,dc=com"
[root@app1 scripts]# more b
dn: cn=admin,ou=Groups,dc=dev,dc=com
changetype: modify
delete: uniquemember
uniquemember: uid=jephe,ou=people,dc=dev,dc=com
4.8 ldap modify/add member. ldapmodify -x -D 'cn=Manager,dc=dev,dc=com' -W -f b
Enter LDAP Password:
modifying entry "cn=admin,ou=Groups,dc=dev,dc=com"
[root@app1 scripts]# more b
dn: cn=admin,ou=Groups,dc=dev,dc=com
changetype: modify
add: uniquemember
uniquemember: uid=jephe,ou=people,dc=dev,dc=com
4.9. how to add user through phpldapadmin
4.9.1 manual way:
a. custom
b. uid=user1
c. ou=people,dc=dev,dc=com
d. inetorgperson
after done, add new attribute- userpasswd , then it will automatically encrypt it using md5 algorithm.
type in password, use mkpasswd.sh to generate
4.9.2 use tools from phpldapadmin
After adding existing users, you can copy the existing user setup to another user, even in different client.
4.10 how to reset password for user
use mkpasswd on linux to generate random password (4 digitals and 4 characters, mkpasswd -l 8 -s 0 )
Part II: install Cognos and configure web server and database
5. configuring Apache on Windows
We use apache server 2.2.2 for Windows.
In httpd.conf configuration, put the following:
options Indexes followsymlinks
allowoverride none
order allow,deny
allowoverride none
options none
order allow,deny
allow from all
in alias module, put
scriptalias /cgi-bin "C:/program files/apache software fouondation/apache 2.2/cgi-bin/"
scriptalias /cognos8/cgi-bin "C:/program files/cognos/c8/cgi-bin"
alias /cognos8 "C:/program files/cognos/c8/webcontent"
You can install cognos web gateway on Linux server also.
6. Configuring browser to use http://ipaddress/cognos8/ (in IE security, make http://ipaddress in the trusted website)
7. setup database for cognos
7.1 MS SQL database
a. go to enterprise manager,go to database, right click, create new one called 'cm'
b. go to security, logins, 'new login' put 'cmuser' as name, password is also 'cmuser', database is 'cm',
database access part, put 'cm' as public and 'db_owner'.
7.2 Oracle database
7.2.1 You have to create a separated instance only for cognos use, it requires AL32UTF8 character set.
can use dbca to create a new instance id 'cognos' with character set AL32UTF8.
After that, create a tablespace 'contentstore' and user 'contentstore' and give user contentstore as dba privileges.
Create tablespace contentstore datafile '/u01/app/oracle/oradata/cognos/contentstore.dbf' size 20m autoextend on next 5m flashback off;
Create user contentstore identified by contentstore default tablespace contentstore temporary tablespace temp;
Grant dba to contentstore;
7.2.2 Configuring Net manager
From start - All programs - Oracle oraClient11g_home1-configuration and Migration tools- net manager
Oracle Net Configuration-local -Servide Naming, add a new one called 'cognos' which has the following information:
Service name: cognos
Connection type: Database default
Protocol: TCP/IP
Hostname: 1.2.3.4 (replace with your oracle server IP)
Port number: 1521
7.2.3 create tablespace and users:
create tablespace cognos datafile '/u01/app/oracle/oradata/cognos.dbf' size 20m autoextend on next 10m flashback off;
create user cognos identified by password default tablespace cognos temporary tablespace temp;
grant connect,resource,create view to cognos;
revoke unlimited tablespace from cognos;
alter user cognos quota unlimited on cognos;
7.2.4 Important - Changing 'cursor sharing' mode in Oracle 11g database used for Cognos reporting
When using Cognos 8 framework manager to fetch tables from Oracle 11g database, by default, it will give you the following errors. It is okay for views.
'RQP-DEF-0177' an error occurred while performing operation sqlbulkfetch status =-9
UDA-SQL-0177 general exception has occurred during the operation fetch.
Solution: In Oracle, change the cursor sharing mode to 'Exact'
Steps:
By default, Oracle 11g use 'SIMILAR' mode for cursor sharing, you need to change it to 'EXACT' mode.
Alter system set cursor_sharing='EXACT' scope = both;
To verify what's the value for cursor sharing, use:
select name, value from gv$parameter where name like '%cursor%'
8 Cognos Installation
8.1 Install Cognos for Windows and Framework manager
Install everything including web content for use for apache later
8.2 Configuring 'Cognos configruaton'
8.2.1 Use Oracle as content store
You have to delete the default Microsoft sql server content store first before you can create Oracle content store
Download the oracle 10g latest JDBC driver class12.jar then put it under c:\Program files\cognos\c8\webapps\p2pd\WEB-INF\lib
Note: Oracle 11g JDBC driver doesn't work with Cognos 8
Now you can configure Data Access - Content Manager- contentstore part as follows:
Database server and port number: Serverip:1521
Userid and password: contentstore/contentstore/contentstore
Service name which is oracle instance sid : cognos
save and start cognos service
8.3 Configuring data source in Cognos
Use browser to access http://localhost/cognos8/, then go to 'cognos connection', go to 'tools' - 'directory' , to create a new data source to use Oracle database 11g.
8.4 Open framework manager to create a new project and data source.
For sql net connection string, use 'cognos' which is defined earlier in tnsname.ora
Remember: you must change Oracle 11g cursor sharing mode to 'EXACT' so that Framework manager can receive table information from database, otherwise, it will give errors. For views, it is okay
9. cognos configuration for LDAP
Authentication: dev
Namespace ID: DEV
Host and port: 10.0.0.18:389
base distinguished Name: dc=dev,dc=com
user lookup: uid=${userID},ou=people,dc=dev,dc=com
Bind user DN and password: cn=Manager,dc=dev,dc=com
Password is the Manager password
Size Limit: 0
Time out in Seconds: 0
note: you also need to disable anonymous access from default 'Cognos' namespace.
10. restart cognos configuration and test it ( http://server/cognos8/)
Part III Cognos user permission and how it works
11. system administrators in cognos namespace itself
You can put your admin group 'admin' in 'dev' openldap database (pointing to dev openldap database for authentication) in the cognos directory 'system administrators'.
After adding to system administrator, you can view all directories under 'public folder' in cognos for reports.
steps below:
1. use IE to login https://url/cognos/
2. go to 'launch' menu, cognos administration
3. go to 'security'-'cognos', the second page, the last second one which is 'system administrators'
4. go to properities, members, add 'admin' group in 'dev' namespace
5. you only need to add 'admin' group to system administrator, no need to add to other cognos directories.
12. how to create user and assign permission for reports
If a user needs to view multiple openldap database/ cognos namespace reports, you should add this user in dev openldap database
instead of individual client namespace.
Then you can assign this user to multiple namespace to view their reports.
steps: login as dev admin group user, go to 'home' and clien properity for each client folder, permission, add the user.
Part IV Cognos reporting
13. create data source connection, use a user which can read all client schemas data ( select any table) or use system user for creating data source connection which can read all schema data
14. modify datasource schema before creating and publishing a new package for another client.
Before publishing a package, you need to change 'schema' part in data source name properities then save so that that published package is only for that schema only. Don't enable data source view when publishing package, choose only 'english'.
Use Cognos 8.3 + Oracle 11g for business reporting
How to grant Oracle user/schema privileges properly
Environment: Oracle 11g 64bit
Objective: create and grant proper privileges to a user/schema
Steps:
create tablespace jephe datafile ‘/u01/app/oracle/oradata/jephe.dbf’ size 100m autoextend on next 10m [flashback off]
create user jephe identified by password default tablespace jephe temporary tablespace temp;
grant connect,resource,create view to jephe
revoke unlimited tablespace from jephe
alter user jephe quota unlimited on jephe;
note:
a. If you use impdp to import schema from expdp dump, the unlimited tablespace system privileges will remain, you have to manually revoke that system privilege.
Revoke unlimited tablespace from jephe
This will revoke systemwide unlimited tablespace privileges, otherwise, the user can write to any tablespace
UNLIMITED TABLESPACE system privilege: Overrides all individual tablespace quotas and
gives the user unlimited quota on all tablespaces, including SYSTEM and SYSAUX. This
privilege must be granted with caution.
Note: Be aware that granting the RESOURCE role includes granting this privilege.
b. CREATE PROCEDURE privilege is for creating procedures, functions, packages
c. you can check your privs after login:
select * from session_privs;
select * from session_roles;
d. check role sys,tab and role privileges
sqlplus / as sysdba
select distinct object_name from dba_objects where object_name like 'ROLE_%' order by object_name;
OBJECT_NAME
--------------------------------------------------------------------------------
ROLE_ROLE_PRIVS
ROLE_SYS_PRIVS
ROLE_TAB_PRIVS
SQL> select * from role_sys_privs where role like 'CONNECT';
ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CONNECT CREATE SESSION NO
SQL> select * from role_sys_privs where role like '%RESOURCE%';
ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE TABLE NO
RESOURCE CREATE INDEXTYPE NO
8 rows selected.
SQL> select * from role_tab_privs where role like '%RESOURCE%';
no rows selected
SQL> select * from role_role_privs where role like '%RESOURCE%';
no rows selected
How to copy the whole schema from one server to another in Oracle 11g
Objective: clone one schema from one Oracle 11g database to another 11g database server, schema name might change.
Environment: RHEL5, From Oracle 11g 64bit to Oracle 11g 32bit.
Steps:
1. create server side directory first before exporting schema
. oraenv
orcl
sqlplus / as sysdba
create or replace directory dmpdp as '/u01/dmpdp';
grant read,write on directory dmpdp to system;
note: you can use 'select * from dba_directories' to check after creation.
2. use expdp to dump schema
expdp system directory=dmpdp dumpfile=jephe.20091210.dmpdp logfile=jephe.20091210.logdp schemas=jephe
note: if you are exporting schema for lower version of Oracle database, such as 10g, you might need to use exp instead of expdp:
exp system file=jephe.20091210.dmp log=jephe.20091210.log statistics=none owner=jephe
3. check the existing schema on destination database server
. oraenv
uatdb
sqlplus / as sysdba
select name from v$datafile;
select distinct s.owner,s.tablespace_name,d.file_name from dba_segments s,dba_data_files d where s.tablespace_name = d.tablespace_name;
note: above statement lists out tablespace names for relevant schema.
drop tablespace jephe including contents and datafiles
drop user jephe cascade;
note: if above drop user command got ORA-01940 error: cannot drop a user that is currently connected. Then you need to kill all the existing user connections first.
You can use the following scripts to do it:
# more killusersession.sql
set head off feedback off pagesize 0 echo off term off linesize 32767 trimsp on tab off define off;
spool /u01/scripts/killusersessionfinal.sql;
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where username='JEPHE';
select 'exit;' from dual;
spool off;
exit;
cd /u01/scripts/
sqlplus / as sysdba @killusersession.sql
sqlplus / as sysdba @killusersessionfinal.sql
4. create tablespace on destination database server and import
sqlplus / as sysdba
select name from v$datafile;
create tablespace jephe datafile '/path/to/jephe.dbf' size 100m autoextend on next 10m flashback off;
exit
if you are going to use imp instead of impdp to import schema later, you will have to do this:
create tablespace jephe datafile ‘/u01/app/oracle/oradata/uatdb/jephe.dbf’ size 100m autoextend on next 10m flashback off
create user jephe identified by password default tablespace jephe temporary tablespace temp;
grant connect,resource,create view, select any table to jephe
revoke unlimited tablespace from jephe
alter user jephe quota unlimited on jephe;
5. transfer dumpfile to /home/oracle on destination server and define the /home/oracle as 'oracle'
sqlplus / as sysdba
create or replace directory oracle as '/home/oracle';
grant read,write on directory dmpdp to system;
exit;
# impdp system directory=oracle dumpfile=jephe.20091210.dmpdp logfile=jephe.20091210.logimpdp schemas=jephe
if you use imp, do this:
imp system file=/home/oracle/jephe.dmp log=/home/oracle/jephe.implog fromuser=jephe touser=jephe;
note:
1. if you need to import to another schema, you can do this:
firstly create tablespace zhitan, then run
# impdp system directory=oracle dumpfile=jephe.20091210.dmpdp logfile=jephe.20091210.logimpdp remap_schema=jephe:zhitan remap_tablespace=jephe:zhitan transform=oid:n
or
# imp system file=/home/oracle/jephe.dmp log=/home/oracle/jephe.implog fromuser=jephe touser=zhitan;
note: if using imp to import from one user to another use, you need to
alter user zhitan default tablespace zhitan;
Revoke the unlimited tablespace and change the new user’s quota to have space only on the new tablespace:
revoke unlimited tablespace from zhitan;
alter user zhitan quota unlimited on zhitan quota 0 on jephe;
6. checking after import
see if the destination tablespace got data after import
sqlplus / as sysdba
SQL> column owner format a20
SQL> select owner,count(*) from dba_segments where tablespace_name='TEST_DATA' group by owner;
see if user jephe is using his own tablespace:
select username,default_tablespace from dba_users where username='JEPHE';
7. compile the schema
sqlplus / as sysdba
sql> exec dbms_utility.compile_schema('JEPHE');
8. check invalid objects and the number of invalid objects for schema JEPHE
# more numberofinvalidobjects.sql
select count(*) JEPHE, object_type from dba_objects where owner='JEPHE' and status <> 'VALID' and object_name not like 'FB_%' group by object_type;
# more invalidobjects.sql
column object_type format A10;
column object_name format A30;
select object_type "Invalid JEPHE",object_name from dba_objects where owner='JEPHE' and status <> 'VALID' and object_name not like 'FB_%' order by object_type,object_name;
Cloning an existing virtualbox VM for remote desktop connection
Objective: cloning an existing virtualbox VM for remote desktop connection
Environment: Linux host server running Fedora 9 and Virtualbox 2.2.2r46594
Steps:
1. shutdown existing VM vm1
# VBoxManage controlvm vm1 acpipowerbutton
2. clone vm1 to vm2
# cd /root/.VirtualBox/HardDisks
# VBoxManage clonehd vm1.vdi vm2.vdi
3. add one more VM and use the existing filechange memory setting for vm2
use virtualbox command on Linux host to add one more VM, use the existing vdi file vm2.vdi. Change settings such as network card (NAT or Bridge), Memory usage and different vrdp port etc
4. enable mutli connection for vrdp
# VBoxManage modifyvm vm2 --vrdpmulticon on
5. enable pam for external vrdp authentication
for Fedora 9, you need to do this:
# ln -sf /lib/libpam.so.0.81.13 /lib/pam.so
then external authentication can use PAM to authenticate normal user accounts on Fedora 9 VirtualBox host server.
6. create username and password on Linux host for vm2 vrdp authentication
# useradd vm2user
# passwd vm2user
7. startup vm2
# VBoxManage startvm vm2 --type vrdp
8. use remote desktop connection on Windows client pc to connect to vm2
configure mstsc to save username and password for vm2 connection.
9. compress vdi file size
use dd to create empty zero file on Linux/Windows host
dd if=/dev/zero of=temp1 bs=1M
rm -f temp1 or del temp1(Windows)
VBoxManage modifyhd vdifilename --compact
Run db2 script at the specified time automatically and send log file out
Objective: run db2 script at the specified time automatically and send log file out.
Environment: RHEL5 with db2 V9.0
Script:
- write script as below
#!/bin/sh
export PATH=/db2/db2inst1/sqllib/bin:/usr/bin:/bin
. /db2/db2inst1/sqllib/db2profile
cd /db2/db2inst1/scripts/20091210
FILE=open.sql
db2 connect to db1
db2 set schema = schema1
db2 -tvf $FILE -l ${FILE}.log
db2 terminate
sleep 5
scp ${FILE}.log backup@mon1:/tmp
sleep 5
#copy log file to email server and run email command there
ssh -t backup@mon1 "email -b -s "script result" jephe@domain.com < /tmp/${FILE}.log"
2. make it run as cronjob
#chmod +x /db2/db2inst1/scripts/20091210/open.sh
then crontab -e to add the following to run it at 9am 5 Jan
0 9 5 1 * /db2/db2inst1/scripts/20091210/open.sh > /dev/null 2>&1
note:
1. make sure all your command used in the script are in /usr/bin or /bin, otherwise, put the necessary paths inside the script export line.
2. put open.sql under /db2/db2inst1/scripts/20091210/
3. some tips - open and close code
update something set code=concat(code,'_DISABLE') where something;
update something set code=replace(code,'_DISABLE','') where something;
Posts
