Oracle-L: Re: User with less privileges...

From: Don Granaman <granaman_at_cox.net>
Date: Thu, 01 Aug 2002 22:08:29 -0800
Message-ID: <F001.004A99BA.20020801220829@fatcity.com>

I agree on the use of roles - it is the best way to go. However, beware that object privileges granted via a role are NOT in effect inside a definer's rights procedure/package (the default type). This *may* require some investigation and, perhaps, some changes to the application, the privileges of the package owner, the owner of the package, or the package authid, or ...

I disagree about granting CONNECT to everyone - grant "create session" instead. CONNECT is actually a pre-defined role with a number of system privileges that most application users do NOT need (alter session, create table, create cluster, create database link, etc.) in addition to the "create session" system privilege.

Likewise, I would grant explicit tablespace quotas. Granting RESOURCE is again overkill. Most application users don't need tablespace quotas and even if they do it is usually something trivial (e.g. 1-10 MB) in USERS. The system privilege "unlimited tablespace" (included in the RESOURCE role) is especially dangerous as it includes the SYSTEM tablespace.

The "easy way" out is to just grant *everything* to PUBLIC, but it is a very poor choice from any rational security perspective - as you are now discovering. (Oracle preaches this, but doesn't actually practice it themselves!)

You will need to do as Bill suggested:

Create a set of application-specific functional roles (e.g. CUST_SVC_REP, CUST_SVC_SUPR, CUST_SVC_ADMIN, ...).
Grant privileges to roles as appropriate
Grant roles to users as appropriate
Revoke all (most?) of the application object privileges (and perhaps some others) from PUBLIC

The public synonyms are another issue. The don't carry any intrinsic privilege - SELECT, INSERT, etc. still have to be granted to the user or to a role granted to the user. However, public synonyms can be a performance issue and *may* be undesirable for other reasons.

Don Granaman
[OraSaurus]

Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, August 01, 2002 11:28 AM

try this:

rather than granting specific privs to PUBLIC, create specific roles for the different types of users you have, and grant appropriate object privs to each role (granting connect also helps :-). then for each user you add, just give that user whatever role is relevent and you're set . . . they will still be able to access public synonyms. only issue with this is that you'll still need to specify TS quotas to the specific users, as they don't inherit these from the roles (unless you grant RESOURCE to the role, which has UNLIMITED TABLESPACE).

using roles is easy to maintain, document and manage

-bill

-----Original Message-----

Sent: Thursday, August 01, 2002 11:18 AM To: Multiple recipients of list ORACLE-L

Hi guys.

Can you give some ideeas about this problem.

I have a schema which contains all the objects for the application. The user owner of the schema is also the application administrator and having more privilleges. The other users can have access to these objects by beeing granted with some special privilleges (like select/update/insert/delete for tables, execute for functions&procedures)

Because the user are deleted or added from time to time, the application author decided to grant the above kind of privilleges to the public and also create some public synonyms with the same names as the originals.

BUT, my problem is that now I need to create an user (he does not have any relations with the ordinary application users) which I don't want to have any access to the hrowner objects, or just on few.

Is this doable working only on this new user or I have to re-create all those synonyms and grant privilleges to every application user and revoke'em from public?

Thank in advance!

iulian

**
The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Orange Romania SA is neither liable for the proper, complete transmission of the information contained in this communication nor any delay in its receipt.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Iulian.ILIES_at_orange.ro

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Magaliff, Bill
INET: Bill.Magaliff_at_lendware.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Don Granaman
INET: granaman_at_cox.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message