<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Fakhri Gulamov on Medium]]></title>
        <description><![CDATA[Stories by Fakhri Gulamov on Medium]]></description>
        <link>https://medium.com/@fakhree.gulamov?source=rss-c3eb4e3e9136------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*J8RZb57SQG4LMA0WtN3e9g.png</url>
            <title>Stories by Fakhri Gulamov on Medium</title>
            <link>https://medium.com/@fakhree.gulamov?source=rss-c3eb4e3e9136------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 03:07:36 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@fakhree.gulamov/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Applying Restrictions on Column with Oracle Virtual Private Database (VPD)]]></title>
            <link>https://medium.com/@fakhree.gulamov/applying-restrictions-on-column-with-oracle-virtual-private-database-vpd-24b7be0b0474?source=rss-c3eb4e3e9136------2</link>
            <guid isPermaLink="false">https://medium.com/p/24b7be0b0474</guid>
            <dc:creator><![CDATA[Fakhri Gulamov]]></dc:creator>
            <pubDate>Mon, 07 Apr 2025 18:17:00 GMT</pubDate>
            <atom:updated>2025-04-07T18:17:00.651Z</atom:updated>
            <content:encoded><![CDATA[<p>Oracle Virtual Private Database (VPD) is a powerful security mechanism that enhances data protection by dynamically filtering SQL queries at the user level. This blog will explain the practical implementation of a <strong>VPD policy</strong> on the <strong>FIN</strong> field.</p><h3>1. What is the Requirement?</h3><p>Imagine you have a table that stores <strong>FIN codes, account numbers, customer names, and other sensitive information</strong>. You have been provided with a list of <strong>FIN codes that need to be hidden</strong>. Our goal is to <strong>prevent regular users from seeing rows containing these FIN values</strong>, while allowing certain privileged users (whitelisted, such as <strong>service accounts</strong>) to access everything without restrictions.</p><h3>2. Creating the Tables</h3><p>To achieve this, we define two tables:</p><ul><li><strong>BLACKLIST</strong>: Stores <strong>FIN</strong> values that should be restricted.</li><li><strong>WHITELIST_USERS</strong>: Stores a list of users exempt from these restrictions.</li></ul><pre>CREATE TABLE BLACKLIST (<br>  FIN VARCHAR2(50) NOT NULL,<br>  CUSTOMER_NO VARCHAR2(100)<br>);</pre><pre>CREATE TABLE WHITELIST_USERS (<br>  USERNAME VARCHAR2(20) NOT NULL<br>);</pre><h3>3. Creating the VPD Function</h3><p>This function checks if the current user is in the whitelist. If not, it applies a restriction, preventing access to rows containing <strong>blacklisted FINs</strong>.</p><pre>CREATE OR REPLACE FUNCTION VPD_POLICY_FIN (<br>  schema_name VARCHAR2,<br>  object_name VARCHAR2<br>) RETURN VARCHAR2<br>AS<br>  predicate VARCHAR2(4000);<br>  v_count NUMBER;<br>BEGIN<br>  -- Check if the user is in the whitelist<br>  SELECT COUNT(1) INTO v_count<br>  FROM WHITELIST_USERS<br>  WHERE USERNAME = SYS_CONTEXT(&#39;USERENV&#39;, &#39;SESSION_USER&#39;);</pre><pre> -- Apply restriction for non-whitelisted users<br>IF v_count = 0 THEN<br>    predicate := &#39;FIN NOT IN (SELECT FIN FROM BLACKLIST)&#39;;<br>  ELSE<br>    predicate := &#39;1=1&#39;;  -- Whitelisted user, no restriction<br>  END IF;</pre><pre>RETURN predicate;<br>END;<br>/</pre><h3>4. Applying the VPD Policy</h3><p>Now, let’s activate the VPD policy on the table using <strong>DBMS_RLS.ADD_POLICY</strong>.</p><pre>BEGIN  <br>  DBMS_RLS.ADD_POLICY(  <br>    object_schema   =&gt; &#39;TABLE_OWNER&#39;,         -- the schema where the table resides  <br>    object_name     =&gt; &#39;TABLE_NAME&#39;,          -- the name of the table to protect  <br>    policy_name     =&gt; &#39;VPD_POLICY_FIN&#39;,      -- the name you want to assign to the policy  <br>    function_schema =&gt; &#39;SYS&#39;,                -- the schema where the VPD function is created  <br>    policy_function =&gt; &#39;VPD_POLICY_FIN&#39;,      -- the name of the VPD function  <br>    statement_types =&gt; &#39;SELECT&#39;,              -- apply the policy only to SELECT statements  <br>    enable          =&gt; TRUE<br>  );  <br>END;<br>/</pre><p>This ensures that the <strong>VPD policy</strong> is enforced on <strong>TABLE_NAME</strong>. Replace <strong>TABLE_OWNER</strong> and <strong>TABLE_NAME</strong> with actual schema and table names in your database.</p><h3>5. Testing the VPD Policy</h3><p>When different users run a simple <strong>SELECT</strong> query, we can verify whether the restrictions work as expected.</p><pre>-- As user &#39;NORMAL_USER&#39;<br>SELECT * FROM TABLE_NAME;  -- Blacklisted FIN rows will be hidden<br><br>-- As user &#39;SERVICE_USER&#39; (in whitelist)<br>SELECT * FROM TABLE_NAME;  -- All rows visible</pre><ul><li>✅ <strong>Whitelisted users</strong> will see all records.</li><li>🚫 <strong>Other users</strong> will not see rows containing <strong>blacklisted FINs</strong>.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/528/0*JGtivcAWgjAT99i2.png" /></figure><h3>6. Removing the VPD Policy</h3><p>If necessary, you can remove the VPD policy with the following command:</p><pre>BEGIN<br>  DBMS_RLS.DROP_POLICY (<br>    object_schema   =&gt; &#39;TABLE_OWNER&#39;,<br>    object_name     =&gt; &#39;TABLE_NAME&#39;,<br>    policy_name     =&gt; &#39;VPD_POLICY_FIN&#39;<br>  );<br>END;<br>/</pre><h3>7. Conclusion</h3><p>With this approach, we successfully implemented an <strong>Oracle VPD policy</strong> to <strong>dynamically restrict access</strong> to specific FIN records. <strong>Whitelisted users</strong> are exempt from restrictions, while <strong>non-whitelisted users</strong> cannot view rows containing <strong>blacklisted FINs</strong>. This method provides an effective way to enhance security in your Oracle database.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=24b7be0b0474" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Why Does Autoextend in Oracle Exceed the Defined Limit?]]></title>
            <link>https://medium.com/@fakhree.gulamov/oracle-19c-d%C9%99-autoextend-niy%C9%99-t%C9%99yin-edil%C9%99n-h%C9%99ddi-a%C5%9F%C4%B1r-34e773339754?source=rss-c3eb4e3e9136------2</link>
            <guid isPermaLink="false">https://medium.com/p/34e773339754</guid>
            <dc:creator><![CDATA[Fakhri Gulamov]]></dc:creator>
            <pubDate>Wed, 19 Mar 2025 07:51:50 GMT</pubDate>
            <atom:updated>2025-03-20T22:14:25.778Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>Why Does Autoextend in Oracle Exceed the Defined Limit?</strong> 🚀</h3><p>For Oracle DBAs, this is a familiar scenario: <strong>autoextend NEXT 100MB</strong> is set, but suddenly, <strong>the datafile expands by 10GB or more!</strong> 😳 Is Oracle playing tricks on us? 😂 Nope, this is actually part of Oracle’s <strong>smart storage management</strong> system.</p><p>Let’s dive into why this happens and how to take control of it.</p><h3>Why Does Oracle Ignore the NEXT 100MB Setting?</h3><p>Starting from Oracle 11g Release 2 (11.2) and continuing through Oracle 12c, 18c, 19c, and later versions, the Autoextend feature does not always follow the exact NEXT increment specified. Even if you set NEXT 100MB, Oracle may extend the datafile by several gigabytes at once. This behavior is due to the SMCO (Space Management Coordinator) background process, which proactively predicts future space requirements and preallocates additional space to optimize performance and prevent frequent extensions.</p><h4>Key Reasons for This Behavior:</h4><p>✅ <strong>SMCO “</strong><a href="https://docs.oracle.com/en/database/oracle/oracle-database/23//dbiad/db_SMCO.html">Space Management Coordinator Process</a><strong>”</strong> — Oracle tries to allocate space before it’s urgently needed.</p><p>✅ <strong>Extent Allocation Strategy</strong> — Instead of growing 100MB at a time, Oracle increases space in larger increments to prevent frequent extensions.</p><p>✅ <strong>Segment Growth &amp; HWM (High Water Mark)</strong> — Oracle ensures enough space for large tables and indexes to avoid fragmentation.</p><p>✅ <strong>ASM Optimizations</strong> — If using ASM, Oracle balances file growth across storage.</p><p>So, can we <strong>tame this over-prepared Oracle?</strong> 🤔</p><h3>How to Control Oracle’s Autoextend Behavior?</h3><p>If unexpected autoextend growth is causing headaches, there are ways to <strong>regain control</strong>:</p><h4>1. Disable SMCO’s Overactive Predictions (_enable_space_preallocation)</h4><p>Setting this to 0 prevents Oracle from proactively extending space before it’s actually needed.</p><pre>ALTER SYSTEM SET &quot;_enable_space_preallocation&quot; = 0 SCOPE=SPFILE;</pre><p>(This requires a database restart.)</p><h4>2. Adjust Oracle’s Free Space Buffer (_kttext_warning)</h4><p>By default, Oracle reserves <strong>5% of tablespace free space</strong>. Reducing this to <strong>2%</strong> makes Oracle extend less aggressively.</p><pre>ALTER SYSTEM SET &quot;_kttext_warning&quot; = 2 SCOPE=SPFILE;</pre><h4>3. Turn Off Autoextend and Manually Manage Growth 🛑</h4><p>For full control over storage allocation:</p><pre>ALTER DATABASE DATAFILE &#39;+DATAGROUP/CFILEDB/DATAFILE/users.673.1195982003&#39; AUTOEXTEND OFF;<br>ALTER DATABASE DATAFILE &#39;+DATAGROUP/CFILEDB/DATAFILE/users.673.1195982003&#39; RESIZE 15G;</pre><p>This ensures that Oracle won’t make surprise expansions.</p><h3>Conclusion: Training Oracle to Follow Orders 🧐</h3><p>Oracle is <strong>smart, but sometimes overly cautious</strong>. If unexpected autoextend expansions are causing trouble, <strong>tweaking these hidden parameters</strong> can bring it under control.</p><p>The real question is: <strong>Do you trust Oracle’s predictions, or do you need to discipline it?</strong> 😆 <strong>Drop your thoughts in the comments!</strong> 👇</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=34e773339754" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>