CURRENT_SCHEMA Approach



This method uses the CURRENT_SCHEMA session attribute to automatically point application users to the correct schema.

First, we create the schema owner and an application user.


CONN sys/password AS SYSDBA

-- Remove existing users and roles with the same names.


DROP USER schema_owner CASCADE;
DROP USER app_user CASCADE;
DROP ROLE schema_rw_role;
DROP ROLE schema_ro_role;

-- Schema owner.


CREATE USER schema_owner IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;

GRANT CONNECT, CREATE TABLE TO schema_owner;

-- Application user.


CREATE USER app_user IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;

GRANT CONNECT TO app_user;


Notice that the application user can connect, but does not have any tablespace quotas or privileges to create objects.

Next, we create some roles to allow read-write and read-only access.


CREATE ROLE schema_rw_role;
CREATE ROLE schema_ro_role;
We want to give our application user read-write access to the schema objects, so we grant the relevant role.
GRANT schema_rw_role TO app_user;


We need to make sure the application user has its default schema pointing to the schema owner, so we create an AFTER LOGON trigger to do this for us.


CREATE OR REPLACE TRIGGER app_user.after_logon_trg
AFTER LOGON ON app_user.SCHEMA
BEGIN
DBMS_APPLICATION_INFO.set_module(USER, 'Initialized');
EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema=SCHEMA_OWNER';
END;
/


Now we are ready to create an object in the schema owner.
CONN schema_owner/password

CREATE TABLE test_tab (
id NUMBER,
description VARCHAR2(50),
CONSTRAINT test_tab_pk PRIMARY KEY (id)
);

GRANT SELECT ON test_tab TO schema_ro_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON test_tab TO schema_rw_role;


Notice how the privileges are granted to the relevant roles. Without this, the objects would not be visible to the application user. We now have a functioning schema owner and application user.


CONN app_user/password
Connected.
DESC test_tab
Name Null? Type
ID NOT NULL NUMBER
DESCRIPTION VARCHAR2(50)


This method is ideal where the application user is simply an alternative entry point to the main schema, requiring no objects of its own. It is clean and doesn't require management of thousands of synonyms. I don't find it very useful for developers who need to make copies or modify schema objects during development.

Comments

Popular posts from this blog

SQL Interview Questions

Manage Your Database Connection

Number of Days in Current Month