Home » RDBMS Server » Security » Security
Security [message #109029] Mon, 21 February 2005 09:08 Go to next message
Messages: 4
Registered: February 2005
Junior Member
Our security was not designed properly, in that there are objects that have been given public access and public synonyms created for them. There are also a number of users who were given privileges that they really shouldn't have. The applications give the right restrictions, but if the users go and access the database through sql*plus, they could create havoc if they wanted to.

Is there a way to get around this and restrict database access other than thru the app without having to go and audit each user's roles and without having to remove public grants? Product_user_profile table does not give enough restriction. There are lots of ways around that.
Database is on Oracle 9i.

Re: Security [message #118765 is a reply to message #109029] Sun, 08 May 2005 04:01 Go to previous messageGo to next message
Frank Naude
Messages: 4575
Registered: April 1998
Senior Member
You may be able solve this problem using Secure Application Roles. For more info, see the "Security Overview" Guide:

"One way to address this challenge is through a secure application role: a role implemented by a package. The package can perform any desired validation to ensure that the appropriate conditions are met before the user can exercise privileges granted to the role in the database. The database ensures that it is only the trusted package implementing the role that determines the correct access conditions.

A secure application role is used by an application, can only be enabled by the application, and does not need a password."

Best regards.

Re: Security [message #120613 is a reply to message #109029] Fri, 20 May 2005 15:38 Go to previous message
Michael Hartley
Messages: 110
Registered: December 2004
Location: West Yorkshire, United Ki...
Senior Member


see http://download-west.oracle.com/docs/cd/B10501_01/server.920/a90842/ch10.htm#1005596 which discusses SQLPLUS security in particular restricting user access using the Product_user_profile.

This is not a total salvation because users could connect to the database using AN-other application, but baring SQLplus buys you some time.

While you have the time. Generate roles for each of the user activity type, and grant the existing schema "public" privs to these new roles. Then you have the facility to remove "public" privs and grant the new roles. I would suggest you build a test database from production and explore the new security model carefully and completely before applying it to production.

Kind regards,
Michael Hartley, http://www.openfieldsolutions.co.uk
Previous Topic: Restrict Oracle access by User and IP Address
Next Topic: Question about granting tables
Goto Forum:

Current Time: Mon Sep 25 18:53:08 CDT 2023