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.