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 ------------------------------------------------------------------------ Rem NAME: db_logon_trigger.sql BY: lvordos Rem Rem DESCRIPTION: Creates table & trigger to set the current_schema at Rem logon time as defined for users in system.default_schema. This Rem script must be run as a DBA user. Rem Rem Concept cloned from http://www.ixora.com.au (Steve Adams) with Rem modifications. 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 BEGIN SELECT schema_name into default_schema FROM system.default_schema where user_name = user; EXCEPTION WHEN NO_DATA_FOUND THEN nodata := TRUE; END; IF nodata = FALSE THEN execute immediate 'alter session set current_schema = ' ||default_schema ; END IF ; END ; / spool off [EMAIL PROTECTED] 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" <[EMAIL PROTECTED]> > 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
