ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Note: Large OLTP systems where users log in to the database as their own user ID can benefit from explicitly qualifying the segment owner,
rather than using public synonyms. This significantly reduces the number of entries in the dictionary cache.
Data Dictionary Objects
ALL_SYNONYMS
DBA_SYNONYMS
SYNONYMS
CDB_SYNONYMS
SYN$
USER_SYNONYMS
Related System Privileges
CREATE ANY SYNONYM
CREATE SYNONYM
DROP PUBLIC SYNONYM
CREATE PUBLIC SYNONYM
DROP ANY SYNONYM
GRANT create synonym TO uwclass;
REVOKE create synonym FROM uwclass;
Table For Create Synonym Demos
CREATE TABLE reallyobnoxiouslynamedtable (
test VARCHAR2(20));
Create Synonym
Create Private Synonym
CREATE OR REPLACE <EDITIONABLE | NONEDITIONABLE> SYNONYM
[IF NOT EXISTS <synonym_name>]
[SHARING = <METADATA | NONE>]
FOR <object_name> [@ <dblink_name>];
desc reallyobnoxiouslynamedtable
SELECT *
FROM reallyobnoxiouslynamedtable;
CREATE OR REPLACE SYNONYM simple FOR reallyobnoxiouslynamedtable;
desc user_synonyms
col synonym_name format a30
col table_owner format a30
col table_name format a30
SELECT synonym_name, table_owner, table_name
FROM user_synonyms;
INSERT INTO reallyobnoxiouslynamedtable VALUES ('ABC');
INSERT INTO simple VALUES ('DEF');
SELECT * FROM reallyobnoxiouslynamedtable;
SELECT * FROM simple;
Create Public Synonym
CREATE OR REPLACE <EDITIONABLE | NONEDITIONABLE> PUBLIC SYNONYM
[IF NOT EXISTS <synonym_name>]
[SHARING = <METADATA | NONE>]
FOR <object_name> [@ <dblink_name>];
CREATE OR REPLACE PUBLIC SYNONYM alltab FOR all_tables;
desc alltab
SELECT COUNT(*)
FROM alltab;
Create A Synonym For An Object Owned By A Different Schema
CREATE OR REPLACE <EDITIONABLE | NONEDITIONABLE> [PUBLIC] SYNONYM
[IF NOT EXISTS <schema_name.>] <synonym_name>]
[SHARING = <METADATA | NONE>]
FOR [<schema_name>.]<object_name> [@ <dblink_name>];
conn uwclass/uwclass@pdbdev
SELECT * FROM v_$session;
SELECT * FROM sys.v_$session;
-- as SYS grant SELECT privilege to schema
conn / as sysdba@pdbdev
GRANT SELECT ON v_$session TO uwclass;
-- log back on as user
conn uwclass/uwclass
SELECT * FROM v_$session;
SELECT * FROM sys.v_$session;
CREATE OR REPLACE SYNONYM sess FOR sys.v_$sessions;
SELECT * FROM sess;
Understanding the TABLE_NAME column of xxx_synonyms
CREATE VIEW test_view AS
SELECT SUBSTR(object_name,1,30) objname , object_type
FROM all_objects;
CREATE OR REPLACE FUNCTION test_func RETURN INTEGER IS
BEGIN
RETURN 1;
END test_func;
/
CREATE OR REPLACE SYNONYM a FOR servers;
CREATE OR REPLACE SYNONYM b FOR test_view;
CREATE OR REPLACE SYNONYM c FOR test_func;
desc a
desc b
desc c
set linesize 121
SELECT s.synonym_name, s.table_name, o.object_type
FROM user_synonyms s, user_objects o
WHERE s.table_name = o.object_name;
Total System Global Area 2550136832 bytes
Fixed Size 3048872 bytes
Variable Size 671091288 bytes
Database Buffers 1862270976 bytes
Redo Buffers 13725696 bytes
Database mounted.
Database opened.
SQL> CREATE SCHEMA SYNONYM dvault FOR dvsys;
*
ERROR at line 1:
ORA-65049: creation of local user or role is not allowed in CDB$ROOT
SQL> alter pluggable database pdbdev open;
Pluggable database altered.
SQL> conn sys@pdbdev as sysdba
Enter password:
Connected.
SQL> CREATE SCHEMA SYNONYM dev FOR uwclass;
Schema synonym created.
SQL> SELECT COUNT(*) FROM dev.servers;
COUNT(*)
----------
141
SQL> DROP synonym dev;
*
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist
SQL> DROP SCHEMA SYNONYM dev;
Schema synonym dropped.
Alter Synonym
Recompile Synonym
ALTER SYNONYM <synonym_name> COMPILE;
CREATE SYNONYM test_syn FOR servers;
ALTER SYNONYM test_syn COMPILE;
Rename Synonym
RENAME SYNONYM <current_name> TO <new_name>;
RENAME SYNONYM test_syn TO syn_test;
SELECT synonym_name
FROM user_synonyms;
Drop Synonym
Drop Private Synonym
DROP SYNONYM <synonym_name> [FORCE];
DROP SYNONYM simple;
Drop Public Synonym
DROP PUBLIC SYNONYM <synonym_name> [FORCE];
DROP PUBLIC SYNONYM alltab FORCE;
Demos
Drop All Synonyms
conn uwclass/uwclass@pdbdev
SELECT synonym_name
FROM user_synonyms;
CREATE OR REPLACE PROCEDURE dropsyn AUTHID DEFINER IS
CURSOR s_cur IS
SELECT synonym_name
FROM user_synonyms;
RetVal NUMBER;
sqlstr VARCHAR2(200);
BEGIN
FOR s_rec IN s_cur LOOP
sqlstr := 'DROP SYNONYM ' || s_rec.synonym_name;
EXECUTE IMMEDIATE sqlstr;
END LOOP;
END dropsyn;
/
SELECT synonym_name
FROM user_synonyms;
Create Private Synonyms
SPOOL c:\temp\csyns.txt
SELECT 'create or replace synonym ' || object_name ||
' FOR ' || user || '.' || object_name ||';'
FROM user_objects
WHERE object_type <> 'SYNONYM'
AND object_type NOT LIKE '%BODY';
