LinuxQuestions.org - ROLES /privilege problem PLZ HELP

- Programming (https://www.linuxquestions.org/questions/programming-9/)

- - ROLES /privilege problem PLZ HELP (https://www.linuxquestions.org/questions/programming-9/roles-privilege-problem-plz-help-240175/)

ROLES /privilege problem PLZ HELP

i want to allow the access to a particular table (thru roles), only if the

connection call from a particular IP address, otherwise on logon set the role

of the account to a different one, where the table access is not possible.

Using the secure application role strategy.Performed the following steps:

owner for the tables -- xyz_owner
user for the tables -- xyz_test
1. Created 2 roles
hr_data_role -- all 10 tables
hr_nodata_role -- only 9 tables
2. created a packaged procedure to check the ip-address as 'xyz_owner'
-- Package spec
CREATE OR REPLACE PACKAGE Hr_Data_Admin_Pkg
AUTHID CURRENT_USER
IS
PROCEDURE Hr_Data_View_Pr;
END;
/
-- Package body
CREATE OR REPLACE PACKAGE BODY Hr_Data_Admin_Pkg
IS
PROCEDURE Hr_Data_View_Pr
IS
BEGIN
IF (sys_context('userenv','ip_address') = '10.10.10.10')
THEN
dbms_session.set_role('hr_data_role');
ELSE
dbms_session.set_role('hr_nodata_role');
END IF;
END Hr_Data_View_Pr;
END Hr_Data_Admin_Pkg;
/
3. Created an after logon trigger as 'sys' user
create or replace trigger logon_set_role_xyz
after logon on xyz_test.schema
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION; --!!!
begin
hr_stage.hr_data_admin_pkg.hr_data_view_pr;
commit;
end;
4. -- roles ire assigned to the user 'xyz_test'
-- execute permission on the procedure was given to the user 'xyz_test'
when I connect to the schema from 10.10.10.10 server or any other machine (with
different ip address), the log on trigger is getting executed. But, from a
different ip address (not 10.10.10.10), still i are able to describe the
table. Looks like the packaged procedure is not working. If a direct call is
made to packaged procedure, then the role options are working fine and table
access is restricted.
COULD YOU GIVE ME A DIFFERENT SOLUTION TO THE PROBLEM
Can you please suggest an alternative way to implement the
functionality?

regards
anirudh

Let's do some debugging then.

Code:

CREATE OR REPLACE PACKAGE BODY Hr_Data_Admin_Pkg

IS

PROCEDURE Hr_Data_View_Pr

IS

BEGIN

dbms_output.enable(1000000);

dbms_output.put_line(sys_context('userenv','ip_address'));

IF (sys_context('userenv','ip_address') = '10.10.10.10')

THEN

dbms_output.put_line('setting role HR_DATA_ROLE');

dbms_session.set_role('hr_data_role');

ELSE

dbms_output.put_line('setting role HR_NODATA_ROLE');

dbms_session.set_role('hr_nodata_role');

END IF;

END Hr_Data_View_Pr;

END Hr_Data_Admin_Pkg;

Try logging in from a remote machine and let me know what output you get, if any.

To be honest though, I would just create two schema logins: one for you to use from the server which has the HR_DATA_ROLE, and the other for everyone else to use from wherever they are, with the HR_NODATA_ROLE. Is there a reason why you can't do this?

I tried this but it does not work

Could try writing the debug messages to a file using the utl_file package, instead of trying to write them to stdout with dbms_output (which clearly isn't working).

In case you're not familiar, you use utl_file like this:

Code:

DECLARE

  ...

  fp utl_file.file_type;

  ...

BEGIN

  ...

  fp := utl_file.fopen('/path/to/directory', 'filename', 'W'); -- 'W' for writing, 'R' for reading

  ...

  utl_file.put_line(fp, 'A line of text for the file');

  ...

  utl_file.fclose(fp);

  ...

END;