ROLES /privilege problem PLZ HELP

anirudh · 10-08-2004, 06:51 AM

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

vasudevadas · 10-08-2004, 04:58 PM

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?

anirudh · 10-21-2004, 09:22 AM

I tried this but it does not work

vasudevadas · 10-21-2004, 01:21 PM

No output?

anirudh · 10-21-2004, 03:30 PM

no output

vasudevadas · 10-21-2004, 04:07 PM

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;