Home » SQL & PL/SQL » SQL & PL/SQL » Synonym switching (oracle 11g release 2 )
Synonym switching [message #660443] Thu, 16 February 2017 08:49 Go to next message
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 Go to previous messageGo to next message
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 #660447 is a reply to message #660443] Thu, 16 February 2017 08:56 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

On the surface it appears to me to be a flawed design.
In an real time online OLTP application how can you ensure that the "correct" synonym always exists for existing & new sessions logging into application?

Please post actual SQL that you believe needs to occur.
Re: Synonym switching [message #660448 is a reply to message #660447] Thu, 16 February 2017 09:06 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 #660449 is a reply to message #660443] Thu, 16 February 2017 09:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
3) we are planning to created views on top synonyms in schema database
Why don't you create the views in schema_a and b and create synonyms for them in schema as you do it for tables?

Re: Synonym switching [message #660461 is a reply to message #660443] Thu, 16 February 2017 16:50 Go to previous message
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

Previous Topic: cumulative sum & product
Next Topic: Comma separate value with column value as column name
Goto Forum:
  


Current Time: Fri Mar 29 07:15:43 CDT 2024