CURRENT_SCHEMA
ApproachCURRENT_SCHEMA
session attribute to automatically point application users to the correct schema.Notice that the application user can connect, but does not have any tablespace quotas or privileges to create objects.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 passwordDEFAULT TABLESPACE usersTEMPORARY TABLESPACE tempQUOTA UNLIMITED ON users;GRANT CONNECT, CREATE TABLE TO schema_owner;-- Application user.CREATE USER app_user IDENTIFIED BY passwordDEFAULT TABLESPACE usersTEMPORARY TABLESPACE temp;GRANT CONNECT TO app_user;
We want to give our application user read-write access to the schema objects, so we grant the relevant role.CREATE ROLE schema_rw_role;CREATE ROLE schema_ro_role;
We need to make sure the application user has its default schemapointing to the schema owner, so we create an AFTER LOGON trigger to dothis for us.GRANT schema_rw_role TO app_user;
Now we are ready to create an object in the schema owner.CREATE OR REPLACE TRIGGER app_user.after_logon_trgAFTER LOGON ON app_user.SCHEMABEGINDBMS_APPLICATION_INFO.set_module(USER, ‘Initialized‘);EXECUTE IMMEDIATE ‘ALTER SESSION SET current_schema=SCHEMA_OWNER‘;END;/
Notice how the privileges are granted to the relevant roles. Withoutthis, the objects would not be visible to the application user. We nowhave a functioning schema owner and application user.CONN schema_owner/passwordCREATE 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;
This method is ideal where the application user is simply analternative entry point to the main schema, requiring no objects of itsown. It is clean and doesn‘t require management of thousands ofsynonyms. I don‘t find it very useful for developers who need to makecopies or modify schema objects during development.SQL> CONN app_user/passwordConnected.SQL> DESC test_tabName Null? Type----------------------------------------------------- -------- ------------------------------------ID NOT NULL NUMBERDESCRIPTION VARCHAR2(50)SQL>
Once again, the application user can connect, but does not have anytablespace quotas. The difference here is that the application userdoes have the privilege to create synonyms.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 passwordDEFAULT TABLESPACE usersTEMPORARY TABLESPACE tempQUOTA UNLIMITED ON users;GRANT CONNECT, CREATE TABLE TO schema_owner;-- Application user.CREATE USER app_user IDENTIFIED BY passwordDEFAULT TABLESPACE usersTEMPORARY TABLESPACE temp;GRANT CONNECT, CREATE SYNONYM TO app_user;
Now we are ready to create an object in the schema owner in the same way we did in the previous example.CREATE ROLE schema_rw_role;CREATE ROLE schema_ro_role;GRANT schema_rw_role TO app_user;
If we now connect to the application user we are not able to see theobject without qualifying it with a schema name. We can either proceedin this fashion, or use a synonym to point to the correct object.CONN schema_owner/passwordCREATE 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;
I find this method rather cumbersome due to the sheer number ofsynonyms required, especially when there are a large number ofapplication users. Obviously, it is possible to use public synonyms,but this can be problematic when you have multiple application schemason a single instance. I only use this method when I have developers whoneed to create their own schema objects for testing.SQL> CONN app_user/passwordConnected.SQL> DESC test_tabERROR:ORA-04043: object test_tab does not existSQL> DESC schema_owner.test_tabName Null? Type----------------------------------------------------- -------- ------------------------------------ID NOT NULL NUMBERDESCRIPTION VARCHAR2(50)SQL> CREATE SYNONYM test_tab FOR schema_owner.test_tab;Synonym created.SQL> DESC test_tabName Null? Type----------------------------------------------------- -------- ------------------------------------ID NOT NULL NUMBERDESCRIPTION VARCHAR2(50)SQL>
联系客服