Re: Privileges on directory object are failing

From: <alan.aschenbrenner_at_ihs.com>
Date: Thu, 14 Jul 2005 15:24:29 -0600
Message-ID: <OFE052BCAD.BE92AC38-ON8725703E.00756E08@ihs.com>

Gabriel,

    It looks like you are missing the keyword "directory" on the commands:

You have:

GRANT WRITE ON "SYS"."DIR_NB" TO "AC"; It should be:

GRANT WRITE ON DIRECTORY "SYS"."DIR_NB" TO "AC"; Alan Aschenbrenner
Senior Database Administrator
Information Handling Services Inc.
alan.aschenbrenner_at_ihs.com
(303)-858-6394

---

Confidentiality Notice: The information in this e-mail may be confidential and / or privileged. This e-mail is intended to be reviewed by only the individual or organization named in the e-mail address. If you are not the intended recipient, you are hereby notified that any review, dissemination or copying of this e-mail and attachments, if any, or the information contained herein, is strictly prohibited.

                                                                                                                                        
                      "Aragon, Gabriel                                                                                                  
                      (GE Commercial            To:       <oracle-l_at_freelists.org>                                                      
                      Finance)"                 cc:                                                                                     
                      <gabriel.aragon_at_ge        Subject:  Privileges on directory object are failing                                    
                      .com>                                                                                                             
                      Sent by:                                                                                                          
                      oracle-l-bounce_at_fr                                                                                                
                      eelists.org                                                                                                       
                                                                                                                                        
                                                                                                                                        
                      07/14/2005 03:07                                                                                                  
                      PM                                                                                                                
                      Please respond to                                                                                                 
                      gabriel.aragon                                                                                                    
                                                                                                                                        
                                                                                                                                        

Hi list, oracle 9.2.0.6.0, SunOS 5.8 sun4u sparc SUNW,Sun-Fire

this is making me crazy, I have 2 directories created:

CREATE OR REPLACE DIRECTORY "DIR_NB" AS

'-------------------NB';

CREATE OR REPLACE DIRECTORY "DIR_ND" AS

'-------------------ND';

I have 2 users:

CREATE USER "AC" PROFILE "DEFAULT"
    IDENTIFIED BY "AC" DEFAULT TABLESPACE "---"     TEMPORARY TABLESPACE "TEMP"
    ACCOUNT UNLOCK; GRANT CREATE ANY TABLE TO "AC";
GRANT CREATE SNAPSHOT TO "AC";
GRANT UNLIMITED TABLESPACE TO "AC";
GRANT EXECUTE ON "SYS"."DBMS_SYSTEM" TO "AC"; GRANT "CONNECT" TO "AC";
GRANT "RESOURCE" TO "AC";

---

CREATE USER "NV" PROFILE "DEFAULT"
    IDENTIFIED BY "NV" DEFAULT TABLESPACE "---"     TEMPORARY TABLESPACE "TEMP"
    ACCOUNT UNLOCK; GRANT CREATE ANY TABLE TO "NV";
GRANT UNLIMITED TABLESPACE TO "NV";
GRANT EXECUTE ON "SYS"."DBMS_SYSTEM" TO "NV"; GRANT "CONNECT" TO "NV";
GRANT "RESOURCE" TO "NV";
and privs for dirs are:

drwxrwxrwx 96 Jul 14 15:24 ND
drwxrwxrwx 96 Jun 23 13:49 NB

but when I try to assing privs:

SQL> GRANT READ ON DIRECTORY "SYS"."DIR_NB" TO "AC"; Grant succeeded.

SQL> GRANT READ ON DIRECTORY "SYS"."DIR_ND" TO "AC"; Grant succeeded.

SQL> GRANT WRITE ON "SYS"."DIR_NB" TO "AC"; GRANT WRITE ON "SYS"."DIR_NB" TO "AC"
*

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> GRANT WRITE ON "SYS"."DIR_ND" TO "AC"; GRANT WRITE ON "SYS"."DIR_ND" TO "AC"
*

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> GRANT WRITE ON DIRECTORY "DIR_NB" TO "NV"; Grant succeeded.

SQL> GRANT READ ON DIRECTORY "DIR_NB" TO "NV" ; Grant succeeded.

SQL> GRANT WRITE ON DIRECTORY "DIR_ND" TO "NV"; Grant succeeded.

SQL> GRANT READ ON DIRECTORY "DIR_ND" TO "NV" ; Grant succeeded.

Write privilege on directories works for user NV but fails for user AC, what am I missing here???? No luck on metalink or google.

TIA
Gabriel

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jul 14 2005 - 16:21:04 CDT