1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ckeck this sp (IS THERE ANY OTHER WAY TO SOLVE THIS)

Discussion in 'SQL PL/SQL' started by sandip.senmajumder, Jan 27, 2011.

  1. sandip.senmajumder

    sandip.senmajumder Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    CREATE GLOBAL TEMPORARY TABLE tt_UserList
    (
    UserId NUMBER(19) NOT NULL,
    ParentUserId NUMBER(19)
    );
    /
    CREATE GLOBAL TEMPORARY TABLE tt_RoleList
    (
    RoleId NUMBER(19) NOT NULL,
    ParentRoleId NUMBER(19)
    );
    /
    CREATE GLOBAL TEMPORARY TABLE tt_PrivList
    (
    PrivId NUMBER(19) NOT NULL
    );
    /
    CREATE GLOBAL TEMPORARY TABLE tt_RevokedPrivList
    (
    RevokedPrivId NUMBER(19) NOT NULL
    );
    /


    CREATE OR REPLACE PROCEDURE kiits.prComnResolveUserPrivileges
    (

    -- Add the parameters for the stored procedure here
    pUserId--Internal user id
    IN NUMBER DEFAULT NULL ,
    pIsDebug--whether the procedure should be debugged. When 1, then debug
    IN NUMBER DEFAULT 0 ,
    c1 IN OUT SYS_REFCURSOR
    )
    AS

    BEGIN
    BEGIN

    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    --Validate Parameters
    --if @pUserId can not be null
    IF pUserId IS NULL THEN

    BEGIN
    raise_application_error( -20002, 'Invalid Parameters' );--Error message
    END;
    END IF;
    --severity
    --state
    -- Processing logic
    -- Create a CTE to collect the list of all users in the hierarchy
    WITH cte_UserList AS ( SELECT A.UserInternalId ,
    A.ParentUserId
    FROM t_Users A
    WHERE A.UserInternalId = pUserId
    UNION ALL
    SELECT A.UserInternalId ,
    A.ParentUserId
    FROM t_Users A
    JOIN cte_UserList
    ON A.UserInternalId = cte_UserList.ParentUserId )
    INSERT INTO tt_UserList
    ( SELECT DISTINCT UserInternalId ,
    ParentUserId
    FROM cte_UserList );
    -- Create a CTE to collect the list of all associated roles and their role hierarchy
    WITH cte_RoleList AS ( SELECT RL.RoleId ,
    RL.ParentRole
    FROM t_Roles RL
    WHERE RoleId IN ( SELECT DISTINCT A.RoleId
    FROM t_User_Roles A
    JOIN tt_UserList B
    ON A.UserInternalId = B.UserId )

    UNION ALL
    SELECT A.RoleId ,
    A.ParentRole
    FROM t_Roles A
    JOIN cte_RoleList B
    ON A.RoleId = B.ParentRoleId )
    INSERT INTO tt_RoleList
    ( SELECT DISTINCT RoleId ,
    ParentRoleId
    FROM cte_RoleList );
    --populate privlist table with the list of privileges directly associated with the user and its hierarchy,
    -- together with the list of privileges assigned to the users through roles and role hierarchy
    -- except the list of privileges which are revoked from various users in the hierarchy
    INSERT INTO tt_PrivList
    ( SELECT DISTINCT PRV.PrivilegeId
    FROM ( SELECT UP.PrivilegeId
    FROM t_User_Privileges UP
    JOIN tt_UserList UL
    ON UP.UserInternalId = UL.UserId
    UNION ALL
    SELECT RP.PrivilegeId
    FROM t_Roles_Privileges RP
    JOIN tt_RoleList RL
    ON RP.RoleId = RL.RoleId ) PRV
    EXCEPT
    SELECT RevokedPrivilegeId
    FROM t_User_Revoked_Privileges RVP
    JOIN tt_UserList UL
    ON RVP.UserInternalId = UL.UserId );
    OPEN c1 FOR
    SELECT DISTINCT PV.PrivilegeId PrivilegeId ,
    PV.PrivilegeCode PrivilegeCode ,
    PV.PrivilegeName PrivilegeName ,
    1 IsAuditTrailingRequired ,
    PV.PrivilegeGroupId PrivilegeGroupId ,
    PVG.PrivilegeGroupName PrivilegeGroupName
    FROM t_Privileges PV
    JOIN tt_PrivList PL
    ON PV.PrivilegeId = PL.PrivId
    LEFT JOIN t_Privilege_Groups PVG
    ON PV.PrivilegeGroupId = PVG.PrivilegeGroupId;
    END;
    EXCEPTION
    WHEN OTHERS THEN

    BEGIN
    -- Error handling logic
    IF pIsDebug = 1 THEN

    BEGIN
    OPEN c1 FOR
    SELECT SQLCODE ,
    SQLERRM
    FROM DUAL ;
    END;
    END IF;
    END;
    END;
    END;
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Very nice, but wouldn't it help anyone trying to help you if you specify what you are trying to achieve with this code?
     
  3. sandip.senmajumder

    sandip.senmajumder Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    well this SP will do this
    Retrieves the privileges for the specified user id, after considering the assigned
    privileges to the user and other users in the user hierarchy list. It also includes the assigned
    privileges to the associated roles and other roles in the role hierarchy list. However it excludes
    the privileges in the user revoked list
     
  4. sandip.senmajumder

    sandip.senmajumder Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Why so many BEGIN statements? They are not needed. Second, your first error 'message' is cryptic to say the least; which parameters are invalid? Third, your final error handler populates a reference cursor which requires additional handling to display the error message and text; why? You should be formatting the error text so it can be displayed by the sp without such gyrations. I can only presume you're populating these global temporary tables to query them later but you failed to include the 'ON COMMIT PRESERVE ROWS' directive to maintain the data throughout the session.
     
  6. sandip.senmajumder

    sandip.senmajumder Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    THIS IS THE SP.>>>>
    CHECK THIS



    CREATE OR REPLACE PROCEDURE kiits.prComnResolveUserPrivileges
    (
    -- Add the parameters for the stored procedure here
    pUserId--Internal user id
    IN NUMBER DEFAULT NULL ,
    pIsDebug--whether the procedure should be debugged. When 1, then debug
    IN NUMBER DEFAULT 0 ,
    c1 IN OUT SYS_REFCURSOR
    )
    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    --Validate Parameters
    --if @pUserId can not be null
    IF pUserId IS NULL THEN
    BEGIN
    raise_application_error( -20002, 'Invalid Parameters' );--Error message
    END;
    END IF;
    --severity
    --state
    -- Processing logic
    -- Create a CTE to collect the list of all users in the hierarchy
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    INSERT INTO tt_UserList
    WITH cte_UserList(UserInternalId,ParentUserId) AS ( SELECT A.UserInternalId ,
    A.ParentUserId
    FROM t_Users A
    WHERE A.UserInternalId = pUserId
    UNION ALL
    SELECT A.UserInternalId ,
    A.ParentUserId
    FROM t_Users A
    JOIN cte_UserList cu
    ON A.UserInternalId = cu.ParentUserId )
    SELECT DISTINCT UserInternalId ,
    ParentUserId
    FROM cte_UserList;
    -- Create a CTE to collect the list of all associated roles and their role hierarchy
    INSERT INTO tt_RoleList
    WITH cte_RoleList(RoleId,ParentRoleId) AS ( SELECT RL.RoleId ,
    RL.ParentRole
    FROM t_Roles RL
    WHERE RoleId IN ( SELECT DISTINCT A.RoleId
    FROM t_User_Roles A
    JOIN tt_UserList B
    ON A.UserInternalId = B.UserId )
    UNION ALL
    SELECT A.RoleId ,
    A.ParentRole
    FROM t_Roles A
    JOIN cte_RoleList B
    ON A.RoleId = B.ParentRoleId )
    SELECT DISTINCT RoleId ,
    ParentRoleId
    FROM cte_RoleList ;
    --populate privlist table with the list of privileges directly associated with the user and its hierarchy,
    -- together with the list of privileges assigned to the users through roles and role hierarchy
    -- except the list of privileges which are revoked from various users in the hierarchy
    INSERT INTO tt_PrivList
    ( SELECT DISTINCT PRV.PrivilegeId
    FROM ( SELECT UP.PrivilegeId
    FROM t_User_Privileges UP
    JOIN tt_UserList UL
    ON UP.UserInternalId = UL.UserId
    UNION ALL
    SELECT RP.PrivilegeId
    FROM t_Roles_Privileges RP
    JOIN tt_RoleList RL
    ON RP.RoleId = RL.RoleId ) PRV
    MINUS
    SELECT RevokedPrivilegeId
    FROM t_User_Revoked_Privileges RVP
    JOIN tt_UserList UL
    ON RVP.UserInternalId = UL.UserId );
    OPEN c1 FOR
    SELECT DISTINCT PV.PrivilegeId PrivilegeId ,
    PV.PrivilegeCode PrivilegeCode ,
    PV.PrivilegeName PrivilegeName ,
    1 IsAuditTrailingRequired ,
    PV.PrivilegeGroupId PrivilegeGroupId ,
    PVG.PrivilegeGroupName PrivilegeGroupName
    FROM t_Privileges PV
    JOIN tt_PrivList PL
    ON PV.PrivilegeId = PL.PrivId
    LEFT JOIN t_Privilege_Groups PVG
    ON PV.PrivilegeGroupId = PVG.PrivilegeGroupId;
    END;
    EXCEPTION
    WHEN OTHERS THEN
    BEGIN
    -- Error handling logic
    IF pIsDebug = 1 THEN
    BEGIN
    raise_application_error(SQLCODE, SQLERRM );
    END;
    END IF;
    END;
    /
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This

    raise_application_error(SQLCODE, SQLERRM );

    will create its own error if SQLCODE is less than 20000 or greater than 20999; read here

    http://oratips-ddf.blogspot.com/2008/05/ive-never-seen-that-error-before.html

    on why that happens and how to write a usable error handler. You also still have this:

    raise_application_error( -20002, 'Invalid Parameters' );--Error message

    which would be better written as this:

    raise_application_error( -20002, 'User ID cannot be NULL' );--Error message

    to report WHICH parameter had an invalid value. Since you no longer need the refcursor you can remove the input parameter c1.
     
  8. sandip.senmajumder

    sandip.senmajumder Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    what if when that select block will exec ....
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I don't understand the question. Please ask it in a complete sentence.