New READ Object Privilege in 12cR1

In writing a blog post about:
Creating a Read Only Database User Account in an Oracle Database

It came to my attention of the new “READ” object privilege, which is a New Feature in 12.1.0.2:
Changes in Oracle Database 12c Release 1 (12.1.0.2)
READ and SELECT Object Privileges

The “SELECT” object privilege in addition to querying the table, allows the user to:
LOCK TABLE table_name IN EXCLUSIVE MODE;
SELECTFROM table_name FOR UPDATE;

The New Feature of “READ” object privilege, does not allow the user to lock tables in exclusive mode nor select table for update.

Prior to 12.1.0.2, the “SELECT” object privilege is only available which allows the locking:

GRANT SELECT ON ... TO ...;

12.1.0.2 onwards, the new “READ” object privilege is available which doesn’t allow the locking:

GRANT READ ON ... TO ...;

This also applies to the “SELECT ON ANY TABLE“, prior to 12.1.0.2 which allows the locking:

GRANT SELECT ANY TABLE TO ...;

12.1.0.2 onwards, the new “READ ON ANY TABLE” object privilege is available which doesn’t allow the locking:

GRANT READ ANY TABLE TO ...;

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Creating a Read Only Database User Account in an Oracle Database

It can be quite common to create a “Read Only” database user account in an Oracle database.  To do this is pretty simple using the principle of least privilege:

CREATE USER READ_ONLY IDENTIFIED BY "password";
GRANT CREATE SESSION TO READ_ONLY;

Expected output:

SQL> CREATE USER READ_ONLY IDENTIFIED BY "password";

User created.

SQL> GRANT CREATE SESSION TO READ_ONLY;

Grant succeeded.

SQL>

Depending on the Oracle Version, it’s recommend to use “READ” object privilege new in 12.1.0.2 instead of “SELECT” to prevent locking.  See the following blog post for more info:
New READ Object Privilege in 12cR1

PLEASE NOTE: For anyone using Oracle Version prior to 12.1.0.2, replace the object privilege “READ” with “SELECT“.

To read specific tables for a schema:

SET HEADING OFF
SET PAGES 9999 LINES 400
SPOOL TABLE_GRANTS.sql
SELECT 'GRANT READ ON ' || OWNER || '.' || TABLE_NAME || ' TO READ_ONLY;'
FROM DBA_TABLES
WHERE OWNER IN ('ZEDDBA')
ORDER BY 1;
SPOOL OFF
@TABLE_GRANTS.sql
!rm TABLE_GRANTS.sql

Expected output, where you can see the user “READ_ONLY” able to select from the table owned by user “ZEDDBA“:

SQL> SET HEADING OFF
SQL> SET PAGES 9999 LINES 400
SQL> SPOOL TABLE_GRANTS.sql
SQL> SELECT 'GRANT READ ON ' || OWNER || '.' || TABLE_NAME || ' TO READ_ONLY;'
 2 FROM DBA_TABLES
 3 WHERE OWNER IN ('ZEDDBA')
 4 ORDER BY 1;

GRANT READ ON ZEDDBA.DEMO TO READ_ONLY;

SQL> SPOOL OFF
SQL> @TABLE_GRANTS.sql
SP2-0734: unknown command beginning "SQL> SELEC..." - rest of line ignored.

Grant succeeded.

SP2-0734: unknown command beginning "SQL> SPOOL..." - rest of line ignored.
SQL> !rm TABLE_GRANTS.sql

SQL> conn read_only/password
Connected.
SQL> SELECT * FROM ZEDDBA.DEMO;

Read Only example.

SQL> exit

To read all tables in the databases:

GRANT READ ANY TABLE TO READ_ONLY;

Expected output, where you can see the user “READ_ONLY” able to select from the table owned by user “ZEDDBA“, even after the revoke and the user “ZEDDBA2“, which hasn’t been explicitly referenced:

SQL> conn zeddba/zeddba
Connected.
SQL> REVOKE READ ON ZEDDBA.DEMO FROM READ_ONLY;

Revoke succeeded.

SQL> GRANT READ ANY TABLE TO READ_ONLY;

Grant succeeded.

SQL> conn read_only/password
Connected.
SQL> SELECT * FROM ZEDDBA.DEMO;

COMMENTS
--------------------------------------------------------------------------------
Read Only example.

SQL> SELECT * FROM ZEDDBA2.DEMO;

COMMENTS
--------------------------------------------------------------------------------
Read Only example.

SQL>

To grant access to dictionary tables:

GRANT SELECT ANY DICTIONARY TO READ_ONLY;

