Using a Proxy User for Code Deployment in Oracle

“Proxy Users !” exclaimed Debbie.
“I say, that’s rather harsh don’t you think ?” came the rather startled reply from her boss.
Debbie sighed. They were in the midst of a discussion on the subject of how best to deploy database changes to multiple schemas.
“I meant”, she replied with iron patience, “that we could set up a proxy user to connect as each application owner in turn. That way, we wouldn’t have to grant those CREATE ANY privileges that get auditors so worried”.
“Oh, I see”, said Mike, who didn’t.
Not for the first time, Debbie wondered whether she had been lumbered with a less competent man as her boss simply in order to imbue this post with a semblance of social realism.
“I think”, she said, “that it’s time to move on to the techie bit.”
Debbie is right, as usual…

In order to make a change in Oracle ( or any database for that matter), you need at some point to connect to the database and run some SQL.
This is relatively straightforward if you are using the schema that is – or will be – the owner of the objects you are creating or changing.

However, this may not be possible if the account is identified externally or – in more recent releases – it’s a schema only account.
So, what is the best way to setup and use an account to make such changes in other schemas ?

The changes

NOTE : whilst the code examples that follow are written using Oracle 18c, This technique will also work in releases as far back as 10G.
I’ll point out any container specific syntax as it comes up.

We want to create a new table in the HR schema :

create table new_dept as select * from departments
/

create or replace view it_vw as 
    select * from departments where deptartment_id in (60, 210, 230)
/

As you’d expect, HR itself has the required permissions to do this.
In 18c, you may have to switch to the container in which the HR user is installed first…

alter session set container = xepdb1

…but the privileges are pretty much the same…

select privilege 
from dba_sys_privs
where grantee = 'HR'
union
select privilege
from dba_sys_privs
where grantee in ( 
    select granted_role 
    from dba_role_privs 
    where grantee = 'HR')
order by 1
/


PRIVILEGE                               
----------------------------------------
ALTER SESSION                           
CREATE CLUSTER                          
CREATE DATABASE LINK                    
CREATE INDEXTYPE                        
CREATE OPERATOR                         
CREATE PROCEDURE                        
CREATE SEQUENCE                         
CREATE SESSION                          
CREATE SYNONYM                          
CREATE TABLE                            
CREATE TRIGGER                          
CREATE TYPE                             
CREATE VIEW                             
UNLIMITED TABLESPACE                    

14 rows selected. 

Oracle’s system privileges are not really that granular. You can either have the privileges to create objects in your own schema, or you can have privileges to create them in any schema.
So, if we follow Mike’s suggestion, we’d end up with something like…

create user sledgehammer identified by nutcracker
/

grant create session,
    create any table,
    create any view,
    select any table
    to sledgehammer
/

Which would do what we need…

-- If we're using a container database, check that we're in the right one...
select sys_context('userenv', 'con_name') from dual;

SYS_CONTEXT('USERENV','CON_NAME')                                                                                                                                      
---------------------------------
XEPDB1                                                                                                                                                                 

alter session set current_schema = HR;

Session altered.

create table new_dept as select * from departments;

Table created.

create or replace view it_vw as
    select *
    from departments
    where department_id in (60, 210, 230)
/

View created.

The problem here is that it doesn’t so much ignore the principle of least privilege as walk up to it and make a rude gesture…

create table system.oops(unexpected varchar2(1));

Table created.

Fortunately, as Debbie knows, there is a better way…

The Proxy Schema

To start with, it’s probably worth taking a quick peek at the documentation for Proxy User which suggests that the only privilege such a user should be granted is CREATE SESSION.

Therefore, we can create our proxy user like this :

grant create session to app_deploy identified by debbieknowsbest
/

Next, we need to configure the HR user to allow the proxy user to connect to it :

alter user hr grant connect through app_deploy
/

Now we can connect to the HR schema using the proxy…

connect app_deploy[hr]@xepdb1
Enter password: 
Connected.
SQL> 

…which we can confirm with this query…

select sys_context('userenv', 'session_user') as "Session User",
    sys_context('userenv', 'proxy_user') as "Proxy User" 
from dual;

Session User  Proxy User
------------ ------------------------------
HR           APP_DEPLOY

Now we can run our DDL statements in the HR schema…

create table new_dept as select * from departments
/

Table created.

create or replace view it_vw as
    select *
    from departments
    where department_id in (60, 210, 230)
/

View created

If we now connect as a sufficiently privileged user, we can confirm that these objects have been created in the HR schema :

select owner, object_name, object_type
from dba_objects
where object_name in ('NEW_DEPT', 'IT_VW')
/

OWNER                          OBJECT_NAME                    OBJECT_TYPE            
------------------------------ ------------------------------ -----------------------
HR                             IT_VW                          VIEW                   
HR                             NEW_DEPT                       TABLE                  

We can now use the APP_DEPLOY schema for any code releases. All we need to do to make an application owner account accessible in this way is :

alter user <application_owner> grant connect through app_deploy

Further Reading

Debbie isn’t the only person to wear clogs of cleverness when it comes to Proxy Users.

There’s this article by Norm on the subject.
Additionally, it’s covered in the context of Schema Only accounts in this post on the Pythian site by Simon Pane.

Advertisements

1 thought on “Using a Proxy User for Code Deployment in Oracle

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.