I am also looking for this that how it is possible that i restrict the user to access the database through software which is in dev 6i. I put almost every check to there in 6i. But I am still confusing that if log on through sql plus he can damage the database.

Although I create a self password form for athentication, it works fine and i am not giving the user the original db password but any user who have dba privilage(like system) can access the user 's scheema through aqlplus or other softwareeven he can delete it ...

Now how can I prevent it (triggers or not???)

The true, at least in NT, if you have access to server, you have access to the database,

To give some security using form I do the following
1) basic role, this role has the minimum to connect and enable a role.
2) applicatoin role, with password, it must be enabled.
3) schema users, that users are "locked", to connect between database I user other users.

Tom,

To prevent people from connecting via sql*plus, can't we use the product_user_profile table to enforce that? Or I am missing something?

Arup

What do you think of password mangling?
When a user logs into our app, before they are logged into the database, the app logs into the database as a user with access to only one package.
The package is used to create a hash out of the username and password that the user entered.
The app then logs the user in using their username and the hashed password.
The users therefore do no know their own passwords and can't log in using other tools.
Of course, if they get hold of the app password and figure out the algorithm, they can log in as the app, get the hash, then log in as themselves.
Not genius proof, but at least they can't just stumble in.
Chuck

OK "i
hope they aren't savvy enough to be able to figure it out"
it is untill we get on 9i. ;)

How does "secure application roles and ntier
proxy authentication" prevent a savvy front-end user from using sqltrace and a debugger to open it up?

Tom, what would you recommend for having the middle-ware authenticate itself? My java coworkers are telling me that standard practice is to store the database password in a config file on the server, but it seems this is as risky as storing a role password in a config file on the server.

Hrm, maybe I should describe what I've been planning to do...

Rather than create our own system for managing user logins for our webapp, I'm giving users accounts in oracle, and various application roles (right now we just have an admin and a user role). Admins need to be able to add users, so they have alter, create, and drop user granted to that role.

But I don't want admins to be able to arbitrarily connect to the database via any tool and start dropping users at will; I only want them to have the admin role when logged in via our application. It seems the best way to do this is to login via a proxy account and have a stored proc verify that before allowing the user to enable the admin role.

I'm using user administration as the example here, but there's plenty of other things this will extend to; I'd like to use VPD features to limit visibility to certain things based on what role a user has, for example.

Ideally, the webapp would just pass the user's credentials on to Oracle and let it decide if the connection is to be allowed or not, but then it seems that there's no way to limit user's ability to activate these roles that they should only be using via our application. Of course n-tier proxy auth doesn't allow for that, but our application could at least attempt to connect to oracle as the user before it re-connects as itself and proxies in as the user.

I hope this makes sense... Is there a better way to limit access to the application roles that doesn't involve n-tier authentication? Or am I heading down the wrong road entirely?

Sorry, that paragraph is most unclear. What I was trying to say is that I'd like to use the n-tier feature of using a procedure to validate that the user can access the admin role, but also use the user's actual credentials to login to the database. Is there a way to login to the database via the application using the user's credentials *and* ensure that they can only activate these roles if they login via the application?

Yes, I've thought about using AUTHID DEFINER for doing these things, but it looks like that might be a fair amount of extra work with how things are structured right now. I'm using Oracle Objects for most of the API to the database, and I've implemented methods in the objects to handle the corresponding DML that would be needed to store the information in the actual tables (I'm not storing objects in tables). Unfortunately, there doesn't appear to be any way to grant permissions to individual methods of objects; either you can do everything in the object or nothing. Because of this, I'm granting permission to execute DML on the base tables themselves, based on the different roles. Also, I want to be able to use VPD features to limit what a user can see, and my understanding is that the only way to do that is if procedures either do all the checking themselves (sounds like extra work and a source for bugs), or they run as the current user and let the VPD stuff handle everything.

So, while I can change my user management code to handle permissions on it's own, I certainly don't want to be doing that for everything else. Granted, at least with a VPD even if someone did get access to the tables outside the application and could run arbitrary DML, they'd only be able to modify their own stuff. But I'd still feel better if they were forced to do all DML through the API I've defined, even though I use RI, constraints, triggers, etc. to enforce rules wherever I can.

So...

Is there any way to have n-tier auth require the end-user's credentials as well as the middleware's credentials?

Or, is there another way to secure the application roles that doesn't require n-tier auth? Maybe storing role passwords in a table that only the middleware has access to (I know... yuck)?

Thanks so much for your time!

Tom

Thanks for your valuable response as usual.

Please comment on the role of digital certificates (dc)installed on the client desktop, in addition to other methods of authentication?

Is a dc relevant/applicable at all? Would it be easily setup, stolen etc?

Regards
Hien

Hi Tom,

We have a Forms application which currently has all privileges (select, insert, update, delete) granted to public. All security is controlled within the application by enabling and disabling menu selections etc.

My task is to implement some form of security within this mess, however they are not prepared to go down the FGAC path due to the amount of work involved.