Expected output, where you can see the user “READ_ONLY” able to select from dictionary i.e. table starting with DBA and V$:

SQL> conn zeddba/zeddba
Connected.
SQL> GRANT SELECT ANY DICTIONARY TO READ_ONLY;

Grant succeeded.

SQL> conn read_only/password
Connected.
SQL> SELECT TABLE_NAME
 2 FROM DBA_TABLES
 3 WHERE OWNER = 'ZEDDBA';

TABLE_NAME
--------------------------------------------------------------------------------
DEMO

SQL> SELECT NAME FROM V$DATABASE;

NAME
---------
ZEDDBA

SQL>

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

How To Enable DDL Logging in the Database

If for whatever reason, you are required to log DDL, for example, I need to know why the LAST_DDL_TIME of a table was getting updated, you can do this from Oracle 11g.

To enable:

SQL> show parameter ENABLE_DDL_LOGGING

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------ 
enable_ddl_logging boolean FALSE

SQL> ALTER SYSTEM SET ENABLE_DDL_LOGGING=TRUE;

System altered.

SQL> show parameter ENABLE_DDL_LOGGING

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------ 
enable_ddl_logging boolean TRUE

To disable:

SQL> show parameter ENABLE_DDL_LOGGING

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------ 
enable_ddl_logging boolean TRUE

SQL> ALTER SYSTEM SET ENABLE_DDL_LOGGING=FASLE;

System altered. 

SQL> show parameter ENABLE_DDL_LOGGING 

NAME TYPE VALUE 
------------------------------------ ----------- ------------------------------ 
enable_ddl_logging boolean FALSE 

Create some DDL:

SQL> create view zeddba as select * from dual;

View created.

SQL> select * from zeddba;





SQL> drop view zeddba;

View dropped.

Oracle 12c

Now if you look in the following text file:
$ADR_BASE/diag/rdbms/${DBNAME}/${ORACLE_SID}/log/ddl_${ORACLE_SID}.log

You will see:

Mon Sep 11 15:52:59 2017
diag_adl:create view zahid as select * from dual
diag_adl:drop view zahid

There is also a XML version:
$ADR_BASE/diag/rdbms/${DBNAME}/${ORACLE_SID}/log/ddl/log.xml

<msg time='2017-09-11T15:41:35.000+01:00' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:4424:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='v1ex1dbadm01.v1.com' host_addr='x.x.x.x'
 version='1'>
 <txt>create view zeddba as select * from dual
 </txt>
</msg>
<msg time='2017-09-11T15:41:45.942+01:00' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:4424:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='v1ex1dbadm01.v1.com' host_addr='x.x.x.x'>
 <txt>drop view zeddba
 </txt>
</msg>

Oracle 11g

DDL statements are written to the alert log in: $ADR_BASE/diag/rdbms/${DBNAME}/${ORACLE_SID}/trace/alert_${ORACLE_SID}.log

License

Oracle Database Lifecycle Management Pack for Oracle Database

Licensed Parameters

The init.ora parameter ENABLE_DDL_LOGGING is licensed as part of the Database Lifecycle Management Pack when set to TRUE.  When set to TRUE, the database reports schema changes in real time into the database alert log under the message group schema_ddl. The default setting is FALSE.”

More info

Database Reference: ENABLE_DDL_LOGGING

See MOS Note:
How To Enable DDL Logging in Database (Doc ID 2207341.1)

“When ENABLE_DDL_LOGGING is set to true, the following DDL statements are written to the alert log:

ALTER/CREATE/DROP/TRUNCATE CLUSTER
ALTER/CREATE/DROP FUNCTION
ALTER/CREATE/DROP INDEX
ALTER/CREATE/DROP OUTLINE
ALTER/CREATE/DROP PACKAGE
ALTER/CREATE/DROP PACKAGE BODY
ALTER/CREATE/DROP PROCEDURE
ALTER/CREATE/DROP PROFILE
ALTER/CREATE/DROP SEQUENCE
CREATE/DROP SYNONYM
ALTER/CREATE/DROP/RENAME/TRUNCATE TABLE
ALTER/CREATE/DROP TRIGGER
ALTER/CREATE/DROP TYPE
ALTER/CREATE/DROP TYPE BODY
DROP USER
ALTER/CREATE/DROP VIEW

Earlier, RENAME was not logged and a bug was reported for that and the same is fixed in 11.2.0.4.
Document 12938609.8 – ENABLE_DDL_LOGGING does not log RENAME table statements, this is fixed in 11.2.0.4

