Home » RDBMS Server » Security » Restricting the user from logging in from SQL*PLUS (9.2.0.7)
Restricting the user from logging in from SQL*PLUS [message #343850] Thu, 28 August 2008 06:07 Go to next message
nazbrian
Messages: 36
Registered: July 2008
Member
Hi,

I have a appliation. I do not have the option to customize the application.

I want to restrict the user from logging in to DB from SQL*PLUS or SQL Developer. But, I want to enable him only from the Application.

How do I acheive this.

Is there any easier way other than Secure Application roles (like trigger...)

Brian.
Re: Restricting the user from logging in from SQL*PLUS [message #343885 is a reply to message #343850] Thu, 28 August 2008 07:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Secure Application Role IS the way to do it.
Other way can be workaround.

Regards
Michel
Re: Restricting the user from logging in from SQL*PLUS [message #343937 is a reply to message #343850] Thu, 28 August 2008 08:36 Go to previous messageGo to next message
nazbrian
Messages: 36
Registered: July 2008
Member
Hi Michel,

I would be thankful, if you could give me a link for an example for Secure Appl Role.


Brian.
Re: Restricting the user from logging in from SQL*PLUS [message #344021 is a reply to message #343937] Thu, 28 August 2008 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.oracle.com/pls/db102/search?remark=quick_search&word=secure+application+role&tab_id=&format=ranked

Regards
Michel
Re: Restricting the user from logging in from SQL*PLUS [message #344499 is a reply to message #343850] Sat, 30 August 2008 01:27 Go to previous messageGo to next message
nazbrian
Messages: 36
Registered: July 2008
Member
Hi,

I have created a test for Secure Application Role in
Oracle 9.2.0.7.

My intention is to restrict the user OWB_TARGET from logging in from specific IP through TOAD.

I'm beginner for these Secure Appl Role.

CREATE ROLE sar_role IDENTIFIED  USING boidw.login_restrict  ;

CREATE OR REPLACE PACKAGE login_restrict AUTHID CURRENT_USER
AS
	PROCEDURE set_role;
End;

CREATE OR REPLACE PACKAGE BODY login_restrict
AS 
	PROCEDURE set_role AS
		mod_out VARCHAR2(48);
		act_out VARCHAR2(32);
	BEGIN
		dbms_application_info.read_module(mod_out, act_out);

		IF SYS_CONTEXT('USERENV','CURRENT_USER') in ('OWB_TARGET') 
			and mod_out in ('T.O.A.D.')
			and SYS_CONTEXT('USERENV','IP_ADDRESS') = '172.16.6.128'
		Then
			dbms_session.set_role('sar_role') ;
		End If;
	END;
END login_restrict; 


What is wrong in this code ??

Thanking you in advance,

Brian.
Re: Restricting the user from logging in from SQL*PLUS [message #344508 is a reply to message #344499] Sat, 30 August 2008 02:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Module can be changed.
Use positive check and not negative one: limit the access to the application, don't try to forbid the access for tools.
More precisively check if you really are in your application to enable the role.

Regards
Michel
Re: Restricting the user from logging in from SQL*PLUS [message #344745 is a reply to message #343850] Mon, 01 September 2008 02:58 Go to previous messageGo to next message
nazbrian
Messages: 36
Registered: July 2008
Member
Hi,

Quote:

More precisively check if you really are in your application to enable the role.



Could you please give me a code snippet for the above.

Regards,
Brian.
Re: Restricting the user from logging in from SQL*PLUS [message #344756 is a reply to message #344745] Mon, 01 September 2008 03:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For instance, the application can set a secret key in a context variable that the procedure can check.

Regards
Michel
Re: Restricting the user from logging in from SQL*PLUS [message #344797 is a reply to message #343850] Mon, 01 September 2008 05:05 Go to previous messageGo to next message
nazbrian
Messages: 36
Registered: July 2008
Member
Hi Michel,

I can not customize the application, it is a standard product from ORACLE (Oracle Financial Applications)

Brian.
Re: Restricting the user from logging in from SQL*PLUS [message #344819 is a reply to message #344797] Mon, 01 September 2008 05:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So how can you call the procedure to set the application role (I precise I don't know anything about Oracle Financials)?

Regards
Michel
Re: Restricting the user from logging in from SQL*PLUS [message #347399 is a reply to message #343850] Thu, 11 September 2008 08:46 Go to previous messageGo to next message
nazbrian
Messages: 36
Registered: July 2008
Member
Hi Michel,

Sorry for pasting code from net.

When the user logs into the application, where the control flows in to the code block given below (Assuming the following Roles & packages etc created as given below).


Create the roles as application roles and specify the authorized package that will enable the roles. In this example, hr.hr_admin is the example authorized package.
CREATE ROLE admin_role IDENTIFIED USING hr.hr_admin;
CREATE ROLE staff_role IDENTIFIED USING hr.hr_admin;

Note:

You need to set up the following data structures for the examples in this section to work:
CREATE OR REPLACE PACKAGE hr_logon IS
PROCEDURE hr_set_responsibility;
END;
/

CREATE OR REPLACE PACKAGE BODY hr_logon IS
PROCEDURE hr_set_responsibility IS
   BEGIN
      DBMS_SESSION.SET_IDENTIFIER (1234);
   END;
END;
/

Create an invoker's right procedure.

/* Create a dedicated authentication function for manageability so that changes in authentication policies would not affect the source code of the application - this design is up to the application developers */
/* the only policy in this function is that current user must have been authenticated using the proxy user 'SCOTT' */
CREATE OR REPLACE FUNCTION hr.MySecurityCheck RETURN BOOLEAN 
AS
BEGIN
    /* a simple check to see if current session is authenticated                           
    by the proxy user 'SCOTT' */
    if (sys_context('userenv','proxy_user') = 'SCOTT')
    then
        return TRUE;
    else
        return FALSE;
    end IF;
END;

GRANT EXECUTE ON hr.MySecurityCheck TO PUBLIC;

/*Create the procedure*/
CREATE OR REPLACE PACKAGE hr_admin
AUTHID CURRENT_USER
IS
PROCEDURE hr_app_report;
END;
/
CREATE OR REPLACE PACKAGE BODY hr_admin IS
PROCEDURE hr_app_report IS
BEGIN
    /* set application context in 'responsibility' namespace */
    hr_logon.hr_set_responsibility; 
    /* authentication check here */
    if (hr.MySecurityCheck = TRUE)
    then
         /* check 'responsibility' being set, then enable the roles without
         supplying the password */
         if (sys_context('hr','role') = 'admin' ) 
    then
         dbms_session.set_role('admin_role');
    else
         dbms_session.set_role('staff_role');
        end if;
    end if;
END;
END;


Regards,
Brian.

[Updated on: Thu, 11 September 2008 09:12] by Moderator

Report message to a moderator

Re: Restricting the user from logging in from SQL*PLUS [message #347810 is a reply to message #347399] Sun, 14 September 2008 02:37 Go to previous messageGo to next message
NandKumar
Messages: 92
Registered: June 2007
Location: v$hyderabad
Member
Hi Brian, to prevent user from logging to the DB, we can use product user profile.
Re: Restricting the user from logging in from SQL*PLUS [message #347828 is a reply to message #347810] Sun, 14 September 2008 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Does not work with SQL Developer.

Regards
Michel
Re: Restricting the user from logging in from SQL*PLUS [message #348529 is a reply to message #343850] Wed, 17 September 2008 02:41 Go to previous message
nazbrian
Messages: 36
Registered: July 2008
Member
Hi,

I could not implement Secure Application Role, Basically I did not understand.

I'm Really thankful if anybody gives me a sample code for 9.2.0.7.

For example, user SCOTT should not be allowed to login to SQL Developer rather he should be allowed to login to SQL*Plus.

Thanking you in advance.

Brian.

Previous Topic: GRANT SELECT ON TABLE(s) FOR ALL USERS
Next Topic: transparent Data encryption
Goto Forum:
  


Current Time: Thu Mar 28 08:50:32 CDT 2024