Databases Reference
In-Depth Information
• DELETE to remove rows from tables, table partitions, or views
In addition to these data-specific privileges, several other privileges apply to the objects
within a database schema, such as:
• CREATE to create a table in a schema
• DROP to remove a table in a schema
• ALTER to alter tables or views
All of these privileges can be handled with two simple SQL commands. The
GRANT
command gives a particular privilege to a user or role, while the
REVOKE
command takes
away a specific privilege. You can use
GRANT
and
REVOKE
to modify the privileges for an
individual or a role. You can also grant the ability to re-grant privileges to others. You
can use either of these commands with the keyword PUBLIC to issue or revoke a priv‐
ilege for all database users.
Another security privilege, EXECUTE, allows users to run a PL/SQL procedure or
function. By default, the PL/SQL routine runs with the security privileges of the user
who compiled the routine. Alternately, you can specify that a PL/SQL routine run with
what is termed
invoker's rights
, which means that the routine is run with the security
privileges of the user who is invoking the routine.
Special Roles: DBA, SYSDBA, and SYSOPER
Your Oracle Database comes with three special roles, which have been defined for a
while, and more roles added in the last releases, such as
sysasm
in Oracle Database
11
g
and
sysbackup
in Oracle Database 12
c
. The DBA role is one of the most important
default roles in Oracle. The DBA role includes most system privileges. By default, it is
granted to the users SYS and SYSTEM, both created at database creation time. Base
tables and data dictionary views are stored in the SYS schema. SYSTEM schema tables
are used for administrative information and by various Oracle tools and options. A
number of other administrative users also exist, as consistent with the specific Oracle
features deployed.
The DBA role does not include basic database administrative tasks included in the
SYSDBA or SYSOPER system privileges. Therefore, SYSDBA or SYSOPER should be
specifically granted to administrators. They will “CONNECT AS” either SYSDBA or
SYSOPER to the database and will have access to a database even when it is not open.
SYSDBA privileges can be granted to users by SYS or by other administrators with
SYSDBA privileges. When granted, the SYSDBA privileges allow a user to perform the
following database actions from the command line of SQL*Plus or by logging into Ora‐
cle Enterprise Manager's point-and-click interface:
