Home » RDBMS Server » Security » Moving Application Security to Oracle from Sybase (Oracle 11g)
Moving Application Security to Oracle from Sybase [message #472076] Wed, 18 August 2010 12:28 Go to next message
drduquette
Messages: 18
Registered: August 2010
Location: Florida
Junior Member
In Sybase, my application was using system tables to perform application login security. Those tables obviously don't exist in Oracle. I am looking for ways to provide the following functionality in an Oracle world:

1. How to determine 'x' days of inactivity based on "last login date"?

2. How to determine when a new user logs in for the first time and force them to change their password?

3. If we need to reset a users password, how can we require the user to change their password?

4. Is there any other option other than storing a user-id/password in the application code for locking a user's account if their account needs to be locked due to inactivity?

5. In the USER_USERS view there is a status column. Does anyone know what the different status's can be?

Any thoughts are welcomed...

Thanks in advance
Re: Moving Application Security to Oracle from Sybase [message #472078 is a reply to message #472076] Wed, 18 August 2010 12:32 Go to previous messageGo to next message
BlackSwan
Messages: 26604
Registered: January 2009
Location: SoCal
Senior Member
> In the USER_USERS view there is a status column.
REALLY?

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE	11.2.0.1.0	Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL>  desc user_users
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME				   NOT NULL VARCHAR2(30)
 USER_ID				   NOT NULL NUMBER
 ACCOUNT_STATUS 			   NOT NULL VARCHAR2(32)
 LOCK_DATE					    DATE
 EXPIRY_DATE					    DATE
 DEFAULT_TABLESPACE			   NOT NULL VARCHAR2(30)
 TEMPORARY_TABLESPACE			   NOT NULL VARCHAR2(30)
 CREATED				   NOT NULL DATE
 INITIAL_RSRC_CONSUMER_GROUP			    VARCHAR2(30)
 EXTERNAL_NAME					    VARCHAR2(4000)


I suggest you have the application manage application user's security.
Re: Moving Application Security to Oracle from Sybase [message #472079 is a reply to message #472076] Wed, 18 August 2010 12:35 Go to previous messageGo to next message
Michel Cadot
Messages: 66633
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1. Last login date does not exist unless you activate the audit of session
2. You can create an account with an expired password then it cannot connect until it changes its password
3. Expire the password
4. You can create a job that scans the audit trail and locks the account if it did not connect since a number of days.
5. http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_4174.htm#i1628672

Regards
Michel

[Updated on: Wed, 18 August 2010 13:23]

Report message to a moderator

Re: Moving Application Security to Oracle from Sybase [message #472085 is a reply to message #472079] Wed, 18 August 2010 12:53 Go to previous message
drduquette
Messages: 18
Registered: August 2010
Location: Florida
Junior Member
Thanks Michel Smile You have provided me with some good alternatives.

Regards,
Don
Previous Topic: how can i secure my database
Next Topic: Login Attempts Exceed Maximum
Goto Forum:
  


Current Time: Mon Oct 14 05:29:26 CDT 2019