French & Spanish
A friend once told me that in two job interviews people didn't
bother asking him about whether he knew SQL. He said in both cases they simple asked, "Do you know
how to do joins?" According to him, that's what people want to know. Okay, I'll show you some useful
Security rights for objects (like gadgets, communities, and folders) in the Plumtree Corporate Portal
are stored in its database. The only way Plumtree provides to view security rights is to go into the
administrative wizard for the individual objects. The Plumtree wizards work well when an
administrator is interested in examining or modifying any or all properties of an individual object
such as a press release gadget. The wizards are very cumbersome though if you are trying to audit
security across mulitple objects.
When bringing SPL Worldgroup's portal
into production, I had assigned security rights throughout the portal, and I wanted to be sure I had
not made errors. I wanted to have one report showing security assignments for each object type. I
created these desired reports using SQL queries like the one below for each object type. I execute them in Query Analyzer.
Additionally, it's easy to change the final WHERE clause to get customized reports. Reports might
show all gadgets a user has rights to or might show all users with read/write access to a certain
community. This has become a great tool for me and other portal administrators.
SELECT left(PTGADGETS.OBJECTID,4) AS GID,
left(PTGADGETS.name,30) as 'Gadget Name',
where PTUSERGROUPS.OBJECTID = PTGADGETSECURITY.GROUPID)
) as 'User/Group',
) as 'U/G ID',
left outer join PTGADGETSECURITY ON
PTGADGETS.OBJECTID = PTGADGETSECURITY.OBJECTID
left outer JOIN PTUSERS ON
PTGADGETSECURITY.GROUPID * - 1 = PTUSERS.OBJECTID
WHERE (PTGADGETSECURITY.GROUPID < 0)
or PTGADGETSECURITY.GROUPID > 99
ORDER BY 'Gadget Name'