SQL

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 joins:

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', 
isnull(
	left(replace(PTUSERS.NAME,'splwg\',''),28),
	(select left(replace(PTUSERGROUPS.name,'splwg\hr_',''),28) 
	 from PTUSERGROUPS 
	 where PTUSERGROUPS.OBJECTID = PTGADGETSECURITY.GROUPID)
) as 'User/Group',

isnull(
	PTUSERS.OBJECTID,
	PTGADGETSECURITY.GROUPID
) as 'U/G ID',
PTGADGETSECURITY.ACCESSLEVEL 

FROM         PTGADGETS  

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'