ProgrammingThis forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.
Notices
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
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?
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?
Last edited by vasudevadas; 10-08-2004 at 04:59 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;
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.