I have always found it interesting that DBAs that look after databases insist on limiting the privileges available to developers (which is a good thing to protect the data), but quite happily will log on to their database with every single one of the most powerful privilege known to mankind!
SQL> select * from session_roles;ROLE----------------------------------------CONNECTRESOURCEPDB_DBAAUDIT_ADMINAUDIT_VIEWERSELECT_CATALOG_ROLECAPTURE_ADMINADM_PARALLEL_EXECUTE_TASKACCHK_READSAGA_ADM_ROLESAGA_PARTICIPANT_ROLESAGA_CONNECT_ROLEAQ_ADMINISTRATOR_ROLEAQ_USER_ROLEDATAPUMP_CLOUD_EXPDATAPUMP_CLOUD_IMPPROVISIONERXS_SESSION_ADMINXS_NAMESPACE_ADMINXS_CACHE_ADMINXS_CONNECTSQL_FIREWALL_ADMINSQL_FIREWALL_VIEWEROSAK_ADMIN_ROLEGATHER_SYSTEM_STATISTICSOPTIMIZER_PROCESSING_RATERECOVERY_CATALOG_OWNERRECOVERY_CATALOG_OWNER_VPDRECOVERY_CATALOG_USERMAINTPLAN_APPNOTIFICATIONS_USERNOTIFICATIONS_ADMINPPLB_ROLEDGPDB_ROLEHS_ADMIN_SELECT_ROLEOEM_ADVISORDB_DEVELOPER_ROLESODA_APPLBAC_DBADWROLECONSOLE_ADMINCONSOLE_OPERATORCONSOLE_MONITORCONSOLE_DEVELOPERDCAT_SYNCADB_MONITORCTXAPPDV_SECANALYSTDV_MONITORDV_ADMINDV_OWNERDV_ACCTMGRDV_PATCH_ADMINDV_STREAMS_ADMINDV_GOLDENGATE_ADMINDV_XSTREAM_ADMINDV_GOLDENGATE_REDO_ACCESSDV_AUDIT_CLEANUPDV_DATAPUMP_NETWORK_LINKDV_POLICY_OWNERADPUSERAPEX_ADMINISTRATOR_READ_ROLEAPEX_ADMINISTRATOR_ROLEADPADMINDATA_TRANSFORM_USERLINEAGE_AUTHORORDS_ADMINISTRATOR_ROLEODIADMINPYQADMINRQADMINOML_SYS_ADMINOML_DEVELOPERGRAPH_DEVELOPERGRAPH_ADMINISTRATORPGX_SESSION_CREATEPGX_SERVER_GET_INFOPGX_SERVER_MANAGEPGX_SESSION_READ_MODELPGX_SESSION_MODIFY_MODELPGX_SESSION_NEW_GRAPHPGX_SESSION_GET_PUBLISHED_GRAPHPGX_SESSION_COMPILE_ALGORITHMPGX_SESSION_ADD_PUBLISHED_GRAPHPGX_SESSION_SET_IDLE_TIMEOUT84 rows selected.
As a DBA, my preference has always been the opposite. I want as few privileges as absolutely possible for two reasons:
- The first one is that if something goes disastrously wrong on a database, the finger is unlikely to be pointed at me because I simply didn’t have the privileges to do any of that.
- The second one is that if I am indeed responsible for a database, the absolute last thing I want to have happen as a DBA is for me to catastrophically ruin a database due to human error. There is no greater humiliation as a DBA than a harmless keystroke bringing down the business.
Even on a predominantly read-only database, the risks of logging on with escalated privileges are still there. For example, here’s a table I have put into a read-only tablespace, but lo and behold, an incorrectly cut-pasted DROP TABLE command in the wrong environment will still happily drop that table, even though it is in a read-only table space.
SQL> create table protect_me tablespace soe 2 as select * from scott.emp;Table created.SQL> alter tablespace soe read only;Tablespace altered.SQL> drop table protect_me purge;Table dropped.
Putting aside something as drastic as DDL, even an errant DML statement, perhaps cut and pasted into the wrong environment, is also a huge risk for DBAs when they have development, test and production connections all active at the same time.
SQL> select * from scott.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 30 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 1014 rows selected.(Phone call - "Hey Connor, can you clear out EMP in Development for me?")SQL> delete from scott.emp; -- whoops14 rows deleted.
That is why I am a big fan of this very simple feature in Oracle AI Database 26ai – the ability to set a session to being read only. If I was a DBA, I would set this as a logon trigger for every time I log on to a production database, just as a really safe insurance policy so that I can’t do anything disastrous unless I explicitly flip the session to being read-write.
SQL> alter session set read_only = true;Session altered.SQL> delete from scott.emp;delete from scott.emp*ERROR at line 1:ORA-28193: Can perform read operations onlySQL> select * 2 from scott.emp 3 for update;select * *ERROR at line 1:ORA-28193: Can perform read operations onlySQL> lock table scott.emp in exclusive mode;lock table scott.emp in exclusive mode*ERROR at line 1:ORA-28193: Can perform read operations only
and just returning to the drop table example, even that is blocked in a read-only session.
SQL> drop table scott.emp;drop table scott.emp*ERROR at line 1:ORA-28193: Can perform read operations onlySQL>
So DBAs protect yourself from… well… yourself! Make read-only sessions the default when you connect to a database.
Leave a Reply