BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

EnterpriseDB: SYNONYM

Oracle Tips by Burleson Consulting
 

A synonym is a text pointer from one object to another object.  It is simply a way to refer to an object by using a name that is not its name.  Oracle provides private and public synonyms.  EnterpriseDB provides public synonyms only.   Due to the disconnect between users and schemas (remember that in Oracle a user is a schema), private synonyms don't make much sense in EnterpriseDB.

Public synonyms create a common namespace in the database to refer to objects.  Since it is a common namespace, each public synonym must be unique.  That means you can have two synonyms pointing to hr.emp but you cannot have two synonyms named emp.

CREATE PUBLIC SYNONYM
CREATE OR REPLACE PUBLIC SYNONYM <synonym name>
FOR <object name>

For example, if you create a table, call it emp, in the HR schema, you refer to it as HR.emp.  If you don't want to refer to the schema, you can create a synonym and name it anything you like.

CREATE OR REPLACE PUBLIC SYNONYM employee FOR hr.emp;

CREATE OR REPLACE PUBLIC SYNONYM emp FOR hr.emp;

The synonyms EMPLOYEE and EMP both point to the same object, HR.EMP.

You can also create a synonym for a sequence or a view.

CREATE SEQUENCE seq1;

CREATE OR REPLACE PUBLIC SYNONYM my_seq FOR seq1;

CREATE VIEW hr_emp AS
  SELECT *
     FROM emp;

CREATE OR REPLACE PUBLIC SYNONYM hr_employee FOR hr_emp;

ALTER PUBLIC SYNONYM

You cannot alter a synonym.  You must drop it and recreate it.

GRANT PUBLIC SYNONYM

You cannot grant directly to a synonym.  You must grant permissions to the underlying object.

REVOKE PUBLIC SYNONYM

You cannot revoke permissions from a synonym.  You must revoke permissions from the underlying object.

DROP PUBLIC SYNONYM

You can drop a public synonym with the DROP PUBLIC SYNONM command.

DROP PUBLIC SYNONYM hr_employee;

Roles

In Oracle, a role is a group.  The group can be granted object permissions but cannot own objects.  Users are assigned roles (which puts them in a group).  By being assigned to a group, the user inherits the object permissions granted to that role.

In EnterpriseDB, roles are a bit different.  Users are implemented as roles.  That means that roles and users are in essence the same thing.  A role can own objects as well as be granted object permissions.

Roles, in the Oracle sense, are nice in that you can have finer granularity of permissions.  You can have a user role and a superuser role.  The user role may only be able to select from some tables but the superuser can add and delete records.  You can grant the user role to most users and superuser to only administrators.  If an administrator changes jobs, you can revoke the superuser role (that means take it away from that user) and assign the person the user role.  If someone quits, you revoke his or her roles and you don't need to worry about individual permissions.

CREATE ROLE

CREATE ROLE name
      ADMIN <role name(s)>
      CONNECTION LIMIT <conn limit>
      CREATEDB | NOCREATEDB
      CREATEROLE | NOCREATEROLE
      CREATEUSER | NOCREATEUSER
      INHERIT | NOINHERIT
      IN ROLE <role name(s)>
      IN GROUP <role name(s)>
      LOGIN | NOLOGIN

    
  [ ENCRYPTED | UNENCRYPTED ] PASSWORD '<password>'
      ROLE <role name(s)>
      SUPERUSER | NOSUPERUSER
      SYSID uid
      USER <role name(s)>
      VALID UNTIL '<timestamp>'

To create a basic role with no grants or objects:

CREATE ROLE basic_role;

To create a role that is also a user and can login to the database:

CREATE ROLE basic_role LOGIN;

This is the equivalent of:

CREATE USER basic_role;

Oracle has a special user type called the SYSRemote DBA.  This is a user (normally SYS) that is exempt from most permission checks.  EnterpriseDB has a role that can be assigned to someone that gives much the same permissions as a SYS user.  This special user is called a SUPERUSER and is exempt from all permission checks.

CREATE ROLE not_basic_anymore SUPERUSER;

Oracle also has the Remote DBA special role.  In Oracle this special role type has abilities predefined.  EnterpriseDB has two somewhat equivalent special role types.

The CREATEDB type has permissions to drop and create databases.

CREATE ROLE Remote DBA_type_role CREATEDB;

The CREATEROLE type has the ability to create, alter, drop and assign roles to others.

CREATE ROLE Remote DBA_type2 CREATEROLE;

The final special case we will talk about are secure roles.  A secure role is one that has a password.  Only the person or application that knows the password can use the permissions granted to this role.  The password passed into the database will also be passed to this role.  This is a somewhat limited value special case.

CREATE ROLE basic_role_wih_pwd PASSWORD 'secret_password';

Special cases can be combined into a single role.  For example, it is possible to create a Remote DBA type without resorting to the SUPERUSER:

CREATE ROLE Remote DBA_type CREATEDB CREATEROLE LOGIN;

ALTER ROLE

You can alter a role and rename it:

ALTER ROLE Remote DBA_type RENAME TO Remote DBA_sort_of_type;

Or you can change its attributes:

ALTER ROLE Remote DBA_type NOLOGIN;

GRANT ROLE

You can grant a role to someone:

GRANT Remote DBA_type TO jschmoe;

You can grant to a role:

GRANT SELECT, INSERT ON emp TO Remote DBA_type;

jschmoe can now select from emp and add new records to emp.

REVOKE ROLE

You can revoke from a role:

REVOKE SELECT ON emp FROM Remote DBA_type;

jschmoe can no longer select from emp.

You can revoke a role from someone:

REVOKE Remote DBA_type FROM jschmoe;

jschmoe no longer has the Remote DBA_type role.

DROP ROLE

You can drop a role with the DROP ROLE command.

DROP ROLE Remote DBA_type;

You cannot drop a role that owns objects.  You must drop all of the objects first.




This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright 1996 -  2013 by Burleson. All rights reserved.

Oracle is the registered trademark of Oracle Corporation.



Hit Counter