This discussion is archived
5 Replies Latest reply: May 1, 2013 10:54 PM by Hari_639 RSS

Apex IR report page with download(specific) column security

Ramani_vadakadu Journeyer
Currently Being Moderated
i have IR report page and download column as blob,how can security applied in specific column wise?
e.g. Report page have more rows, and applied query in condition.
Select ID,dbms_lob.getlength(Blob_file) Download  from Document_master
 where Created_by=UPPER(:APP_USER) 
 OR
  (exists (select ''   from   apex_workspace_group_users awgu
                where  awgu.user_name =:app_user  AND awgu.GROUP_NAME='EMPLOYEE_GROUP'
                )
                             )
Now all the rows with Download column to see EMPLOYEE_GROUP users,but i need control the download column only except Created_by=UPPER(:APP_USER) ,this case how can do the security.

Thanx,
Ram

Edited by: Ramani_vadakadu on Apr 24, 2013 2:59 PM
  • 1. Re: Apex IR report page with download(specific) column security
    Hari_639 Guru
    Currently Being Moderated
    Hello Ram,

    I can't understand your query, can you re-phrase it?

    If you want to display some columns only for some "Users" then you can either use "Conditional Display" or "Authentication Scheme".

    If you want to display data based on "User + Row Data" then you can use CASE statement in your query.

    Regards,
    Hari
  • 2. Re: Apex IR report page with download(specific) column security
    Ramani_vadakadu Journeyer
    Currently Being Moderated
    hai Hari,
    I need security in column wise, I tried condition)(Download as column ) display as PL/SQL Exp :P2_Created_by=UPPER(:APP_USER) ,its not showing at all of rows!
    my Req is:
    all user can upload their document and download,but another user can see only rows without download link from original uploader document.
    can you suggest any idea?

    Ram
  • 3. Re: Apex IR report page with download(specific) column security
    Hari_639 Guru
    Currently Being Moderated
    Hello Ram,

    Then you have to create your own function to download files from your table and use CASE statement to display "Download Link" or NULL based on CREATED_BY user and logged in user.

    See Downloading Documents from the Custom Table

    Regards,
    Hari
  • 4. Re: Apex IR report page with download(specific) column security
    Ramani_vadakadu Journeyer
    Currently Being Moderated
    Hai hari,

    I created the following proceedure,how can call and apply the column wise security? already download option working fine,can you guide plz.
    CREATE OR REPLACE PROCEDURE TOPMGT.download_my_file(p_file in number) AS
            v_mime  VARCHAR2(48);
            v_length  NUMBER;
            v_file_name VARCHAR2(2000);
            Lob_loc  BLOB;
    BEGIN
    
            SELECT NWM_DOC_MIME_TYPE, Blob_file, NWM_DOC_FILE_NAME,DBMS_LOB.GETLENGTH(Blob_file)
                    INTO v_mime,lob_loc,v_file_name,v_length
                    FROM Document_master
                    WHERE ID = p_file;
                  --
                  -- set up HTTP header
                  --
                        -- use an NVL around the mime type and 
                        -- if it is a null set it to application/octect
                        -- application/octect may launch a download window from windows
                        owa_util.mime_header( nvl(v_mime,'application/octet'), FALSE );
     
                    -- set the size so the browser knows how much to download
                    htp.p('Content-length: ' || v_length);
                    -- the filename will be used by the browser if the users does a save as
                    htp.p('Content-Disposition:  attachment; filename="'||replace(replace(substr(v_file_name,instr(v_file_name,'/')+1),chr(10),null),chr(13),null)|| '"');
                    -- close the headers            
                    owa_util.http_header_close;
                    -- download the BLOB
                    wpg_docload.download_file( Lob_loc );
    end download_my_file;
    /
    IR region with following case statement.
     
    CASE
        WHEN CREATED_BY =:APP_USER
        THEN 'red'
         
        WHEN CREATED_BY='NULL'
        THEN 'BLUE'
      END the_color,
    
     I created an Item> Column Formatting <span style="color:#the_color#;font-weight:bold;">#CREATED_BY#</span>
    Edited by: Ramani_vadakadu on May 1, 2013 6:38 PM
  • 5. Re: Apex IR report page with download(specific) column security
    Hari_639 Guru
    Currently Being Moderated
    Hello,

    You have to create a page which will be just used to download the documents. Look for "Create Download Page for Embedded PL/SQL Gateway" section @ How to Upload and Download Files in an Application and follow steps specified to create such page.

    Let's assume you have created such page, and page number - 10 and item holding PK of document P10_FILE_ID, then your query will looks as follows..
    SELECT dm.id, dm.created_by,
         CASE 
              WHEN UPPER(dm.created_by) = :APP_USER
              THEN '&lt;a href="f?p=&APP_ID.:10:&APP_SESSION.::::P10_FILE_ID:'||dm.id||'" title="Download">Download</a>'
              ELSE NULL
         END download
    FROM document_master dm
    And set "Display Type" for "Download" column as "Standard Report Column"

    Regards,
    Hari

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points