Security - Oracle SQL Jumpstart with Examples - page 516

Now let's connect to PRINCE and experiment with his privileges.

CONNECT PRINCE/CHARMING@OLTP;

Let's create a small table owned by PRINCE.

CREATE TABLE MYHORSES (NAME VARCHAR2(30) PRIMARY KEY
, STALL NUMBER);

Now grant the object privilege to ARIEL, for querying this new table.

GRANT SELECT ON MYHORSES TO ARIEL;

SQL*Plus will reply with, “Grant succeeded.” The owner of a table,
PRINCE, can grant object privileges on that table.
Additionally, PRINCE has WITH ADMIN OPTION on the CREATE
TABLE and CREATE VIEW system privileges, so he can grant them to
others. The next command will allow PRINCE to grant the ability to create
views to ARIEL.

GRANT CREATE VIEW TO ARIEL;

Let's take a small step back for a moment and look briefly at system and
object privileges. There are a multitude of system privileges. For a complete
list, see Oracle documentation. As far as Oracle SQL is concerned, you do

Table 23.1 System Privileges

System Privilege Description

CREATE ANY TABLE

Create a table in any user's schema.

CREATE TABLE

Create a table in your own schema only.

CREATE USER

Create a new Oracle user.

ALTER DATABASE

Modify database settings with the ALTER DATA-
BASE command.

CREATE ANY INDEX

Create an index on a table in any schema.