Different user for login and database access
Robert Simpson, October 07, 2006 - 10:36 am UTC
Sounds like he may be able to do what he wants with Enterprise Security. User "TOM" could be set up in the LDAP directory, and mapped to "TOM_SECURE" in the database. The user in the database would be a "CREATE USER ... IDENTIFIED GLOBALLY ..." type of user and therefore would not have a password - the connection to the database would be authenticated via a secure (SSL) connection to the LDAP server (we use Oracle Internet Directory - OID). That has the added advantage that each user's password is stored only in one place regardless of the number of databases accessed. In the scenario presented in the original question, each user would have two passwords for every database, all potentially expiring and changing at different intervals based on those databases' password expiration policies. The disadvantage is that the SSL connection between each database and the LDAP server requires a certificate, but Oracle provides the utilties that you can use to create a self-signed certificate for your own certificate authority (CA) which can then issue the certificates for the databases (for free - you can also use those utilities to issue free certificates for web servers, which works well for internal applications where you have enough control over the clients which are connecting via https to import the CA certificate into their browser).
Additional password protection for table access
Robert Simpson, October 07, 2006 - 10:45 am UTC
Or much easier, create a password protected non-default role.
CREATE ROLE TOM_SECURED IDENTIFIED BY password_secured;
GRANT ...table privs... TO TOM_SECURED;
GRANT TOM_SECURED TO TOM;
CONN TOM/password_of_tom;
in PL/SQL:
SET ROLE TOM_SECURED IDENTIFIED BY password_secured;
TOM would not be able to access the tables until he entered the password for TOM_SECURED.
October 07, 2006 - 11:03 am UTC
but that is NOT becoming another user.
And if you can set it in plsql, you can set it in sqlplus just the same.
Not sure where you are going with this one (although, if you mix SECURE APPLICATION ROLES with N-Tier Proxy authentication - then you got something, but NOT from within plsql - only from an outside client that connects to the database)
How to change user dynamically
Chandra sing, February 10, 2009 - 12:16 pm UTC
Hi
I look for the same solution.In oracle forms we use one user with only connect privileges and in the PRE_FORM trigger change to user Y who has all the tables.
In C/S ,the session will be same for all the forms but, if you use that same logic in the plsql it is not the case even if it is possible.
Ex. user X has connect privilege and has a so called procedure my_proc with change user option(assumption only).
Now the security issue is , anyone can login using sqlplus and connect X/password and execute the procedure my_proc will get all the access to the user Y.
Anyway it is not going to serve the purpose. Please share if you have other thought other than this which will be a great help.
February 11, 2009 - 9:28 am UTC
... Please share if you have other
thought other than this which will be a great help. ....
sure I have thoughts - design before build, not the other way around?
What you have done is so dangerous - you use this common user Y in a CLIENT SERVER application. All I have to do is hit "enter query" mode, and use # in a field to start SQL injection the **(#$@ out of you. Think about this, the user can submit ARBITRARY SQL from forms - not just from sqlplus.
So, it would not matter if you fix "sqlplus", you still are hosed.
... Ex. user X has connect privilege and has a so called procedure my_proc with
change user option(assumption only).
...
that does not make sense, a stored procedure (stored and compiled in the database) cannot change the currently logged in user credentials. It could enable roles, but it CANNOT change the user
db links or jobs may hold the answer
Jona, December 03, 2009 - 7:22 am UTC
instead of using roles, If he has specific stored procedure he want's to run with a different user can't he just use db link?
I have used this mechanism for a different purpose, to run statistics on ETL source database's indexes, but I believe it can help him as well.
execute immediate 'begin dbms_stats.gather_index_stats@stg.source_dblink(OWNNAME => ''' || p_schema_name || ''',INDNAME => ''' || p_index_name || ''',ESTIMATE_PERCENT => 20); end;';
i ran dbms_stats.gather_index_stats through source_db link at stg schema that connects with user app to a different database with app's privileges.
so you can create a db link with tom_secured in the same database then run a stored procedure with that user. the only problem is that once the procedure / query ended the session will be gone.
What more is that the tom unsecured user must have privileges for the db link so maybe it's not really top security level but it can still protect you, but anyhow if you wanted to switch while running so it's unsecured base line...
Another thing you may want to try is to run external procedure from the database (old jobs or easy with scheduler) that reconnects to the database and do whatever. though still you'll need to transform parameters to it by scheduler arguments or database. Another plus is that you will be able to chose running synchronized / synchronized.
Enjoy
Jona
December 04, 2009 - 3:11 pm UTC
that would be a performance hit and a half. I would not want to use a loopback database link for every calls..
In the 21st century - had I been answering this then, I would have pointed them to:
http://docs.oracle.com/docs/cd/B19306_01/java.102/b14355/proxya.htm#CHDHHAAD tom would connect, tom would be give the privilege to connect through to tom_secured. Tom could in effect become tom_secured. The need to have tom_secured even have an encrypted password totally disappears.