| Security Advisory |
Applies a supported cryptographic hash function to a SQL statement. This function can not be used to hash a string that is not a valid SQL statement.
There is no valid reason for any user to have or request EXECUTE on this package. If a request is made treat it with great suspicion and do not grant the object privilege without a deep investigation as to why and workarounds.
And, if you do, revalidate the need no less frequently than once each year. |
| |
| Recommended Security Rules |
NEVER
- Grant execute on this package to any user or role
WITH GREAT CARE
- Check regularly to determine if execute on this package has been granted and if so treat it as highly suspicious: It should raise alarms.
CAUTIONS
|
| |
| How Oracle Works |
| Oracle Documentation says this package function can only be used to hash a valid SQL statement |
-- And the verdict, we are happy to say, is:
DECLARE
sqlstr VARCHAR2(4000):='This function can not be used to hash a string that is not a valid SQL statement.';
digtype PLS_INTEGER := 3;
RetVal RAW(2000);
BEGIN
SELECT dbms_sqlhash.gethash(sqlstr, dbms_crypto.hash_sh1)
INTO RetVal
FROM dual;
dbms_output.put_line(RetVal);
END;
/
DECLARE
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at "SYS.DBMS_SQLHASH", line 94
ORA-06512: at "SYS.DBMS_SQL", line 1134
ORA-06512: at "SYS.DBMS_SQLHASH", line 26
ORA-06512: at line 1
ORA-06512: at line 6
-- but we like to push a little harder
DECLARE
sqlstr VARCHAR2(4000):='SELECT COUNT(*) FROM (SELECT ''DBA_TABLES'' FROM dual)';
digtype PLS_INTEGER := 3;
RetVal RAW(2000);
BEGIN
dbms_output.put_line(sqlstr);
SELECT dbms_sqlhash.gethash(sqlstr, dbms_crypto.hash_sh1)
INTO RetVal
FROM dual;
dbms_output.put_line(RetVal);
END;
/
SELECT COUNT(*) FROM (SELECT 'DBA_TABLES' FROM dual)
D9195BEDB8CCAC0F0D4FAAC8D386491E7FE2AD0E
PL/SQL procedure successfully completed.
DECLARE
sqlstr VARCHAR2(4000):='WITH
q AS (SELECT dummy FROM dual) SELECT dummy FROM q';
digtype PLS_INTEGER := 3;
RetVal RAW(2000);
BEGIN
dbms_output.put_line(sqlstr);
SELECT dbms_sqlhash.gethash(sqlstr, dbms_crypto.hash_sh1)
INTO RetVal
FROM dual;
dbms_output.put_line(RetVal);
END;
/
WITH q AS (SELECT dummy FROM dual) SELECT dummy FROM q
0B1DEF79965ED3A49321922201F414D1117D6753
PL/SQL procedure successfully completed.
-- and it seems likely to be a true statement within the realm of real-world SQL |
| |
| DBMS_SQLHASH Package Information |
| AUTHID |
CURRENT_USER |
| Constants (uses DBMS_CRYPTO constants also listed here) |
| Name |
Data Type |
Value |
| General |
| HASH_MD4 (128 bit hash) |
BINARY_INTEGER |
1 |
| HASH_MD5 (128 bit hash) |
BINARY_INTEGER |
2 |
| HASH_SH1 (160 bit hash) |
BINARY_INTEGER |
3 |
| HASH_SH256 |
BINARY_INTEGER |
4 |
| HASH_SH384 |
BINARY_INTEGER |
5 |
| HASH_SH512 |
BINARY_INTEGER |
6 |
|
| Dependencies |
| DBMS_CRYPTO |
DBMS_SQL |
UTL_RAW |
| DBMS_LOB |
|
|
|
| Documented in Types & Packages |
No |
| First Available |
12.1 |
| Pragma |
PRAGMA SUPPLEMENTAL_LOG_DATA(default, NONE); |
| Security Model |
Owned by SYS with no privileges granted |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmsobtk.sql |
| |
| GETHASH |
| Interface to generate the hash value of the result set returned by a SQL query |
dbms_sqlhash.gethash(
sqltext IN VARCHAR2, -- input sql statement
digest_type IN BINARY_INTEGER, -- digest algorithm type
chunk_size IN NUMBER DEFAULT 134217728) -- 128M
RETURN RAW; |
set serveroutput on
DECLARE
sqlstr VARCHAR2(4000):='SELECT object_name FROM all_objects WHERE rownum = 1';
digtype PLS_INTEGER := 3;
RetVal RAW(2000);
BEGIN
SELECT dbms_sqlhash.gethash(sqlstr, dbms_crypto.hash_sh1)
INTO RetVal
FROM dual;
dbms_output.put_line(RetVal);
END;
/
3806F18B5BCF32570525EF9A234AA1D07A78084E
PL/SQL procedure successfully completed. |