Author: Robert Lawson
Environment: Windows Server, MS SQL Server, PeopleSoft (Learning Solutions 8/PT 8.19)
Description: This procedure creates a database security role from a PeopleSoft security role. This was used to mimic PeopleSoft security for MS Access user access.
Code:if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CreateRoleTables]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_CreateRoleTables]
GO
CREATE PROCEDURE sp_CreateRoleTables
@InputRole VARCHAR(100),
@Debug smallint = 0
AS
/*
Name: sp_CreateRoleTables
Purpose: Create database security role from a PeopleSoft security role.
Date Whom What
----------------------------------------------------------------------------------
03-JUL-03 Robert Lawson Creation date, PT 8.19 & SQL Server 2000
Parmaters
-----------------------------------------------------------------
@InputRole Passed Role name
@Degug Passed 0=no, 1=yes; default is 0.
Description:
This stored procedure helps setup security for non-PeopleSoft application access to
a PeopleSoft database. The technique is to create a database security group equal to
the PeopleSoft security role. The assumption is that you use NT authentication, and you
match the user's network account with the database security role.
The database security role is created from the PeopleSoft security role. The database
security role has SELECT permission ('read only') for all the tables the PeopleSoft
security role allows a user. The tables allowed to a PeopleSoft user are found
through this chain:
Security Role to Permissions List
Permission List/Query to Access Group Permissions
Access Group Permisssions (Tree Name/Access Group) to Tree Structure
Traverse Tree Structure to actual records
The traverse portion is complicated as T-SQL has no method like Oracle's "CONNECT BY.. PRIOR".
How To Use:
In Query Analyzer, in the database you are working in:
1. Execute this script to create the stored procedure
2. Execute this stored procedure for each security role, as such:
exec sp_CreateRoleTables 'UPG_STDN_REC'
In Enterprise Manager
3. In Security/Logins, add new user, if not there, using domain/user and Windows Authentication
- Database Access for this database roles are "public" and "security role"
- Default database is this database
*/
/*
==========================================================
Variables and temp table
==========================================================
*/
IF EXISTS (SELECT 'x' FROM tempdb.dbo.sysobjects WHERE type = 'U' AND LEFT(name,15) = '#CreateRoleTables')
DROP Table #CreateRoleTables
CREATE TABLE #CreateRoleTables
(Tree CHAR(20), Parent CHAR(20), Child CHAR(20), CNode int, PNode int, TLevel int, Type CHAR(1))
DECLARE
@TLevel int, -- Number of levels traversed. 0=first
@Tree CHAR(20), -- Tree name
@CNode int, -- Child node number
@PNode int, -- Parent node number
@Child CHAR(20), -- Parent "contains" level
@Parent CHAR(20), -- Parent level
@Type CHAR(1), -- 'R'ecord, 'G'roup
@CNode1 int, -- Node number
@PNode1 int, -- Node number
@Child1 CHAR(20), -- Child
@Parent1 CHAR(20), -- Parent
@Type1 VARCHAR(1), -- 'R'ecord, 'G'roup for Parent
@EndOfTree smallint, -- 0=no,1=yes. When true, then no more levels to traverse within tree
@SQLString NVARCHAR(500), -- from sp_helpsql
@Table VARCHAR(100), -- SQL table name
@RoleCursor CURSOR,
@ParentCursor CURSOR,
@ChildCursor CURSOR,
@TableCursor CURSOR
SET NOCOUNT ON -- Turn off row count
/*
==========================================================
Load tree name/access groups
==========================================================
*/
SET @TLevel = 0 -- Top of tree
SET @EndOfTree = 1 -- 0=not, 1=yes at EndofTree
SET @RoleCursor = CURSOR FOR
SELECT DISTINCT SA.TREE_NAME, TN.PARENT_NODE_NAME, TN.TREE_NODE,
TN.TREE_NODE_NUM, TN.PARENT_NODE_NUM, TN.TREE_NODE_TYPE
FROM PSTREENODE TN, PS_SCRTY_ACC_GRP SA, PSROLECLASS RC
WHERE
RC.ROLENAME = @InputRole AND
SA.CLASSID = RC.CLASSID AND
SA.ACCESSIBLE = 'Y' AND
TN.TREE_NAME = SA.TREE_NAME AND
TN.TREE_NODE = SA.ACCESS_GROUP AND
TN.TREE_NODE_TYPE = 'G' -- 'G'roup
OPEN @RoleCursor -- Returns all Tree Names & Access Group's for a security role
WHILE (0=0)
BEGIN
FETCH NEXT FROM @RoleCursor INTO
@Tree, @Parent, @Child, @CNode, @PNode, @Type
if @@Fetch_Status <> 0 break
IF @Debug = 1 PRINT 'RoleCursor ' + @Tree + ' ' + @Parent + ' ' + @Child
INSERT INTO #CreateRoleTables (Tree, Parent, Child, CNode, PNode, TLevel, Type) VALUES
(@Tree, @Parent, @Child, @CNode, @PNode, @TLevel, @Type)
SET @EndOfTree = 0
END -- RoleCursor
CLOSE @RoleCursor
/*
==========================================================
Traverse Tree Logic
==========================================================
*/
WHILE @EndOfTree = 0
BEGIN
IF @Debug = 1 PRINT @TLevel
SET @EndOfTree = 1
SET @ParentCursor = CURSOR FOR
SELECT Tree, Parent, Child, CNode, PNode, Type
FROM #CreateRoleTables
WHERE TLevel = @TLevel
OPEN @ParentCursor -- Get 'parent' level
WHILE (0=0)
BEGIN
FETCH NEXT FROM @ParentCursor INTO
@Tree, @Parent, @Child, @CNode, @PNode, @Type
if @@Fetch_Status <> 0 break
IF @Debug = 1 PRINT '@ParentCursor ' + @Parent + ' ' + @Child
SET @ChildCursor = CURSOR FOR
SELECT TREE_NAME, PARENT_NODE_NAME, TREE_NODE, TREE_NODE_NUM, PARENT_NODE_NUM, TREE_NODE_TYPE
FROM PSTREENODE WHERE
TREE_NAME = @Tree AND
PARENT_NODE_NUM = @CNode AND
PARENT_NODE_NAME = @Child
OPEN @ChildCursor -- Get level below 'parent' information
WHILE (0=0)
BEGIN
FETCH NEXT FROM @ChildCursor INTO
@Tree, @Parent1, @Child1, @CNode1, @PNode1, @Type1
if @@Fetch_Status <> 0 break
IF @Debug = 1 PRINT '@ChildCursor ' + @Parent1 + ' ' + @Child1
INSERT INTO #CreateRoleTables (Tree, Parent, Child, CNode, PNode, TLevel, Type) VALUES
(@Tree, @Parent1, @Child1, @CNode1, @PNode1, (@TLevel + 1), @Type1)
SET @EndOfTree = 0
END -- ChildCursor
END -- ParentCursor
SET @TLevel = @TLevel + 1 -- move down a level (the child becomes the parent, awwww...)
IF @Debug = 1 PRINT '@Tlevel = ' + CONVERT(CHAR(5),@TLevel) + ' @EndOfTree = ' + CONVERT(CHAR(5),@EndOfTree)
END -- EndOfTree
CLOSE @ChildCursor
CLOSE @ParentCursor
/*
==========================================================
Role: remove privledges (and retain membership) or create it
==========================================================
*/ -- From master.dbo.sp_helprole
IF EXISTS (select 'x' from sysusers where name = @InputRole and (issqlrole = 1 or isapprole = 1))
BEGIN
SET @SQLString = N'REVOKE ALL FROM ' + @InputRole
IF @Debug = 1 PRINT @SQLString
EXEC sp_executesql @SQLString
END
ELSE
BEGIN
SET @SQLString = N'sp_addrole ' + @InputRole + ', dbo'
IF @Debug = 1 PRINT @SQLString
EXEC sp_executesql @SQLString
END
/*
==========================================================
Permissions: add to role
==========================================================
*/
SET @TableCursor = CURSOR FOR
SELECT DISTINCT
CASE
WHEN RTRIM(RD.SQLTABLENAME) <> '' THEN RTRIM(RD.SQLTABLENAME)
ELSE 'PS_' + RTRIM(RT.Child)
END
FROM PSRECDEFN RD, #CreateRoleTables RT
WHERE
RT.Type = 'R' AND
RD.RECNAME = RT.Child AND
RD.RECTYPE IN (0,1) -- 0=Record,1=View
OPEN @TableCursor -- Returns list of SQL table names
WHILE (0=0)
BEGIN
FETCH NEXT FROM @TableCursor INTO
@Table
if @@Fetch_Status <> 0 break
-- Verify table actually exists in database (from master.dbo.sp_help)
IF EXISTS (select 'x' from sysobjects where id = object_id(@Table))
BEGIN
SET @SQLString = N'GRANT SELECT ON ' + @Table + ' TO ' + @InputRole
IF @Debug = 1 PRINT 'TableCursor ' + @SQLString
EXEC sp_executesql @SQLString
END
ELSE IF @Debug = 1 PRINT 'TableCursor, *** NO TALBE: ' + @Tree
END -- TableCursor
CLOSE @TableCursor
/*
==========================================================
The End
==========================================================
*/
DEALLOCATE @RoleCursor
DEALLOCATE @ParentCursor
DEALLOCATE @ChildCursor
DEALLOCATE @TableCursor
