Sunday, January 8, 2012

Create directory implictly grant permission to creator (owner is SYS)

[oracle@vmxdb05b ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 8 20:09:10 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> create user donghua2 identified by donghua2;

User created.

SQL> grant create session to donghua2;

Grant succeeded.

SQL> grant create any directory to donghua2;

Grant succeeded.


SQL> conn donghua2/donghua2
Connected.
SQL> create directory HOMEDIR as '/home/oracle';

Directory created.


SQL> exit


SQL> select * from all_directories;

OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS SUBDIR
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry//2002/Sep

SYS SS_OE_XMLDIR
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry/

SYS HOMEDIR
/home/oracle


OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS LOG_FILE_DIR
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/log/

SYS MEDIA_DIR
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media/

SYS XMLDIR
/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml


OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS DATA_FILE_DIR
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/

SYS DATA_PUMP_DIR
/u01/app/oracle/admin/orcl05b/dpdump/

SYS ORACLE_OCM_CONFIG_DIR
/u01/app/oracle/product/11.2.0/dbhome_1/ccr/state


9 rows selected.


SQL> select * from all_tab_privs where grantee='DONGHUA2';

GRANTOR GRANTEE
------------------------------ ------------------------------
TABLE_SCHEMA TABLE_NAME
------------------------------ ------------------------------
PRIVILEGE GRA HIE
---------------------------------------- --- ---
SYS DONGHUA2
SYS HOMEDIR
WRITE YES NO

SYS DONGHUA2
SYS HOMEDIR
READ YES NO

GRANTOR GRANTEE
------------------------------ ------------------------------
TABLE_SCHEMA TABLE_NAME
------------------------------ ------------------------------
PRIVILEGE GRA HIE
---------------------------------------- --- ---

SYS DONGHUA2
SYS HOMEDIR
EXECUTE YES NO



[oracle@vmxdb05b ~]$ sqlplus donghua2/donghua2

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 8 20:17:44 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options


SQL> set echo on
SQL> @utl_file.sql
SQL> DECLARE
2 V1 VARCHAR2(32767);
3 F1 UTL_FILE.FILE_TYPE;
4 BEGIN
5 F1 := UTL_FILE.FOPEN('HOMEDIR','MYFILE','W');
6 UTL_FILE.PUT_LINE(F1,'TEST LINE');
7 UTL_FILE.FCLOSE(F1);
8
9 F1 := UTL_FILE.FOPEN('HOMEDIR','MYFILE','R');
10 UTL_FILE.GET_LINE(F1,V1,32767);
11 UTL_FILE.FCLOSE(F1);
12 END;
13 /

PL/SQL procedure successfully completed.

SQL> ! cat ~/MYFILE
TEST LINE

SQL> connect donghua/donghua
Connected.
SQL> create directory homedir2 as '/home/oracle';

Directory created.

SQL> select * from all_tab_privs where grantee='DONGHUA';

GRANTOR GRANTEE
------------------------------ ------------------------------
TABLE_SCHEMA TABLE_NAME
------------------------------ ------------------------------
PRIVILEGE GRA HIE
---------------------------------------- --- ---
SYS DONGHUA
SYS HOMEDIR2
WRITE YES NO

SYS DONGHUA
SYS HOMEDIR2
READ YES NO

GRANTOR GRANTEE
------------------------------ ------------------------------
TABLE_SCHEMA TABLE_NAME
------------------------------ ------------------------------
PRIVILEGE GRA HIE
---------------------------------------- --- ---

SYS DONGHUA
SYS HOMEDIR2
EXECUTE YES NO