{"id":484,"date":"2011-04-29T11:55:53","date_gmt":"2011-04-29T09:55:53","guid":{"rendered":"http:\/\/www.oradba.ch\/?p=484"},"modified":"2012-06-25T23:57:54","modified_gmt":"2012-06-25T21:57:54","slug":"find-user-with-unlimited-tablespace-quota","status":"publish","type":"post","link":"https:\/\/www.oradba.ch\/wordpress\/2011\/04\/find-user-with-unlimited-tablespace-quota\/","title":{"rendered":"Find User with unlimited Tablespace Quota"},"content":{"rendered":"<p>When performing a database security audit various informations about users, roles and privileges have to be collected, including &#8220;who has unlimited tablespace on SYSTEM&#8221;. It is quite easy to find user with UNLIMITED TABLESPACE or a UNLIMITED quota on SYSTEM. But what when the system privilege is assigned to a role or over several roles? It is still easy if you use hierarchical queries to drill down the cascaded roles, but there is plenty to write.<\/p>\n<h3>Ok, why do we want to know who has unlimited quota on the SYSTEM tablespace?<\/h3>\n<p>User who have unlimited quota on SYSTEM as well the privilege to create tables or clusters could do some kind of denial of service attack to the database. The just have to fill up the free space in the SYSTEM tablespace. If there default tablespace is as well SYSTEM the could even do this without intention.\n<\/p>\n<h3>Then let&#8217;s find them<\/h3>\n<p>First find the user with direct quota on tablespace SYSTEM. <\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">\nSELECT username,tablespace_name, bytes, max_bytes \nFROM dba_ts_quotas \nWHERE max_bytes = -1 AND tablespace_name =&#039;SYSTEM&#039;;\n\nUSERNAME                  TABLESPACE_NAME                BYTES  MAX_BYTES\n------------------------- ------------------------- ---------- ----------\nSCOTT                     SYSTEM                             0         -1\nTEST                      SYSTEM                             0         -1\n<\/pre>\n<p>As mentioned above it is quite easy to find user with system privilege UNLIMITED TABLESPACE.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">\nselect * from dba_sys_privs where privilege = &#039;UNLIMITED TABLESPACE&#039;\n\nGRANTEE                        PRIVILEGE                      ADM\n------------------------------ ------------------------------ ---\nWMSYS                          UNLIMITED TABLESPACE           NO\nRRDOMREG                       UNLIMITED TABLESPACE           NO\nHR                             UNLIMITED TABLESPACE           NO\nOE                             UNLIMITED TABLESPACE           NO\nSYS                            UNLIMITED TABLESPACE           NO\nLOGSTDBY_ADMINISTRATOR         UNLIMITED TABLESPACE           NO\nSCOTT                          UNLIMITED TABLESPACE           NO\nBI                             UNLIMITED TABLESPACE           NO\nOUTLN                          UNLIMITED TABLESPACE           NO\nDBSNMP                         UNLIMITED TABLESPACE           NO\nIX                             UNLIMITED TABLESPACE           NO\nSH                             UNLIMITED TABLESPACE           NO\nDBA                            UNLIMITED TABLESPACE           YES\nSYSTEM                         UNLIMITED TABLESPACE           YES\n\n14 rows selected.\n<\/pre>\n<h3>What about cascaded roles?<\/h3>\n<p>Mmh, but since Oracle 11g it is possible to grant UNLIMITED TABLESPACE to a role and this can be granted to an other role which is granted again to an other role. It could be a role concept or somebody who want to hide a little bit some privileges. To test it I&#8217;ve created three roles DBA3, DBA2 and DBA1, granted UNLIMITED TABLESPACE to DBA3.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">\nSELECT \n  grantee, \n  privilege, \n  DECODE(p,&#039;=&gt;&#039;||grantee,&#039;direct&#039;,p) path\nFROM (\n  SELECT \n    grantee, \n    privilege,\n    SYS_CONNECT_BY_PATH(grantee, &#039;=&gt;&#039;) p\n  FROM (\n    SELECT \n      grantee, \n      privilege\n    FROM dba_sys_privs\n    UNION ALL\n    SELECT \n      grantee, \n      granted_role privilege\n    FROM \n      dba_role_privs)\n  START WITH privilege = &#039;UNLIMITED TABLESPACE&#039;\n  CONNECT BY PRIOR grantee = privilege )\nWHERE \n  (grantee in (SELECT username FROM dba_users)\n  OR grantee = &#039;PUBLIC&#039;);\n\nGRANTEE   PRIVILEGE               PATH\n--------- ----------------------- -------------------------------\nBI        UNLIMITED TABLESPACE    direct\nSYS       DBA                     =&gt;DBA=&gt;SYS\nSYSTEM    DBA                     =&gt;DBA=&gt;SYSTEM\nSCOTT     DBA1                    =&gt;DBA3=&gt;DBA2=&gt;DBA1=&gt;SCOTT\nSYS       DBA1                    =&gt;DBA3=&gt;DBA2=&gt;DBA1=&gt;SYS\nSYS       DBA2                    =&gt;DBA3=&gt;DBA2=&gt;SYS\nSYS       DBA3                    =&gt;DBA3=&gt;SYS\nDBSNMP    UNLIMITED TABLESPACE    direct\nHR        UNLIMITED TABLESPACE    direct\nIX        UNLIMITED TABLESPACE    direct\nSYS       LOGSTDBY_ADMINISTRATOR  =&gt;LOGSTDBY_ADMINISTRATOR=&gt;SYS\nOE        UNLIMITED TABLESPACE    direct\nOUTLN     UNLIMITED TABLESPACE    direct\nRRDOMREG  UNLIMITED TABLESPACE    direct\nSH        UNLIMITED TABLESPACE    direct\nSYS       UNLIMITED TABLESPACE    direct\nSYSTEM    UNLIMITED TABLESPACE    direct\nWMSYS     UNLIMITED TABLESPACE    direct\n\n18 rows selected.\n<\/pre>\n<p>We now see that SCOTT has UNLIMITED TABLESPACE through DBA3, DBA2 and DBA1<\/p>\n<h3>All wrapped up<\/h3>\n<p>Ok, create one to find user&#8217;s with direct quotas as well through a system privilege will give something like this.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">\nSELECT \n  username,\n  tablespace_name,\n  privilege\nFROM (\n  SELECT \n    grantee username, &#039;Any Tablespace&#039; tablespace_name, privilege\n  FROM (\n    -- first get the users with direct grants\n    SELECT \n      p1.grantee grantee, privilege\n    FROM \n      dba_sys_privs p1\n    WHERE \n      p1.privilege=&#039;UNLIMITED TABLESPACE&#039;\n    UNION ALL\n    -- and then the ones with UNLIMITED TABLESPACE through a role...\n    SELECT \n      r3.grantee, granted_role privilege\n    FROM \n      dba_role_privs r3\n      START WITH r3.granted_role IN (\n          SELECT \n            DISTINCT p4.grantee \n          FROM \n            dba_role_privs r4, dba_sys_privs p4 \n          WHERE \n            r4.granted_role=p4.grantee \n            AND p4.privilege = &#039;UNLIMITED TABLESPACE&#039;)\n    CONNECT BY PRIOR grantee = granted_role)\n    -- we just whant to see the users not the roles\n  WHERE grantee IN (SELECT username FROM dba_users) OR grantee = &#039;PUBLIC&#039;\n  UNION ALL \n  -- list the user with unimited quota on a dedicated tablespace\n  SELECT \n    username,tablespace_name,&#039;DBA_TS_QUOTA&#039; privilege \n  FROM \n    dba_ts_quotas \n  WHERE \n    max_bytes = -1 )\nWHERE tablespace_name LIKE UPPER(&#039;SYSTEM&#039;) \n    OR tablespace_name = &#039;Any Tablespace&#039;;\n\n\nUSERNAME                  TABLESPACE_NAME           PRIVILEGE\n------------------------- ------------------------- ------------------------------\n...\nSYSTEM                    Any Tablespace            UNLIMITED TABLESPACE\nSYS                       Any Tablespace            DBA\nSYSTEM                    Any Tablespace            DBA\nSCOTT                     Any Tablespace            DBA1\nSYS                       Any Tablespace            DBA1\nSYS                       Any Tablespace            DBA2\nSYS                       Any Tablespace            DBA3\nSYS                       Any Tablespace            LOGSTDBY_ADMINISTRATOR\nTEST                      SYSTEM                    DBA_TS_QUOTA\n\n19 rows selected.\n<\/pre>\n<p>Due to the fact that the query is far to long to write more than one time, I&#8217;ve put everything in one script.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">\n@tsq SYSTEM\n\nUser Name     Tablespace Name        Privilege\n------------- ---------------------- -------------------------\nBI            Any Tablespace         UNLIMITED TABLESPACE\nDBSNMP        Any Tablespace         UNLIMITED TABLESPACE\nHR            Any Tablespace         UNLIMITED TABLESPACE\nIX            Any Tablespace         UNLIMITED TABLESPACE\nOE            Any Tablespace         UNLIMITED TABLESPACE\nOUTLN         Any Tablespace         UNLIMITED TABLESPACE\n....\n<\/pre>\n<p>The tsq.sql script can be downloaded at the <a href=\"\/scripts\">script section<\/a> of OraDBA or direct (<img data-recalc-dims=\"1\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/plugins\/wp-downloadmanager\/images\/ext\/unknown.gif?w=625&#038;ssl=1\" alt=\"\" title=\"\" style=\"vertical-align: middle;\" \/>&nbsp;<a href=\"https:\/\/www.oradba.ch\/wordpress\/download\/tsq.sql\">tsq.sql<\/a>). All OraDBA scripts can also be downloaded as a complete archive (<img data-recalc-dims=\"1\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/plugins\/wp-downloadmanager\/images\/ext\/unknown.gif?w=625&#038;ssl=1\" alt=\"\" title=\"\" style=\"vertical-align: middle;\" \/>&nbsp;<a href=\"https:\/\/www.oradba.ch\/wordpress\/download\/oradba.tgz\">oradba.tgz<\/a>).<\/p>\n","protected":false},"excerpt":{"rendered":"<p>When performing a database security audit various informations about users, roles and privileges have to be collected, including &#8220;who has unlimited tablespace on SYSTEM&#8221;. It is quite easy to find user with UNLIMITED TABLESPACE or a UNLIMITED quota on SYSTEM. But what when the system privilege is assigned to a role or over several roles? It is still easy if you use hierarchical queries to drill down the cascaded roles, but there is plenty to write&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[6,7,8,11,32],"tags":[137,18],"class_list":["post-484","post","type-post","status-publish","format-standard","hentry","category-10gr2","category-11gr1","category-11gr2","category-security","category-security-audit","tag-security-audit","tag-trivadiscontent"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p1aErb-7O","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":606,"url":"https:\/\/www.oradba.ch\/wordpress\/2011\/09\/tablespace-quotas-are-forever\/","url_meta":{"origin":484,"position":0},"title":"Tablespace quotas are forever","author":"Stefan","date":"22. September 2011","format":false,"excerpt":"It looks that not only diamonds are forever but also tablespace quotas. Due to the fact that Oracle has a history of tablespaces, it's kind of obvious that it also keeps information quotas. Is this an expected behavior? What could be the impact ?","rel":"","context":"In &quot;10gR2&quot;","block_context":{"text":"10gR2","link":"https:\/\/www.oradba.ch\/wordpress\/category\/oracle-database\/10gr2\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":600,"url":"https:\/\/www.oradba.ch\/wordpress\/2011\/09\/tablespace-point-in-time-recovery-and-oracle-11-2-0-2\/","url_meta":{"origin":484,"position":1},"title":"Tablespace point in time recovery and Oracle 11.2.0.2","author":"Stefan","date":"1. September 2011","format":false,"excerpt":"Tablespace point in time recovery (TSPITR) in particular, the fully automatic TSPITR is not a 11g new feature. It is likely that this can be used without any problem. But it looks like that a bug has been introduced with the latest patchset (11.2.0.2). What is the problem and how\u2026","rel":"","context":"In &quot;11gR2&quot;","block_context":{"text":"11gR2","link":"https:\/\/www.oradba.ch\/wordpress\/category\/oracle-database\/11gr2\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/TSPITR-300x278.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":527,"url":"https:\/\/www.oradba.ch\/wordpress\/2011\/05\/database-audit-and-audit-trail-purging\/","url_meta":{"origin":484,"position":2},"title":"Database Audit and Audit trail purging","author":"Stefan","date":"30. May 2011","format":false,"excerpt":"Setting up database audit is fairly easy. Since the availability of DBMS_AUDIT_MGMT the housekeeping of the audit trail is just a trifle. After the introduction of DBMS_AUDIT_MGMT in an post early this February, I would like to take a closer look at the housekeeping of the Audit Trail and provide\u2026","rel":"","context":"In &quot;10gR2&quot;","block_context":{"text":"10gR2","link":"https:\/\/www.oradba.ch\/wordpress\/category\/oracle-database\/10gr2\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2202,"url":"https:\/\/www.oradba.ch\/wordpress\/2016\/11\/oracle-12-release-2-documentation-available\/","url_meta":{"origin":484,"position":3},"title":"Oracle 12 Release 2 Documentation available","author":"Stefan","date":"8. November 2016","format":false,"excerpt":"Oracle just released the documentation for Oracle 12c Release 2. It seems that most of the new security features are available as discussed in my presentation at DOAG SIG Security in D\u00fcsseldorf on the 18th of october. See docs.oracle.com for the documentation bookshelf. Yet a short summary of new security\u2026","rel":"","context":"In &quot;12cR2&quot;","block_context":{"text":"12cR2","link":"https:\/\/www.oradba.ch\/wordpress\/category\/oracle-database\/12cr2\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":7707,"url":"https:\/\/www.oradba.ch\/wordpress\/2020\/11\/oracle-security-eus-snippets-setup-proxy-user-privileges\/","url_meta":{"origin":484,"position":4},"title":"Oracle Security EUS Snippets &#8211; Setup Proxy User Privileges","author":"Stefan","date":"5. November 2020","format":false,"excerpt":"Since I'm always short of time for a longer blog post, I'll just try a short one. Intended as a mini-series, I will show different configuration examples for Oracle Enterprise User Security. Today I\u2019ll start with the configuration of EUS based proxy privileges. The environment I use is DOE, my\u2026","rel":"","context":"In &quot;12R2&quot;","block_context":{"text":"12R2","link":"https:\/\/www.oradba.ch\/wordpress\/category\/oracle-database\/12r2\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":13852,"url":"https:\/\/www.oradba.ch\/wordpress\/2023\/08\/sql-toolbox-for-simplified-oracle-unified-audit-data-analysis\/","url_meta":{"origin":484,"position":5},"title":"SQL Toolbox for simplified Oracle Unified Audit Data Analysis","author":"Stefan","date":"28. August 2023","format":false,"excerpt":"On my journey through the area of database security, Oracle Unified Audit has been a constant companion. I not only created audit concepts, but also often had the opportunity to implement them. Besides the configuration, the administration and evaluation of the audit data was always part of it. Occasionally I\u2026","rel":"","context":"In &quot;19c&quot;","block_context":{"text":"19c","link":"https:\/\/www.oradba.ch\/wordpress\/category\/oracle-database\/19c\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/saua_teact.sql.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/saua_teact.sql.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/saua_teact.sql.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/saua_teact.sql.png?resize=700%2C400&ssl=1 2x"},"classes":[]}],"_links":{"self":[{"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/posts\/484","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/comments?post=484"}],"version-history":[{"count":9,"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/posts\/484\/revisions"}],"predecessor-version":[{"id":871,"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/posts\/484\/revisions\/871"}],"wp:attachment":[{"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/media?parent=484"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/categories?post=484"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/tags?post=484"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}