| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: User with less privileges...
Or, you can use a logon trigger to alter the user's schema to the application schema they need access to. Here's some code for that. For each user, insert the user_name and schema_name they need access to.
Rem
set echo on verify off feedback on
whenever sqlerror exit sql.sqlcode ;
spool /tmp/db_logon_trigger.txt
CREATE TABLE system.default_schema (
user_name varchar2(30),
schema_name varchar2(30)
)
TABLESPACE tools ;
ALTER TABLE system.default_schema
ADD CONSTRAINT pk_default_schema
PRIMARY KEY (user_name)
USING INDEX TABLESPACE tools ;
CREATE OR REPLACE trigger system.set_current_schema
AFTER LOGON on database
DECLARE
default_schema varchar2(30); nodata boolean := FALSE;BEGIN
EXCEPTION
WHEN NO_DATA_FOUND THEN
nodata := TRUE;
execute immediate 'alter session set current_schema = ' ||default_schema ;
END IF ;
END ;
/
spool off
Iulian.ILIES_at_orange.ro wrote:
>
> 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]
>
> ----- 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
> 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).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suzy Vordos INET: lvordos_at_qwest.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).Received on Fri Aug 02 2002 - 11:26:08 CDT
 |
 |