Oracle Directory Object
Oracle offers two ways for a session to read or write operating system files.
1.Oracle directories
2. UTL_FILE package
UTL_FILE package contains many PL/SQL procedures for manipulating directory objects. Operating system directories that can be accessed by UTL_FILE are only those listed in UTL_FILE_DIR instance parameter. This parameter default to NULL but can be set to any comma separated list as well as * (wildcard) which means any directory in operating system.
SQL > SELECT NAME, VALUE
FROM V$PARAMETER
WHERE UPPER(NAME) = 'UTL_FILE_DIR'
Result
---------------------------------------------------------------------------------------------------
utl_file_dir NULL
All the directories listed will be visible to all sessions. To give a value for UTL_FILE_DIR parameter file use the following command.
SQL > sqlplus / as sysdba
SQL > ALTER SYSTEM SET UTL_FILE_DIR = 'C:\TEST' SCOPE = SPFILE
SQL > SELECT NAME, VALUE
FROM V$PARAMETER
WHERE UPPER(NAME) = 'UTL_FILE_DIR'
Result
---------------------------------------------------------------------------------------------------
utl_file_dir C:\TEST
Notice the use of SCOPE = SPFILE: it is a static parameter and change can not take effect without restarting the instance.
Oracle directories give a layer of abstraction between the user and the operating system: DBA create a directory within the database, which points to a physical path. Permissions on these Oracle directories can then be granted to individual users. This gives much more finely grained access control than the UTL_FILE_DIR parameter. Oracle does not check if the directory exists or not. Having created the directory read and write permission should be granted to ORACLE users. Directories are always own by SYS user despite who created them.
SQL > GRANT CREATE ANY DIRECTORY TO HR
SQL > CREATE DIRECTORY TEST AS 'C:\Test'
SQL > GRANT READ ON DIRECTORY TEST TO PUBLIC
SQL > GRANT WRITE ON DIRECTORY TEST TO SCOTT
1.Oracle directories
2. UTL_FILE package
UTL_FILE package contains many PL/SQL procedures for manipulating directory objects. Operating system directories that can be accessed by UTL_FILE are only those listed in UTL_FILE_DIR instance parameter. This parameter default to NULL but can be set to any comma separated list as well as * (wildcard) which means any directory in operating system.
SQL > SELECT NAME, VALUE
FROM V$PARAMETER
WHERE UPPER(NAME) = 'UTL_FILE_DIR'
Result
---------------------------------------------------------------------------------------------------
utl_file_dir NULL
All the directories listed will be visible to all sessions. To give a value for UTL_FILE_DIR parameter file use the following command.
SQL > sqlplus / as sysdba
SQL > ALTER SYSTEM SET UTL_FILE_DIR = 'C:\TEST' SCOPE = SPFILE
SQL > SELECT NAME, VALUE
FROM V$PARAMETER
WHERE UPPER(NAME) = 'UTL_FILE_DIR'
Result
---------------------------------------------------------------------------------------------------
utl_file_dir C:\TEST
Notice the use of SCOPE = SPFILE: it is a static parameter and change can not take effect without restarting the instance.
Oracle directories give a layer of abstraction between the user and the operating system: DBA create a directory within the database, which points to a physical path. Permissions on these Oracle directories can then be granted to individual users. This gives much more finely grained access control than the UTL_FILE_DIR parameter. Oracle does not check if the directory exists or not. Having created the directory read and write permission should be granted to ORACLE users. Directories are always own by SYS user despite who created them.
SQL > GRANT CREATE ANY DIRECTORY TO HR
SQL > CREATE DIRECTORY TEST AS 'C:\Test'
SQL > GRANT READ ON DIRECTORY TEST TO PUBLIC
SQL > GRANT WRITE ON DIRECTORY TEST TO SCOTT
Comments
Post a Comment