The best I can come up with is to use password-protected application roles which would be enabled with a call to set_role within a Forms startup trigger. I know it's not totally secure due to the issues outlined above but is there anything further that can be done given their reluctance to implement FGAC?

Thaks for your help.

We overcame the problem by encrypting the application password. I.e. all application passwords were initially reset to match the username but encrypted. When the application user next logs on they are forced to change their password. This password will only be recognised by the application. If the user tries to log on via sqlplus, toad, etc with their application username and password the connection will fail.

The application runs on a dedicated server and someone has suggested checking the logon server in a global database trigger, if it's the server which runs the application then let them log in otherwise refuse the login attempt. Do you see any problems with this approach?

The Q & A gave me a good idea of what can and cannot be done, and how effective the things that can be done are.
I would certainly recomment this. It was more helpful than just searching on Metalink.

Tom,

We have similar situation and would like to prevent non-IT user accessing Database directly from sqlplus/Toad/ODBC. In your answer to this thread you mentioned use of secure application role,
---> from your answer---
so, now, a stored procedure can say:


if TOM_KYTE is the real user
AND
the PROXY USER IS "HR"
then
enable role
else
fail
end if


TOM_KYTE identifies himself to the middle tier using username/password, x509
PKI, whatever...
------------------>

We have almost 30000 database users outof which we have 500 IT users, we donot want to restrict IT user from accessing db via ODBC/Toad/SQLplus. Does this mean I have to write 30000 IF then statement?

We are also planning to install OID, would you recommend us what is the best way to move from database user to OID based users?

Tom,

How does Oracle Applications handle this?
Doesn't Oracle Apps use the APPLSYSPUB user somehow to establish privs for each particular user?

Thanks,

Robert

Tom,

Oracle 8.1.7.4
Custom Forms 6i client/server application.

Based on the understanding that in 8i there is no air-tight way to separate 'application' security from 'sqlplus' security...

Would you please comment on this approach?

1) Users must have update, etc. privs on the application's tables (no way around this!)
2) Turn on auditing for the application's tables (This keeps everyone 'honest')
3) Export application's schema daily (This provides some help against 'mistakes').

Isn't this about as good as we can get?
What do you think?

Thanks,

Robert.

Tom,

The export would be in case someone accidentally deleted half a table by mistake.
With an export you might be able to reconstruct the table.

How would you use a hot backup to reconstruct a single table?

Thanks!

Robert

Tom,

<QUOTE>
a competent dba can use a hot backup to restore ANY piece or slice of data in the database. (they would restore a mini instance, just system, the tablespace they need and rollback and grab whatever they needed out of it)
<QUOTE>

This is terrific info!!!
I will definitely be practicing this in my 'sand-box' databases!!

Thank you, sir!

Robert

If all you want to do is restrict users to performing pre-defined actions, and perhaps having an audit trail of these actions, you can have all updates to tables trapped by Table API triggers. The code defined for these actions within the triggers then restricts what users can do.

Is this a valid, albeit limited, form of security and how hard is it to work around it?

I tried to create Secure Application Role in our database guess what: Feature not enabled! It is an standard edition database. This is like saying to small and mid size businesses that you don't need proper security.

Initially we ran a VB utility that resets the user password to an encrypted value of the user name. The next time the user logs on they enter their username as the password and are then forced to change their password to a new value.

Subsequently the user can only log on via the VB Front-end as this is only way that the password can be validated. Any attempts to log on using sqlplus, access, etc fail with incorrect password.

Changing the password using dba tools also fails, as the VB Front-end has not encrypted the new password. So when the user enters the new password access is again denied.

All users names are stored in an application table as well as oracle. The VB encrypts the incoming password then connects the user to oracle, which in turn checks the previous encrypted password.

E.g. user fred password bloggs. The VB checks that fred is a valid user by checking the application table and then encrypts the password bloggs into 12x45b. The user then connects to oracle, via the VB, and the password is checked. They match as the password was previously set after being encrypted by the VB. Therefore if fred tries to log on with an application other than the VB front-end bloggs is not encrypted and the passwords will not match.

The user can change their own password and a superuser can reset it. Previously when a user forgot their password it could be changed by the dba. This is no longer possible, as the password will not be encrypted. Password maintenance can only be done via the VB front-end or the VB utility previously mentioned.

It's not hard to guess when the answer (sqlnet tracing) is already on your site :

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:2886797089063

Also, for windozey people, there's SQL Monitor which does pretty much the same thing, but is more point-and-click.

http://www.toadsoft.com/downld.html <code>

If I understand the requirement properly, the poster basically wants the ability to prevent users from accessing the database from SQL Plus etc., and only allow access via the front-end app. If that's it then how about an after logon system trigger that checks to see if you have permission to access the database via that particular application? For example, create a table (lets call it APPS_ALLOWED) that stores the userids and the applications using which the user can access the database. This table will need to be stored in a secure account such as the DBA's accout. Next, create an after logon trigger (which is fired right after you log in). In this trigger, check to see whether the program the person used to log in (found in v$session) is in the list of authorized applications stored in the APPS_ALLOWED table. If it is, welcome to the database, if not, raise an exception/error. Because the after logon trigger fails, the person's session is aborted and he/she is not allowed inside the database. I've seen this done in some applications although I'm curious to know your thoughts, Tom, about the use of such a technique to prevent access from unauthorized apps. Thanks.

