We have hundreds of developers who need access hundreds of application schemas, deployed to multiple locations. Data is sensitive and there is a requirement that each human access must be done via a personal database account that has access to only allowed application schemas. That has always been a struggle for me how to manage all these access privileges in a nice and easy way. Since many many many different databases are involved, database itself cannot be the source of truth for the access privileges and so far we have just synchronised the access privileges from the source of truth system to all individual databases.<\/p>\n\n\n\n
I think there is a better way now – Secure Application Roles.<\/p>\n\n\n\n
The idea behind them is very flexible – there is no need to grant the individual roles to database users, the users need to execute a procedure (which will do all necessary validations) and then enable the role(s) for the user session.<\/p>\n\n\n\n
Lets first set up a common SECURITY_MANAGER schema, that will contain all our security related code and logging.<\/p>\n\n\n\n
create user security_manager no authentication\n default tablespace users\n quota 1g on users;\n\ngrant create table, create procedure to security_manager;\n\ncreate table security_manager.allowed_grants (\n db_username varchar2(128) not null,\n db_role varchar2(128) not null,\n primary key (db_username,db_role)\n) organization index;\n\ncreate table security_manager.role_grant_log (\n grant_time timestamp default sys_extract_utc(systimestamp) not null,\n db_username varchar2(128) not null,\n granted_role varchar2(128) not null,\n is_allowed number(1) not null,\n comments varchar2(1000),\n client_host varchar2(200),\n client_ip varchar2(50),\n unified_audit_session_id varchar2(100)\n);<\/code><\/pre>\n\n\n\nHere are two helper programs, since ACTIVATE_ROLE procedure below will need to read and write to tables in SECURITY_MANAGER schema and I do not want to grant access to these tables to users directly.<\/p>\n\n\n\n
-- The job of the following procedure is just logging the grant request\nCREATE OR REPLACE PROCEDURE security_manager.log_role_grant\n (p_requested_role role_grant_log.granted_role%TYPE\n , p_is_allowed role_grant_log.is_allowed%TYPE\n , p_comments role_grant_log.comments%TYPE) \nACCESSIBLE BY (activate_role) IS\n PRAGMA AUTONOMOUS_TRANSACTION;\nBEGIN\n INSERT INTO role_grant_log\n (db_username, granted_role, is_allowed, comments\n , client_host, client_ip, unified_audit_session_id)\n VALUES \n (SYS_CONTEXT('USERENV','SESSION_USER')\n , p_requested_role, p_is_allowed, SYS_CONTEXT('USERENV','HOST')\n , SYS_CONTEXT('USERENV','IP_ADDRESS')\n , SYS_CONTEXT('USERENV','UNIFIED_AUDIT_SESSIONID'));\n COMMIT;\nEND;\n\/\n\n-- The following function just check the master autorisation table if\n-- user is allowed to activate the role or not\nCREATE OR REPLACE FUNCTION security_manager.is_role_allowed\n (p_username allowed_grants.db_username%TYPE\n , p_requested_role role_grant_log.granted_role%TYPE) RETURN boolean\nACCESSIBLE BY (activate_role) IS\n v_is_role_allowed NUMBER;\nBEGIN\n SELECT COUNT(*) INTO v_is_role_allowed\n FROM allowed_grants\n WHERE db_username = p_username AND db_role = UPPER(p_requested_role);\n RETURN v_is_role_allowed = 1;\nEND;\n\/<\/code><\/pre>\n\n\n\nNow the security code itself. The procedure below just checks from a simple table, if the logged in user is allowed to activate the requested role or not and also logs the request. In real life it can be much more complex – the code could make a REST call to external autorisation system and ofcourse logging should be much more detailed.<\/p>\n\n\n\n
NB! The procedure must be declared AUTHID CURRENT_USER – using invokers rights.<\/strong><\/p>\n\n\n\nCREATE OR REPLACE PROCEDURE security_manager.activate_role\n (p_requested_role allowed_grants.db_role%TYPE\n , p_comments role_grant_log.comments%TYPE)\nAUTHID CURRENT_USER IS\n v_activated_roles VARCHAR2(4000);\nBEGIN\n -- Check if users is allowd to activate the requested role\n IF NOT is_role_allowed(SYS_CONTEXT('USERENV','SESSION_USER'), p_requested_role) THEN\n log_role_grant(upper(p_requested_role), 0, p_comments);\n raise_application_error(-20000, 'You are not allowed to activate the requested role.');\n END IF;\n -- Query all roles that are currently active for the session and append the requested role to that list\n SELECT listagg(role, ',') WITHIN GROUP (ORDER BY role) INTO v_activated_roles FROM (\n SELECT role FROM session_roles\n UNION\n SELECT upper(p_requested_role) FROM dual\n ); \n -- Activate all roles\n log_role_grant(upper(p_requested_role), 1, p_comments);\n DBMS_SESSION.SET_ROLE(v_activated_roles);\nEND;\n\/<\/code><\/pre>\n\n\n\nNow I create the role itself and grant the role read access to one application table. Here IDENTIFIED USING<\/strong> clause does the magic – it tells Oracle that sec_app_role_test1<\/strong> role can only be enabled by security_manager.activate_role<\/strong> procedure.<\/p>\n\n\n\nCREATE ROLE sec_app_role_test1\n IDENTIFIED USING security_manager.activate_role;\n\nGRANT READ ON app1.t1 TO sec_app_role_test1;<\/code><\/pre>\n\n\n\nAnd my developer personal account is called ILMKER and this account only needs execute privileges on my security package. In real life you would grant this execute to a common role that all developers have (in my case that custom role is called PERSONAL_ACCOUNT).<\/p>\n\n\n\n
GRANT execute ON security_manager.activate_role TO ilmker;<\/code><\/pre>\n\n\n\nBy default ILMKER user cannot access table APP1.T1.<\/p>\n\n\n\n
ILMKER SQL> SELECT * FROM session_roles;\n\nROLE \n-----------------\nPERSONAL_ACCOUNT\n\nILMKER SQL> SELECT * FROM app1.t1;\n\nSQL Error: ORA-00942: table or view does not exist<\/code><\/pre>\n\n\n\nLets test using my developer account ILMKER… first I try to request a role that I do not have been granted access to. No luck, I get the exception “ORA-20000: You are not allowed to activate the requested role.”<\/p>\n\n\n\n
SQL> SELECT * FROM session_roles;\n\nROLE \n--------------------\nPERSONAL_ACCOUNT\n\nSQL> exec security_manager.activate_role('sec_app_role_test1', 'Jira ref: ABC-490');\n\nORA-20000: You are not allowed to activate the requested role.\n\nSQL> SELECT * FROM session_roles;\n\nROLE \n--------------------\nPERSONAL_ACCOUNT\n\nSQL> SELECT * FROM app1.t1;\n\nSQL Error: ORA-00942: table or view does not exist<\/code><\/pre>\n\n\n\nAfter security administrator grants me the role – inserts a row to security_manager.allowed_grants table for this example and NOT executing Oracle GRANT command.<\/p>\n\n\n\n
insert into security_manager.allowed_grants (db_username, db_role)\n values ('ILMKER', upper('sec_app_role_test1'));\ncommit;<\/code><\/pre>\n\n\n\nI ask my developer to run again.<\/p>\n\n\n\n
SQL> SELECT * FROM session_roles;\n\nROLE \n--------------------\nPERSONAL_ACCOUNT\n\nSQL> exec security_manager.activate_role('sec_app_role_test1', 'Jira ref: ABC-490');\n\nPL\/SQL procedure successfully completed.\n\nSQL> SELECT * FROM session_roles;\n\nROLE \n--------------------\nPERSONAL_ACCOUNT\nSEC_APP_ROLE_TEST1\n\nSQL> SELECT * FROM app1.t1;\nno rows selected<\/code><\/pre>\n\n\n\nThe developer is happy now! Role was activated in the developer session and developer was able to read the application table. All requests were also logged by SECURITY_MANAGER schema.<\/p>\n\n\n\n
SQL> select * from security_manager.role_grant_log;\n\nGRANT_TIME DB_USERNAME GRANTED_ROLE CLIENT_HOST CLIENT_IP UNIFIED_AUDIT_SESSION_ID IS_ALLOWED COMMENTS \n2023-05-12 15:05:28,478732000 ILMKER SEC_APP_ROLE_TEST1 STH-FVFFV04QQ05R 10.88.17.241 3428220339 0 Jira ref: ABC-490 \n2023-05-12 15:10:41,923908000 ILMKER SEC_APP_ROLE_TEST1 STH-FVFFV04QQ05R 10.88.17.241 3428220339 1 Jira ref: ABC-490<\/pre>\n\n\n\nI think this is a powerful feature to control access to data based on much more complex criteria than just DBA executing GRANT commands. Before enabling the role code can make a REST query to external autorisation system, check the client host IP, check the client authentication method, enable detailed unified auditing policies. Possibilities are endless!<\/p>\n","protected":false},"excerpt":{"rendered":"
We have hundreds of developers who need access hundreds of application schemas, deployed to multiple locations. Data is sensitive and there is a requirement that each human access must be done via a personal database account that has access to only allowed application schemas. That has always been a struggle for me how to manage […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[4,63],"class_list":["post-637","post","type-post","status-publish","format-standard","hentry","category-blog-entry","tag-oracle","tag-security"],"_links":{"self":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/637","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/comments?post=637"}],"version-history":[{"count":5,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/637\/revisions"}],"predecessor-version":[{"id":642,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/637\/revisions\/642"}],"wp:attachment":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/media?parent=637"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/categories?post=637"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/tags?post=637"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}