CREATE OR REPLACE package body EM_ACCESS_PAGE
as
--
-- PURPOSE
--
--  To get the list of users and roles.
--
-- PARAMETERS
--
--  P_GIVEN_OBJECT_NAME_IN - Name of the user or role
--  P_OBJECT_NAME_OUT - List of users and roles
--  P_OBJECT_DESCRIPTION_IN - List of descritpion of roles and users
--
--
/*
PROCEDURE GET_USER_ROLE_LIST
(
    p_given_object_name   IN   VARCHAR2,    
    p_object_name         OUT  VARCHAR2_TABLE,
    p_object_description  OUT  VARCHAR2_TABLE
) IS
    l_count                  NUMBER;
    l_counter                NUMBER;
    l_is_super		     NUMBER;
    l_public_role   	     VARCHAR2(6);

    CURSOR c1(public_role VARCHAR2) IS
        SELECT user_name grantee, 'User' description
        FROM mgmt_created_users
        WHERE upper(user_name) LIKE upper('%'||p_given_object_name||'%')  AND DELETING = 0
        UNION
        SELECT role_name grantee, description description
        FROM mgmt_roles
        WHERE upper(role_name) LIKE upper('%'||p_given_object_name||'%') AND upper(role_name) <> upper(public_role)
        ORDER BY 1;
	
BEGIN
    l_count := 1;
    p_object_name := varchar2_table();
    p_object_description := varchar2_table();
	l_is_super := MGMT_USER.HAS_PRIV(MGMT_USER.GET_CURRENT_EM_USER, 'SUPER_USER');
	IF(l_is_super = MGMT_USER.USER_DOES_NOT_HAVE_PRIV)
	THEN
		l_public_role := 'PUBLIC';
	ELSE
		l_public_role := ' ';
	END IF;
	FOR rec IN c1(l_public_role)		
    LOOP
        p_object_name.extend(1);
        p_object_description.extend(1);
        p_object_name(l_count) := rec.grantee;
        IF (rec.description='User') THEN  -- Determine whether he is a Admin oR a Super Admin
            SELECT count(*)
            INTO l_counter
            FROM mgmt_priv_grants
            WHERE grantee=rec.grantee
            AND priv_name='SUPER_USER';
            IF (l_counter=1) then
                p_object_description(l_count):='1';
            ELSE
                p_object_description(l_count):='0';
            END IF;
        ELSE -- this is a role, display first 30 characters of description
            IF (length(rec.description)>30) THEN
                p_object_description(l_count):=substr(rec.description,1,28)||'..';
            ELSE
                p_object_description(l_count):=nvl(rec.description, 'ROLE');
            END IF;
        END IF;
        l_count := l_count+1;
    END LOOP;

END GET_USER_ROLE_LIST;

PROCEDURE get_access_info
(
    p_target_name       IN  varchar2,
    p_target_type       IN  varchar2,
    p_error_message     OUT varchar2,
    p_user_name         OUT varchar2_table,
    p_user_description  OUT varchar2_table,
    p_user_privilege    OUT varchar2_table
)
IS
    l_counter number;
    l_count   number;
    l_guid       raw(16);
BEGIN

    l_guid := mgmt_target.get_target_guid(p_target_name, p_target_type);             
    
    IF (l_guid IS NULL) THEN
        p_error_message:='Object GUID cannot be NULL';
        RETURN;
    END IF;

    p_user_name  := varchar2_table();
    p_user_description := varchar2_table();
    p_user_privilege := varchar2_table();

    l_counter:=0;
    FOR c1 IN
    (  SELECT user_name grantee, 'User' TYPE
        FROM mgmt_created_users
        WHERE 
              deleting=0 and
                user_name IN (
                                  SELECT DISTINCT(grantee) 
                             FROM mgmt_priv_grants
                             WHERE grantee_is_role=0
                             AND guid=l_guid
                           )    
                             
        UNION
        SELECT role_name grantee, description TYPE
        FROM mgmt_roles
        WHERE
             role_name IN (     
                                  SELECT DISTINCT(grantee)
                            FROM mgmt_priv_grants
                            WHERE grantee_is_role=1
                            AND guid=l_guid     
                              )
        
        ORDER BY 1
     )
     LOOP
        l_counter:=l_counter+1;
        p_user_name.extend(1);
        p_user_name(l_counter):=c1.grantee;
        p_user_description.extend(1);
        IF (c1.type='User') THEN  -- Determine whether he is a Admin oR a Super Admin
            SELECT count(*)
            INTO l_count
            FROM mgmt_priv_grants
            WHERE grantee=c1.grantee
            AND priv_name='SUPER_USER';
            IF (l_count=1) THEN
                p_user_description(l_counter):='1';
            ELSE
                p_user_description(l_counter):='0';
            END IF;
        ELSE -- this is a role, display first 30 characters of description
            IF (length(c1.type)>30) THEN
                p_user_description(l_counter):=substr(c1.type,1,28)||'..';
            ELSE
                p_user_description(l_counter):=nvl(c1.type, 'ROLE');
            END IF;
        END IF;  -- if (c1.type='User') ends

        FOR c2 IN  
        (
            SELECT priv_name
            FROM mgmt_priv_grants
            WHERE grantee=c1.grantee
            AND guid = l_guid
            AND direct_grant=1
        )
        LOOP
            p_user_privilege.extend(1);
            p_user_privilege(l_counter):=c2.priv_name;

        END LOOP; -- c2 end

     END LOOP;  --c1 ends


END get_access_info;

PROCEDURE get_access_info_template
(
    p_target_name       IN  varchar2,
    p_target_type       IN  varchar2,
    p_error_message     OUT varchar2,
    p_user_name         OUT varchar2_table,
    p_user_description  OUT varchar2_table,
    p_user_privilege    OUT varchar2_table
)
IS
    l_counter number;
    l_count   number;
    l_guid       raw(16);
BEGIN

    l_guid := mgmt_template.get_template_guid(p_target_type,p_target_name);    
    IF (l_guid IS NULL) THEN
        p_error_message:='Object GUID cannot be NULL';
        RETURN;
    END IF;

    p_user_name  := varchar2_table();
    p_user_description := varchar2_table();
    p_user_privilege := varchar2_table();

    l_counter:=0;
    FOR c1 IN
    (  SELECT user_name grantee, 'User' TYPE
        FROM mgmt_created_users
        WHERE 
              deleting=0 and
                user_name IN (
                                  SELECT DISTINCT(grantee) 
                             FROM mgmt_priv_grants
                             WHERE grantee_is_role=0
                             AND guid=l_guid
                           )    
                             
        UNION
        SELECT role_name grantee, description TYPE
        FROM mgmt_roles
        WHERE
             role_name IN (     
                                  SELECT DISTINCT(grantee)
                            FROM mgmt_priv_grants
                            WHERE grantee_is_role=1
                            AND guid=l_guid     
                              )
        
        ORDER BY 1
     )
     LOOP
        l_counter:=l_counter+1;
        p_user_name.extend(1);
        p_user_name(l_counter):=c1.grantee;
        p_user_description.extend(1);
        IF (c1.type='User') THEN  -- Determine whether he is a Admin oR a Super Admin
            SELECT count(*)
            INTO l_count
            FROM mgmt_priv_grants
            WHERE grantee=c1.grantee
            AND priv_name='SUPER_USER';
            IF (l_count=1) THEN
                p_user_description(l_counter):='1';
            ELSE
                p_user_description(l_counter):='0';
            END IF;
        ELSE -- this is a role, display first 30 characters of description
            IF (length(c1.type)>30) THEN
                p_user_description(l_counter):=substr(c1.type,1,28)||'..';
            ELSE
                p_user_description(l_counter):=nvl(c1.type, 'ROLE');
            END IF;
        END IF;  -- if (c1.type='User') ends

        FOR c2 IN  
        (
            SELECT priv_name
            FROM mgmt_priv_grants
            WHERE grantee=c1.grantee
            AND guid = l_guid
            AND direct_grant=1
        )
        LOOP
            p_user_privilege.extend(1);
            p_user_privilege(l_counter):=c2.priv_name;

        END LOOP; -- c2 end

     END LOOP;  --c1 ends


END get_access_info_template;

PROCEDURE update_privilige
(     
      p_target_name         IN  varchar2,
      p_target_type         IN  varchar2,
      p_type                IN  varchar2,
      p_object_name         IN varchar2_table,
      p_object_priv         IN varchar2_table        
) 
IS
        
        l_name                  varchar2(500);
        l_privilege             varchar2(20);        
         l_length                 number;
      l_index_value         number;
      l_grantee_is_role     number;
      l_ipriv_name            varchar2(20);
      l_guid                raw(16);       
BEGIN
      IF(p_type = 'target') 
      THEN
      	  l_guid := mgmt_target.get_target_guid(p_target_name, p_target_type);
      ELSIF(p_type = 'template')
      THEN
	  l_guid := mgmt_template.generate_template_guid(p_target_type, p_target_name);
      END IF;
      
      
      DELETE FROM 
        mgmt_priv_grants 
        WHERE 
        guid=l_guid;    
      
      
      l_index_value := 1;     
        l_length:= p_object_name.count;
          
      
      IF(l_length>0)
      THEN
          LOOP    
            l_name := p_object_name(l_index_value);
            l_privilege := p_object_priv(l_index_value);        
            
            MGMT_USER.GRANT_PRIV
                    (        
                        grantee_in=>l_name,
                           priv_name_in=>l_privilege,
                           guid_in=>l_guid                    
                    );
                            
            --Increase the counter
            l_index_value := l_index_value+1;        
            EXIT WHEN l_index_value > l_length; 
          END LOOP;
      END IF;   

END update_privilige;
*/
END EM_ACCESS_PAGE;
/