Synonym switching [message #660443] |
Thu, 16 February 2017 08:49 |
|
raj85844
Messages: 26 Registered: November 2016 Location: chennai
|
Junior Member |
|
|
Hi Team,
I have big design fault as i would like to fix the same with the help of the experts out here.
we planned incorporate synonym switching.
example :
database objects
------------------------
schema_a --> Tables (batch loading)
schema_b --> Tables (online facing)
schema --> synonym/views (schema :configured database in front end application)
1) schema_a and schema_a all the objects are replicated exactly the same way
2) schema contains only synonym and views (no tables are present)
synonyms are created on top tables underlying schema_a
synonyms are dropped and recreated to point schema_b
3) we are planning to created views on top synonyms in schema database
(yes it is applicable with one condition)
that condition is -->"create any privileges"
Here is the biggest fall back raised.
as the dba refused to grant "create any privileges"
so this is now a big showstopper in built.
hence how can this handled by creating the views without "create any privileges"
cheer guys,
|
|
|
Re: Synonym switching [message #660446 is a reply to message #660443] |
Thu, 16 February 2017 08:56 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
You do not need "create any privileges" The owners of schema_a and schema_b just need to grant you the appropriate privileges on their tables to the "schema" schema. then you can easily change what schema you are pointing to by altering the local synonyms. NO non DBA user should ever get grant any privilege ever.
|
|
|
|
Re: Synonym switching [message #660448 is a reply to message #660447] |
Thu, 16 February 2017 09:06 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Create any * lets you create objects of that type in a schema other than the one you're logged in as. It doesn't do anything else
Nothing in what you've described suggests you have any need for it.
Create any does not give privs to use objects belonging to to other users.
If you create a function in another schema and try to run it you'll get insufficent privs.
To make use of objects belonging to other schemas different privs are needed and no the DBA shouldn't be giving out the blanket ones (SELECT ANY TABLE, EXECUTE ANY PROCEDURE).
As Bill said you should be granting the appropriate privs on the appropriate objects from schema_a and schema_b to schema.
|
|
|
|
Re: Synonym switching [message #660461 is a reply to message #660443] |
Thu, 16 February 2017 16:50 |
|
Caffeine+
Messages: 14 Registered: February 2017
|
Junior Member |
|
|
Can I offer an alternative method to achieve the "source table switching" automatically using the DB's build-in name resolution features? Instead of replacing synonyms on the fly you can also use the CURRENT_SCHEMA setting at the session level and then let the SQL layer do the redirection:
CREATE USER application_schema IDENTIFIED BY application_schema;
GRANT CONNECT TO application_schema;
CREATE USER schema_a IDENTIFIED BY schema_a DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
GRANT CREATE TABLE,CREATE VIEW TO schema_a;
CREATE TABLE schema_a.table_n(col1 VARCHAR2(30));
INSERT INTO schema_a.table_n(col1) VALUES ('This row from schema_a');
COMMIT;
GRANT SELECT ON schema_a.table_n TO application_schema;
CREATE USER schema_b IDENTIFIED BY schema_b DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
GRANT CREATE TABLE,CREATE VIEW TO schema_b;
CREATE TABLE schema_b.table_n(col1 VARCHAR2(30));
INSERT INTO schema_b.table_n(col1) VALUES ('This row from schema_b');
COMMIT;
GRANT SELECT ON schema_b.table_n TO application_schema;
SET ECHO ON
CONNECT application_schema/application_schema;
SELECT col1 FROM table_n;
ALTER SESSION SET CURRENT_SCHEMA=schema_a;
SELECT col1 FROM table_n;
ALTER SESSION SET CURRENT_SCHEMA=schema_b;
SELECT col1 FROM table_n;
Here's the output:
SQL> CONNECT application_schema/application_schema
Connected as APPLICATION_SCHEMA
SQL> SELECT col1 FROM table_n
>> SELECT col1 FROM table_n
*
Error at line 1
ORA-00942: table or view does not exist
SQL> ALTER SESSION SET CURRENT_SCHEMA=schema_a
Session altered.
SQL> SELECT col1 FROM table_n
COL1
------------------------------
This row from schema_a
1 row selected.
SQL> ALTER SESSION SET CURRENT_SCHEMA=schema_b
Session altered.
SQL> SELECT col1 FROM table_n
COL1
------------------------------
This row from schema_b
1 row selected.
}
You didn't say what event triggers the action to redirect the table's source schema so I'll assume you have some control over the application. If you do then you can have it issue the ALTER SESSION command directly from the code.
If you don't have acceptable methods to trigger the app to change the current schema you can use a combination of built-in features to do the redirection. The application schema (or simply "schema" in your scenario) can create a UNION ALL view where the subquery blocks are mutually exclusive. I see the mutually-exclusive view method a lot in applications that accept a variable number of query parameters.
First create a global context to hold the currently active schema so that it's accessible by all sessions. The context is managed by a PL/SQL package so it needs to be created in advance. You'll need to decide a schema that will own it but in this example I'll leave it out:
CREATE OR REPLACE PACKAGE signal_schema_switch_pkg
AS
PROCEDURE publish_active_schema(p_schema_to_activate IN VARCHAR2);
END;
/
CREATE OR REPLACE CONTEXT active_schema_ctx USING signal_schema_switch_pkg ACCESSED GLOBALLY;
CREATE OR REPLACE PACKAGE BODY signal_schema_switch_pkg
AS
PROCEDURE publish_active_schema(p_schema_to_activate IN VARCHAR2)
IS
BEGIN
DBMS_SESSION.SET_CONTEXT ('active_schema_ctx', 'active_schema', p_schema_to_activate);
END;
END;
/
The application schema will own a view that unions the "table_n" together, with filtering:
GRANT CREATE VIEW TO application_schema;
CREATE OR REPLACE VIEW application_schema.table_n
AS
SELECT col1 FROM schema_a.table_n
WHERE SYS_CONTEXT('active_schema_ctx','active_schema') = 'schema_a'
UNION ALL
SELECT col1 FROM schema_b.table_n
WHERE SYS_CONTEXT('active_schema_ctx','active_schema') = 'schema_b'
/
At this point nothing would be returned by the query because the global context value has not been set:
SQL> SELECT * FROM application_schema.table_n
no rows selected.
The redirection can now be done dynamically by setting the global context:
SQL> BEGIN
signal_schema_switch_pkg.publish_active_schema(p_schema_to_activate=>'schema_a');
END;
PL/SQL procedure successfully completed.
SQL> SELECT * FROM application_schema.table_n
COL1
------------------------------
This row from schema_a
1 row selected.
SQL> BEGIN
signal_schema_switch_pkg.publish_active_schema(p_schema_to_activate=>'schema_b');
END;
PL/SQL procedure successfully completed.
SQL> SELECT * FROM application_schema.table_n
COL1
------------------------------
This row from schema_b
1 row selected.
The run time execution plan shows that the "false" branch of the view was not executed. The Starts column is zero and is based on the run time filter predicates:
SQL_ID 410zwxv93wscc, child number 0
-------------------------------------
SELECT * FROM application_schema.table_n
Plan hash value: 2618782992
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 1 |00:00:00.01 | 7 | | | |
| 1 | VIEW | TABLE_N | 1 | 2 | 34 | 6 (0)| 00:00:01 | 1 |00:00:00.01 | 7 | | | |
| 2 | UNION-ALL | | 1 | | | | | 1 |00:00:00.01 | 7 | | | |
|* 3 | FILTER | | 1 | | | | | 1 |00:00:00.01 | 7 | | | |
| 4 | TABLE ACCESS STORAGE FULL| TABLE_N | 1 | 1 | 17 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 7 | 1025K| 1025K| |
|* 5 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | | | |
| 6 | TABLE ACCESS STORAGE FULL| TABLE_N | 0 | 1 | 17 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Note that this method has side-effects (nothing is ever free, huh) and will impact the SQL parse rates. But that's a trade-off that your DBAs will need to weigh against the DB instance's rate of SQL parsing.
HTH
|
|
|