EnterpriseDB: PERMISSIONS Oracle Tips by Burleson Consulting

Every object that is created in a database is owned by some user.  This user is called the OWNER of the object.  When a user creates an object, that user has the right to perform any action allowed by the database against that object.  For example, the owner of a table can insert and delete data from that table.  The owner of a stored procedure can run, or in database parlance, can execute that procedure.

A super user automatically has permissions to do anything in the database.  That's why it's called the super user.

A normal user can be an object owner if the user has create object privileges.  A normal user does not have permissions to use another user’s objects without being granted permission to do so by the owner or a super user.

For example, if user tom creates table "ABC", user bill cannot see or use that table.  If user tom grants insert and select permissions to user bill, user bill can insert and select data but cannot delete data.

Permissions are assigned and removed using the GRANT and REVOKE commands.  As we create objects below, I will show you how to grant and revoke permissions from those objects.   The owner of an object does not need to be granted permissions to the object.  An owner automatically has all possible permissions on an object.

If a GRANT command includes the keywords "WITH GRANT OPTION", that means that the user who has been granted authority can also grant that authority to others.   "WITH GRANT OPTION" is very powerful and should be used sparingly.  The owner of an object always has WITH GRANT OPTION.

DATABASE

We had a detailed discussion about creating databases in Chapter 2 but in the interest of completeness, I will include the syntax for creating a database here.

CREATE DATABASE
CREATE DATABASE <database name>
    [ [ WITH ] [ OWNER [=] <database user> ] 

           [ TEMPLATE [=] <pre-created database template> ]
           [ ENCODING [=] <language encoding> ]
           [ TABLESPACE [=] <tablespace name> ] ]

You can create a database named foo with a default tablespace of bar: 

CREATE DATABASE foo TABLESPACE = bar; 

You can also create a database, foo, with the default language encoding of SQL_ASCII:

CREATE DATABASE foo ENCODING = 'SQL_ASCII';

The results of creating the database look like this:

edb=# CREATE DATABASE foo ENCODING = 'SQL_ASCII';
CREATE DATABASE

If you do not receive errors, you can assume the command completed successfully.

ALTER DATABASE
ALTER DATABASE <database name>
            SET parameter { TO | = } { value | DEFAULT }
            RESET parameter
            RENAME TO <new database name>
            OWNER TO <new database owner>

Alter database is most often used to set runtime parameters.  We discussed these parameters in Chapter 2.

To set the edb_redwood_date parameter to off, we would execute:

ALTER DATABASE foo
  SET edb_redwood_date to OFF; 

To turn it back on, we would use:

ALTER DATABASE foo
  SET edb_redwood_date to ON;

The results of these commands:

edb=# ALTER DATABASE foo
edb-#   SET edb_redwood_date to OFF;
ALTER DATABASE
edb=# ALTER DATABASE foo
edb-#   SET edb_redwood_date to ON;
ALTER DATABASE
edb=#



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