Skip to main content

I created a report for the user who already access all the data in the report (Help needed with a SQL statement (Challenging) | IFS Community), but he wants to see them all together at once.

But when he runs the report, he gets “Insufficient Permissions/privileges” error.

Insufficient Permissions/privileges

Failed executing query (ORA-01031: insufficient privileges)

Here is the query;

SELECT DISTINCT
CPA.INTERNAL_DISPLAY_NAME as FULL_NAME,
COMPANY_ID as COMPANY,
COMPANY_ORG_API.Get_Org_Name(COMPANY_ID,ORG_CODE) as "Dept.",

--PROJECTS ARE POPULATED, DISTINCT Didn't work.
 (SELECT DISTINCT LISTAGG(PAR1.PROJECT_ID, '; ' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY PAR1.PROJECT_ID) FROM Project_access_result2 PAR1 WHERE PAR1.EMPLOYEE_ID = CPA.EMP_NO) as "PROJECT",

COMPANY_POSITION_API.Get_Position_Title(company_id,POS_CODE) as Position,
TRBRD_PERSONEL_BANKA_API.Get_Iban(company_id,emp_no,1,1) as IBAN,
Emp_Employed_Time_API.Get_Common_Emp_Start_Date(COMPANY_ID, EMP_NO) as "Emp. Start",
Emp_Employed_Time_API.Get_Common_Emp_End_Date(COMPANY_ID, EMP_NO, SYSDATE) as "Emp. End",
TRUNC(MONTHS_BETWEEN(sysdate, PER.DATE_OF_BIRTH) / 12,0) as AGE,
'Employment Duration will be calculated based on EXCEL FORMULA =((YEAR(M5)-YEAR(K5))*12+MONTH(M5)-MONTH(K5))/12' as "Duration of Employment",
PER.DATE_OF_BIRTH as Birth,
PER.INSURANCE_ID as "INS. ID",
PER.GENDER as Gender,
'Military Service status field will be added' as MSS,
PER.MARITAL_STATUS as "Mar. Status",
PER.BLOOD_TYPE as Blood,

(SELECT education_level_name
FROM (
    SELECT education_level_name
    FROM   pers_education_profile
    WHERE  person_id = cpa.person_id
    AND    extract(YEAR FROM end_year) != 9999
    ORDER BY end_year DESC, education_level_no DESC
)
WHERE ROWNUM = 1) as "EDU LEVEL",

(SELECT EDU_INSTITUTION_NAME
FROM (
    SELECT EDU_INSTITUTION_NAME
    FROM   pers_education_profile
    WHERE  person_id = cpa.person_id
    AND    extract(YEAR FROM end_year) != 9999
    ORDER BY end_year DESC, education_level_no DESC
)
WHERE ROWNUM = 1) as "University",

(SELECT education_field_name
FROM (
    SELECT education_field_name
    FROM   pers_education_profile
    WHERE  person_id = cpa.person_id
    AND    extract(YEAR FROM end_year) != 9999
    ORDER BY end_year DESC, education_level_no DESC
)
WHERE ROWNUM = 1) as "EDU FIELD",

(SELECT END_YEAR
FROM (
    SELECT END_YEAR
    FROM   pers_education_profile
    WHERE  person_id = cpa.person_id
    AND    extract(YEAR FROM end_year) != 9999
    ORDER BY end_year DESC, education_level_no DESC
)
WHERE ROWNUM = 1) as "GRAD. DATE",

(Select COMM_DATA from PERS_COMMS2 Where METHOD_ID_DB = 'E_MAIL' and DESCRIPTION = 'Şahsi' and PERSON_ID = EMP_NO and ROWNUM = 1) as "Personal E-Mail",

(Select COMM_DATA from PERS_COMMS2 Where METHOD_ID_DB = 'E_MAIL' and DESCRIPTION = 'İş' and PERSON_ID = EMP_NO and ROWNUM = 1) as "Corp. E-Mail",

(Select COMM_DATA from PERS_COMMS2 Where METHOD_ID_DB = 'MOBILE' and DESCRIPTION = 'Şahsi' and PERSON_ID = EMP_NO and ROWNUM = 1) as "Mobile",

(select RELATIVE_NAME from PERS_RELATED where PERSON_ID = EMP_NO AND ROWNUM = 1) as "Emergency 1st Person Name", 

(select RELATIVE_PHONE from PERS_RELATED where PERSON_ID = EMP_NO AND RELATIVE_NAME = (select RELATIVE_NAME from PERS_RELATED where PERSON_ID = EMP_NO AND ROWNUM = 1) AND ROWNUM = 1 ) as "Emergency 1st Person Phone", 

(select RELATIVE_NAME from (SELECT (ROW_NUMBER() OVER (ORDER BY RELATED_ID)) as RN, PR.* FROM PERS_RELATED PR WHERE PERSON_ID = EMP_NO) where RN = 2) as "Emergency 2nd Person Name",

(select RELATIVE_PHONE from (SELECT (ROW_NUMBER() OVER (ORDER BY RELATED_ID)) as RN, PR.* FROM PERS_RELATED PR WHERE PERSON_ID = EMP_NO 
        AND RELATIVE_NAME = (select RELATIVE_NAME 
                                from ( 
                                    SELECT (ROW_NUMBER() OVER (ORDER BY RELATED_ID)) as RN, PR.* 
                                    FROM PERS_RELATED PR 
                                    WHERE PERSON_ID = EMP_NO) 
                                where RN = 2)) 
                            ) as "Emergency 2nd Person Phone"

FROM COMPANY_PERSON_ALL CPA

--LEFT OUTER JOIN Project_access_result2 PAR
--ON CPA.EMP_NO = PAR.EMPLOYEE_ID

LEFT OUTER JOIN PERS PER
ON CPA.EMP_NO = PER.PERSON_ID

--WHERE CPA.EMP_NO = ''
ORDER BY CPA.INTERNAL_DISPLAY_NAME
--GROUP BY CPA.FNAME, CPA.LNAME

 

  1. How can i solve this issue?
  2. I am creating reports for users, some data may come from the modules, pages or windows which they don't have permission but the logic behind the creating report is to allow them to see the data they need not whole module or all the data, so how can i solve this issue by not giving them the permission for relevant modules, pages or windows etc.

Thanks.

Kinds.

I couldn’t solve this, added AO. and IFSAPP. to Infront of APIs that i am using, but nothing changed.

I think i solved it, i created new user with the same privileges of the USER who wants to use this report, and i connected to db with the new created user to see what is wrong when i run the query, i started to add IFSAPP. Infront of every error by one by and at the end i got insufficient privileges error for 

Project_access_result2

than i gave permission in “Database Objects” in Permission Sets, and problem solved for the new created user.

Can you tell me if adding IFSAPP. Infront of every table and giving permission to relevant table which user doesn’t have access is right? I always doubt about it.

Reply


Terms & ConditionsCookie settings