Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: User with less privileges...

RE: User with less privileges...

From: <Iulian.ILIES_at_orange.ro>
Date: Thu, 01 Aug 2002 23:53:19 -0800
Message-ID: <F001.004A9A0B.20020801235319@fatcity.com> 





Thanks guys. 


OK, I can control the access to public synonyms but, I also don't want them
to be seen by that new user (the intruder). So, I guess in order to avoid
public synonyms, I still have to create all the synonyms for every
aplication's users. 


It seems that I'm a lazy person.



Regards


iulian ilies




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


Sent: Friday, August 02, 2002 9:08 AM


To: Multiple recipients of list ORACLE-L






This email has been tested for viruses by F-Secure Antivirus
administered by IT Network Department.





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:



    
  
  1.   Create a set of application-specific functional roles (e.g. CUST_SVC_REP,
CUST_SVC_SUPR, CUST_SVC_ADMIN, ...).

  
  2.   Grant privileges to roles as appropriate
  
  3.   Grant roles to users as appropriate
  
  4.   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]






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
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: 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
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: 
  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).


Received on Fri Aug 02 2002 - 02:53:19 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US