First connect to the 23c database:
C:\>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 26 11:47:56 2023
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
SQL>
Now, lets see what system privileges exist for DIRECTORIES.
SQL> select * from system_privilege_map where name like '%DIR%';
PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-177 CREATE ANY DIRECTORY 0
-178 DROP ANY DIRECTORY 0
SQL>
There is no specific CREATE DIRECTORY that a schema owner can use to create a directory in their own schema. Therefore a user must have CREATE ANY DIRECTORY to create a DIRECTORY object.
Which users have CREATE ANY DIRECTORY in my 23c database?
who_has_priv: Release 1.0.3.0.0 - Production on Fri May 26 11:12:38 2023
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.
PRIVILEGE TO CHECK [SELECT ANY TABLE]: CREATE ANY DIRECTORY
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
EXCLUDE CERTAIN USERS [N]:
USER TO SKIP [TEST%]:
Privilege => CREATE ANY DIRECTORY has been granted to =>
====================================================================
User => HRREST (ADM = NO)
User => SYS (ADM = NO)
Role => DBA (ADM = NO) which is granted to =>
User => AV (ADM = NO)
User => SYSTEM (ADM = NO)
User => SYS (ADM = YES)
User => SYSBACKUP (ADM = NO)
Role => IMP_FULL_DATABASE (ADM = NO) which is granted to =>
Role => DATAPUMP_IMP_FULL_DATABASE (ADM = NO) which is granted to =>
Role => DBA (ADM = NO) which is granted to =>
User => AV (ADM = NO)
User => SYSTEM (ADM = NO)
User => SYS (ADM = YES)
User => SYS (ADM = YES)
User => GSMADMIN_INTERNAL (ADM = NO)
User => SYS (ADM = YES)
Role => DBA (ADM = NO) which is granted to =>
User => AV (ADM = NO)
User => SYSTEM (ADM = NO)
User => SYS (ADM = YES)
Role => OSAK_ADMIN_ROLE (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
PL/SQL procedure successfully completed.
For updates please visit http://www.petefinnigan.com/tools.htm
SQL>
SYS has the grant 7 times; once direct and 6 via various roles; this is typical in Oracle databases for grants to be made many times. First lets create a sample DIRECTORY as SYS:
SQL> sho user
USER is "SYS"
SQL> create directory test as '/test';
Directory created.
SQL>
So an interesting point, the folder "/test" does not exist on the server BUT the DIRECTORY object was created so if we used it it would fail. What about the owner and grants:
SQL> select * from dba_directories where directory_name='TEST';
OWNER
--------------------------------------------------------------------------------
DIRECTORY_NAME
--------------------------------------------------------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
ORIGIN_CON_ID
-------------
SYS
TEST
/test
3
1 row selected.
SQL>
SQL> @get_tab2
get_tab2: Release 1.2.0.0.0 - Production on Fri May 26 11:19:43 2023
Copyright (c) 2007, 2017, PeteFinnigan.com Limited. All rights reserved.
OBJECT TO CHECK [XXX_XXXX]: TEST
SCHEMA/OWNER OF THE OBJECT TO CHECK [USER]: SYS
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
Testing root object => [SYS.TEST]
GRANTOR GRANTEE R S I U D A F D I R Q C E
------------- -------------- - - - - - - - - - - - - -
PL/SQL procedure successfully completed.
For updates please visit http://www.petefinnigan.com/tools.htm
SQL>
So the DIRECTORY was created and it is owned by SYS and there are no grants by default for this DIRECTORY. Obviously we can then make grants on the DIRECTORY for it to be used. What if we want to create a directory in another schema from SYS. The privilege does have the keyword ANY so it would imply we can create a DIRECTORY in another schema. If we have CREATE ANY TABLE for instance we can create a table in a different schema. For instance SYS has this and we can create a table in my VA schema:
SQL> sho user
USER is "SYS"
SQL> create table va.test_table (col01 number);
Table created.
SQL>
So, we should be able to create a DIRECTORY in my VA schema:
SQL> create directory va.test2 as '/test';
create directory va.test2 as '/test'
*
ERROR at line 1:
ORA-02000: missing AS keyword
SQL>
So we cannot; this means that this %ANY% permission works differently to other %ANY% permissions such as CREATE ANY TABLE. hmmm. So can we grant CREATE ANY DIRECTORY to VA and create a directory as VA:
C:\>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 26 12:47:37 2023
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
SQL> grant create any directory to va;
Grant succeeded.
SQL> connect va/va@//192.168.56.18:1521/freepdb1
Connected.
SQL> create directory test2 as '/test';
Directory created.
SQL>
Lets have a look at the ownership of TEST2:
SQL> col owner for a20
SQL> col owner for a20
SQL> col directory_name for a30
SQL> col directory_path for a100
SQL> set lines 220
SQL> l
1* select owner,directory_name,directory_path from dba_directories
SQL> /
OWNER DIRECTORY_NAME DIRECTORY_PATH
-------------------- ------------------------------ ----------------------------------------------------------------------------------------------------
SYS SDO_DIR_ADMIN /opt/oracle/product/23c/dbhomeFree/md/admin
SYS XMLDIR /opt/oracle/product/23c/dbhomeFree/rdbms/xml
SYS XSDDIR /opt/oracle/product/23c/dbhomeFree/rdbms/xml/schema
SYS ORACLE_BASE /opt/oracle
SYS ORACLE_HOME /opt/oracle/product/23c/dbhomeFree
SYS OPATCH_INST_DIR /opt/oracle/product/23c/dbhomeFree/OPatch
SYS DATA_PUMP_DIR /opt/oracle/admin/FREE/dpdump/F87259FB7D3C3519E0530100007F5D4C
SYS DBMS_OPTIM_LOGDIR /opt/oracle/product/23c/dbhomeFree/cfgtoollogs
SYS DBMS_OPTIM_ADMINDIR /opt/oracle/product/23c/dbhomeFree/rdbms/admin
SYS OPATCH_SCRIPT_DIR /opt/oracle/product/23c/dbhomeFree/QOpatch
SYS OPATCH_LOG_DIR /opt/oracle/product/23c/dbhomeFree/rdbms/log
OWNER DIRECTORY_NAME DIRECTORY_PATH
-------------------- ------------------------------ ----------------------------------------------------------------------------------------------------
SYS JAVA$JOX$CUJS$DIRECTORY$ /opt/oracle/product/23c/dbhomeFree/javavm/admin/
SYS TEST2 /test
SYS TEST /test
SYS LOG_FILE_DIR /tmp/
SYS DATA_FILE_DIR /opt/oracle/userhome/oracle/unzipdemos/db-sample-schemas-master/sales_history/
SYS MEDIA_DIR /opt/oracle/userhome/oracle/unzipdemos/db-sample-schemas-master/product_media/
SYS SUBDIR /opt/oracle/userhome/oracle/unzipdemos/db-sample-schemas-master/order_entry//2002/Sep
SYS SS_OE_XMLDIR /opt/oracle/userhome/oracle/unzipdemos/db-sample-schemas-master/order_entry/
19 rows selected.
SQL>
So, our TEST2 DIRECTORY is owned by SYS even though VA created the directory. What about the grants:
SQL> col grantee for a20
SQL> col owner for a20
SQL> col table_name for a20
SQL> col grantor for a20
SQL> col grantable for a3
SQL> set lines 220
SQL> l
1* select grantee, owner, table_name, grantor, privilege, grantable from dba_tab_privs where owner='SYS' and table_name='TEST2'
SQL> /
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA
-------------------- -------------------- -------------------- -------------------- ---------------------------------------- ---
VA SYS TEST2 SYS EXECUTE YES
VA SYS TEST2 SYS READ YES
VA SYS TEST2 SYS WRITE YES
3 rows selected.
SQL>
hmmm, VA has been granted READ, WRITE and EXECTUTE on SYS.TEST2 by SYS. So VA had CREATE ANY DIRECTORY but this works differently to other privileges. If we look at CREATE ANY TABLE and the table we created in the VA schema; TEST_TABLE earlier this is owned by VA and has no grants:
SQL> select owner from dba_tables where table_name='TEST_TABLE';
OWNER
--------------------
VA
1 row selected.
SQL> @get_tab2
get_tab2: Release 1.2.0.0.0 - Production on Fri May 26 12:04:57 2023
Copyright (c) 2007, 2017, PeteFinnigan.com Limited. All rights reserved.
OBJECT TO CHECK [XXX_XXXX]: TEST_TABLE
SCHEMA/OWNER OF THE OBJECT TO CHECK [USER]: VA
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
Testing root object => [VA.TEST_TABLE]
GRANTOR GRANTEE R S I U D A F D I R Q C E
------------- -------------- - - - - - - - - - - - - -
PL/SQL procedure successfully completed.
For updates please visit http://www.petefinnigan.com/tools.htm
SQL>
This CREATE ANY TABLE is different behaviour to CREATE ANY DIRECTORY. If we create a DIRECTORY as SYS then its owned by SYS and no grants are made. We can of course make grants. If we grant CREATE ANY DIRECTORY to another user and then as that user create the DIRECTORY then its still owned by SYS and there are grants WITH GRANT for READ, WRITE and EXECUTE granted to the executor and grantee of CREATE ANY DIRECTORY but the grants were made by SYS and not the grantee of CREATE ANY DIRECTORY (VA in this example).
This is confusing and as I stated this knowledge is useful if we are forensically analysing a database to understand what happened. To analyse Oracle to understand what happened we must understand what happens in Oracle when we create things or use commands
The other point of note is that CREATE DIRECTORY .... does nor check if the directory on the OS exists or not.
#23c #dbsec #oracle #forensics
