PDB Lockdown profiles allow, on a multitenant database, to limit what a user can do inside a PDB.<\/p>\n\n\n\n
One use case is when we want to avoid high privileged users (like Application DBAs) to perform ALTER SYSTEM or ALTER SESSION commands.<\/p>\n\n\n\n
Quickly we would think we can do a PDB Lockdown profile like:<\/p>\n\n\n
\nSQL> create lockdown profile lock_test;\nSQL> alter lockdown profile lock_test disable statement=('ALTER SESSION');\nSQL> alter lockdown profile lock_test disable statement=('ALTER SYSTEM');\n<\/pre><\/div>\n\n\nThe problem of this simple profile is that we can lock ourselves, also as common user, inside the lock profile. <\/p>\n\n\n\n
Image that you want to enable this profile on several PDBs:<\/p>\n\n\n
\nSQL> alter session set container=pdb01;\nSession altered.\n\nSQL> alter system set pdb_lockdown=lock_test;\nSystem altered.\n\nSQL> alter session set container=samplepdb;\nERROR:\nORA-01031: insufficient privileges\n<\/pre><\/div>\n\n\nOups, you cannot anymore change the active container!<\/p>\n\n\n\n\n\n\n\n
One has to reconnect to the CDB and then can go to the second PDB and apply the lockdown profile:<\/p>\n\n\n
\nSQL> connect \/ as sysdba\nConnected.\n\nSQL> alter session set container=samplepdb;\nSession altered.\n\nSQL> alter system set pdb_lockdown=lock_test;\nSystem altered.\n<\/pre><\/div>\n\n\nWhat if now I (I’m the CDB DBA and have SYSDBA rights) want to change a parameter on one of the PDBs? <\/p>\n\n\n\n
It is not possible to disable the pdb_lockdown profile:<\/p>\n\n\n
\nSQL> connect \/ as sysdba\nConnected.\n\nSQL> alter session set container=samplepdb;\nSession altered.\n\nSQL> alter session set pdb_lockdown='';\nERROR:\nORA-01031: insufficient privileges\n\nSQL> alter system set pdb_lockdown='';\nalter system set pdb_lockdown=''\n*\nERROR at line 1:\nORA-01031: insufficient privileges\n<\/pre><\/div>\n\n\nTwo possible options are:<\/p>\n\n\n\n
- drop the PDB Lockdown on Root container<\/li><\/ul>\n\n\n
\nSQL> connect \/ as sysdba\nConnected.\n\nSQL> drop lockdown profile lock_test ;\nLockdown Profile dropped.\n\nSQL> alter session set container=samplepdb;\nSession altered.\n\nSQL> alter system set pdb_lockdown='';\nSystem altered.\n<\/pre><\/div>\n\n\n
- change the existing lockdown profile temporarily to allow operations<\/li><\/ul>\n\n\n
\nSQL> connect \/ as sysdba\nConnected.\n\nSQL> alter lockdown profile lock_test enable statement=('ALTER SYSTEM') users=common;\nLockdown Profile altered.\n\nSQL> alter session set container=samplepdb;\nSession altered.\n\nSQL> alter system set pdb_lockdown='';\nSystem altered.\n\n<\/pre><\/div>\n\n\nThe problem of this two solutions is that the relax of the lockdown profile will apply to all the PDBs where it is enabled. <\/p>\n\n\n\n
If we want just temporarily be super users and skip the lockdown profile, the solution is to disable on the session on the cdb$root and then change to another container. During our session we will not have any lockdown profile enabled.<\/p>\n\n\n
\nSQL> -- Connected to CDB$ROOT\nSQL> show con_name\nCON_NAME\n------------------------------\nCDB$ROOT\n\nSQL> -- Lockdown profiles enabled on PDBs\nSQL> select b.name PDB,a.name, value$ from pdb_spfile$ a join v$pdbs b on b.con_uid=a.pdb_uid where a.name='pdb_lockdown';\nPDB NAME VALUE$\n---------- -------------------- ---------------\nSAMPLEPDB pdb_lockdown 'LOCK_TEST'\nPDB01 pdb_lockdown 'LOCK_TEST'\n\nSQL> -- Lockdown profile disables alter system and alter session\nSQL> select con_id,profile_name,rule,clause,status,users,except_users from cdb_lockdown_profiles;\n CON_ID PROFILE_NA RULE CLAUS STATUS USERS EXCEPT_USE\n---------- ---------- ------------------------- ----- ------- ------ ----------\n 1 LOCK_TEST ALTER SESSION DISABLE ALL\n 1 LOCK_TEST ALTER SYSTEM DISABLE ALL\n\nSQL> -- As common user on cdb$root we can change the session pdb_lockdown profile\nSQL> alter session set pdb_lockdown='';\nSession altered.\n\nSQL> -- And this will go with us to the next container\nSQL> alter session set container=PDB01;\nSession altered.\n\nSQL> show parameter pdb_lockdown\nNAME TYPE VALUE\n------------------------------------ ----------- ------------------------------\npdb_lockdown string\n\nSQL> alter system set open_cursors=300;\nSystem altered.\n\nSQL> -- If we reconnect, the lockdown profile is still there\nSQL> connect \/ as sysdba\nConnected.\nSQL> alter session set container=PDB01;\nSession altered.\n\nSQL> show parameter pdb_lockdown\nNAME TYPE VALUE\n------------------------------------ ----------- ------------------------------\npdb_lockdown string LOCK_TEST\n\nSQL> alter system set open_cursors=300;\nalter system set open_cursors=300\n*\nERROR at line 1:\nORA-01031: insufficient privileges\n<\/pre><\/div>\n\n\n What is interesting to notice from this behaviour is:<\/p>\n\n\n\n
when changing a session parameter on cdb$root, this parameter will remain active also when going to another containers within the same session.<\/p><\/blockquote>\n\n\n\n
The parameters that were not changed on session level, will get adapted when changing to another container, with the values set specifically for that container.<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
PDB Lockdown profiles allow, on a multitenant database, to limit what a user can do inside a PDB. One use case is when we want to avoid high privileged users (like Application DBAs) to perform ALTER SYSTEM or ALTER SESSION commands. Quickly we would think we can do a PDB Lockdown profile like: The problem […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,39,9],"tags":[],"class_list":["post-404","post","type-post","status-publish","format-standard","category-oracle","category-oracle-19c","category-security","czr-hentry"],"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/404","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=404"}],"version-history":[{"count":2,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/404\/revisions"}],"predecessor-version":[{"id":453,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/404\/revisions\/453"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=404"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=404"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=404"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}