Kashif

What about that? Even if I change the sqlplus name to xpls, I guess module will be always 'SQL*Plus'.

USERNAME PROGRAM MODULE
--------------- ------------------------- ------------------
OPS$MPORTES xpls@lath09 (TNS V1-V3) SQL*Plus

Could we code something to check it out and kick them off? Right?

sqlplus is calling dbms-application-info and setting the module, the action and the client.

i can, from sqlplus just call dbms_application_info myself.

ops$tkyte%ORA10GR2> select module from v$session where sid = (select sid from v$mystat where rownum=1);

MODULE
------------------------------------------------
SQL*Plus

ops$tkyte%ORA10GR2> exec dbms_application_info.set_module('fooled','you');

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select module from v$session where sid = (select sid from v$mystat where rownum=1);

MODULE
------------------------------------------------
fooled
 

Great! Thank you.

I have heard changing the password for APPLSYSPUB user on Oracle 11i is not so easy. Do you know how to achive this?

Thanks,

Hi Tom,

What would you think of this approach using secure roles and proxy users :

conn / as sysdba

-- This user will own application objects.
create user app_owner identified by app_owner default tablespace ORA temporary tablespace TMP;
grant connect,resource to app_owner;
grant create procedure, create role, create session, drop any procedure, drop any role, select any dictionary to app_owner;


create user app_user identified by app_user default tablespace ORA temporary tablespace TMP;
alter user app_user default role none;

-- end_user does not need to have a valid password
create user end_user identified by values 'NO_PASSWORD' default tablespace ORA temporary tablespace TMP;
grant create session to end_user;
alter user end_user grant connect through app_user ;



conn app_owner/app_owner

create role secure_role identified using secure_role_package;
grant secure_role to app_user;

create or replace package secure_role_package authid current_user
as
  procedure check_and_activate_my_role;
end;
/

create or replace package body secure_role_package
as
  procedure check_and_activate_my_role
  is
    v_ipaddress varchar2(15);
    v_real_user varchar2(50);
    v_proxyuser varchar2(50);
    v_client_id varchar2(50);
  begin
    v_ipaddress:=SYS_CONTEXT('USERENV','IP_ADDRESS');
    v_real_user:=upper(SYS_CONTEXT('USERENV','SESSION_USER'));
    v_proxyuser:=upper(SYS_CONTEXT('USERENV','PROXY_USER'));
    v_client_id:=SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER');

    if v_ipaddress='191.254.41.211' -- IP address of the app server
       and v_real_user='END_USER'
       and v_proxyuser='APP_USER'
       and v_client_id='cVat5h1Zm$z9x8OZZtYj#7fYEk7mdX1NaTdJ9s24UQMIBUSOme' -- Only the client app knows that passphrase 
    then
      sys.dbms_session.set_role('secure_role');
    else 
      raise_application_error( -20001, 'Pas de bras, pas de chocolat!' );
    end if;
  end;
end;
/

grant execute on secure_role_package to end_user;


create table my_tab1 (col1 number, col2 varchar2(10));
insert into my_tab1 values (1,'aaaaaaaaaa');
insert into my_tab1 values (2,'bbbbbbbbbb');
insert into my_tab1 values (3,'cccccccccc');
commit;

grant select,insert,delete,update on my_tab1 to secure_role;


-- can connect only through proxy user on machine 191.254.41.211
connect app_user[end_user]/app_user@my_base
exec dbms_session.set_identifier('cVat5h1Zm$z9x8OZZtYj#7fYEk7mdX1NaTdJ9s24UQMIBUSOme');
exec app_owner.secure_role_package.check_and_activate_my_role;
select * from app_owner.my_tab1;



-- you cannot connect directly as app_user.
SQL> conn app_user/app_user@my_base
ERROR:
ORA-01045: user APP_USER lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.
SQL>


-- you cannot login as end_user either because there is no valid password.

For example: One could have created app_owner's objects without being logged on as sys_owner ...

Tom, you say above "do not use connect, resource" but you do not give much detail about "why".

1) Could it be because these are generic roles?

In this case if you want app_owner to create its own stuff, app_owner would need "create session" privileges, right?

2) I must admit I have been creating app_owners objects 'as someone else'. I was trying to distinguish between rights needed to install (which go to the super user) and rights needed to execute (which app_owner doesn't need at all). I do see your point and will rethink. As a side benefit, it will be easier to see app_owner objects in SQL Developer if I can log in directly as him.

Now suppose I do want to prevent anyone but the super user from effectively updating the app_owner schema? Would proxying app_owner through the super user accomplish that?

Much better answer than my question, thanks!

I generally have one super-duper user per instance with all possible privileges in order to avoid using SYS/SYSTEM. I gather from your answer that this guy should be used only to manage users, roles and grants, leaving object creation to the object owners.

Should SYS and/or SYSTEM be audited fully as well? Any performance drawbacks?