Oracle Tips by Burleson Consulting
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.
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>
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;
synonyms EMPLOYEE and EMP both
to the same object, HR.EMP.
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
CREATE OR REPLACE PUBLIC SYNONYM hr_employee FOR
ALTER PUBLIC SYNONYM
cannot alter a synonym. You must drop it and recreate it.
GRANT PUBLIC SYNONYM
cannot grant directly to a synonym. You must grant permissions to
the underlying object.
REVOKE PUBLIC SYNONYM
cannot revoke permissions from a synonym. You must revoke
permissions from the underlying object.
DROP PUBLIC SYNONYM
can drop a public synonym with the DROP PUBLIC SYNONM command.
DROP PUBLIC SYNONYM hr_employee;
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.
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
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 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
USER <role name(s)>
VALID UNTIL '<timestamp>'
create a basic role with no grants or objects:
CREATE ROLE basic_role;
create a role that is also a user and can login to the database:
CREATE ROLE basic_role LOGIN;
is the equivalent of:
CREATE USER basic_role;
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.
also has the Remote DBA special role. In Oracle this special role type has
abilities predefined. EnterpriseDB has two somewhat equivalent
special role types.
CREATEDB type has permissions to drop and create databases.
CREATE ROLE Remote DBA_type_role CREATEDB;
CREATEROLE type has the ability to create, alter, drop and assign
roles to others.
CREATE ROLE Remote DBA_type2 CREATEROLE;
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;
can alter a role and rename it:
ALTER ROLE Remote DBA_type RENAME TO Remote DBA_sort_of_type;
can change its attributes:
ALTER ROLE Remote DBA_type NOLOGIN;
can grant a role to someone:
GRANT Remote DBA_type TO jschmoe;
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.
can revoke from a role:
REVOKE SELECT ON emp FROM Remote DBA_type;
jschmoe can no longer select from emp.
can revoke a role from someone:
REVOKE Remote DBA_type FROM jschmoe;
jschmoe no longer has the Remote DBA_type role.
can drop a role with the DROP ROLE command.
DROP ROLE Remote DBA_type;
cannot drop a role that owns objects. You must drop all of the
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.