However, the feature does not log DDLs of some DBMS_STATS operations like:
set_column_stats
set_index_stats
create_extended_stats
drop_extended_stats
set_*_prefs (table/schema/global etc)
delete_pending_stats
publish_pending_stats
export_pending_stats
create_stat_table 

There is an enhancement raised with development to add more operations to this mechanism and would get fixed in 12.2.

Unpublished Bug 22368778 : PERF_DIAG: ENABLE_DDL_LOGGING NEEDS TO LOG MORE DDLS”

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

How to obtain the Historical Database Total Used and Allocated Size from OEM Repository

From time to time, it’s useful to know the total allocated size of a database at OS level, how much of it has been used and what the maximum total size the database can grow to at OS level, see blog post:
How to obtain the Database Total Used, Allocated and Max Size

However, it’s also good to know the historical size.  The below query will give you this from the Oracle Enterprise Manager (OEM) repository:

SELECT Database,
Month_Date,
round(sum(decode(metric_column, 'spaceUsed', maximum))/1024/1024, 3) Used_Size_Tb,
round(sum(decode(metric_column, 'spaceAllocated', maximum))/1024/1024, 3) Allocated_Size_Tb
FROM
(
SELECT target_name Database, trunc(rollup_timestamp, 'MONTH') Month_Date, key_value TB, metric_column, round(max(maximum),0) maximum
FROM mgmt$metric_daily
WHERE target_type = 'rac_database'
and metric_name = 'tbspAllocation'
and metric_column in ('spaceAllocated', 'spaceUsed')
and target_name in ('VERS')
GROUP BY target_name, key_value, trunc(rollup_timestamp, 'MONTH'), metric_column
)
GROUP BY Database, Month_Date
ORDER BY Database, Month_Date
/

Output:

DATABASE   MONTH_DAT USED_SIZE_TB ALLOCATED_SIZE_TB
---------- --------- ------------ -----------------
VERS       01-SEP-15        1.198             1.554
VERS       01-OCT-15        1.209             1.652
VERS       01-NOV-15          1.3             1.805
...
VERS       01-MAY-17        6.526             7.226
VERS       01-JUN-17        7.085             8.528
VERS       01-JUL-17        7.136             7.569

23 rows selected.

SQL>

The unit is in Tb, which should be suitable for most, however this can be changed by add/removing division of 1024.

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

How to obtain the Database Total Used, Allocated and Max Size

From time to time, it’s useful to know the total allocated size of a database at OS level, how much of it has been used and what the maximum total size the database can grow to at OS level.

The below query will give you this:

SELECT round(sum(used_ts_size)/1024/1024, 2) total_used_db_size_tb,
 round(sum(curr_ts_size)/1024/1024, 2) total_current_db_size_tb,
 round(sum(max_ts_size)/1024/1024, 2) total_max_allocated_db_size_tb
FROM
(SELECT df.tablespace_name, (df.bytes - sum(fs.bytes)) / (1024 * 1024) used_ts_size,
df.bytes / (1024 * 1024) curr_ts_size,
df.maxbytes / (1024 * 1024) max_ts_size
FROM dba_free_space fs,
 (select tablespace_name,
 sum(bytes) bytes,
 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes
 from dba_data_files
 group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes,df.maxbytes);

Output:

SQL> SELECT round(sum(used_ts_size)/1024/1024, 2) total_used_db_size_tb,
 2 round(sum(curr_ts_size)/1024/1024, 2) total_current_db_size_tb,
 3 round(sum(max_ts_size)/1024/1024, 2) total_max_allocated_db_size_tb
 4 FROM
 5 (SELECT df.tablespace_name, (df.bytes - sum(fs.bytes)) / (1024 * 1024) used_ts_size,
 6 df.bytes / (1024 * 1024) curr_ts_size,
 7 df.maxbytes / (1024 * 1024) max_ts_size
 8 FROM dba_free_space fs,
 9 (select tablespace_name,
 10 sum(bytes) bytes,
 11 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes
 12 from dba_data_files
 13 group by tablespace_name) df
 14 WHERE fs.tablespace_name (+) = df.tablespace_name
 15 GROUP BY df.tablespace_name,df.bytes,df.maxbytes);

TOTAL_USED_DB_SIZE_TB TOTAL_CURRENT_DB_SIZE_TB TOTAL_MAX_ALLOCATED_DB_SIZE_TB
--------------------- ------------------------ ------------------------------
                 7.15                     7.36                           9.04

SQL>

The unit is in Tb, which should be suitable for most, however this can be changed by add/removing division of 1024.

Related Post:
How to obtain the Historical Database Total Used and Allocated Size from OEM Repository

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)