|Objectives : In Oracle RDBMS privilege is a right, tendered to an
user or to a role, to execute an SQL statement or to access another
user's object. There are two types of privileges, namely, SYSTEM and
To manage the db, by default oracle installer would introduce some
users, depending on the flavor of oracle db, like sys, sysdba, system,
sysman, DBSNP; these users accounts would have DBA admin privileges.
The terms Oracle Users, database user, Oracle account and Oracle
owner are synonymous.
As required DBA would would perform the following tasks:
- Server Hardware and Software
- Create, Open and maintenances of DB
- Backing up DB
- System Operation and securities, password managements .
- Allocating users privileges, assigning roles to the users and
table spaces ( hardware spaces)
- Patching and tuning up the DB
- Up-scaling or moving to new versions etc.
|The account , or user "SYS" : dba_col_privs;
|Database Role and Responsibilities:
A database role is a set of privileges, usually created as a template,
which can be awarded to the data base users. Below is an example,
showing the role of an user "SYS", and showed a total of 401
distinct privileges ( Oracle 11.2g Community download).
- DBA : Admin
- Creating users and granting privileges to the users
- Creating roles and grating to the users
- managing schema objects
- does not have the privileges to start and shut down the DB
- Users like SYS, SYSTEM by default granted to open and close
- SYS has SYSDBA privilege, and performs backup and recovery
of the db
- SYSDSBA : has full admin power.
- SYSOPER : can perform all operation, but can't
access the users data.
- SYSTEM can perform all SYS's operations except backing up or
Authorizes an user to create, modify and delete certain schema as an
owner of the object. It is endowed with CREATE CLUSTER, CREATE
INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE,
CREATE TABLE, CREATE TRIGGER, CREATE TYPE.
- Connect : It is a
basic role created when an admin creates an user, allowing the user
to access the DB, but can't modify or create schema, unless the user
acquirers "RESOURCE" privilege from the admins. ( please review this
article " Oracle_Privileges1.htm").
|A privilege is a right to execute a particular type of SQL
statement or to access to an object belongs to another user. Each
user has allocated one schema, or each schema has a designated user.
|Data Base Privileges: Database privileges can be divided into
two groups, System privileges and object privileges' 80;
- System privileges :
There were 46 distinct system privileges listed in Oracle 11.2g
Users with ANY key word can't access dictionary objects. FYI, Oracle
version 6 had only three (CONNECT, RESOURCE, DBA) PRIVILEGES.
- The user who can grant
or revoke system privileges. "Only the instance administrator
or a user with ADMIN privilege can grant or revoke system
privileges" (Oracle 11.2 release quotes").
- users with "ADMIN OPTION"
- user with "GRANT ANY PRIVILEGE"
- users with "GRANT ANY OBJECT PRIVILEGE"
- Object privileges :
The operations, DELETE, EXECUTE, FLUSH, INDEX, INSERT, LOAD ,
REFERENCES , REFRESH , SELECT ,
UNLOAD , UPDATE, are all object
Users with explicit object privileges and the SYSDBA can access
Below is the properties of the user sys : Roles, and System
The user "Scott" has fewer roles
but has system privileges
|Role Name :
|Database User Management:
- Small or Large!!
- Small : the security administrator may be the only user with
the privileges required to create, alter, or drop database
- Large: may be a number of administrators with privileges to
manage database users.
- User Authentication: Database users can be authenticated
(verified as the correct person) by Oracle using the host operating
system, network services, or the database.
- OS security: admin should have privileges to create or delete
- Data Security (preferences)
: eg. May use select or insert but
not allowed to use Delete statement.
Must have an unique name.
You can create a role using either the SQL command CREATE ROLE, or
the Create Role property sheet of Enterprise Manager.
The user like "security administrator" has the CREATE ROLE system
privilege to create a role.
Granting Object Privileges :
GRANT select, insert, delete ON emp TO jfee, tsmith;
GRANT insert(ename, job) ON emp TO jfee, tsmith;
Granting ALL object privileges:
GRANT ALL ON salary TO jfee;
Granting INSERT privileges: to a column
GRANT INSERT (acct_no)ON accounts TO scott;
Who Can Grant or Revoke System Privileges?
Only two types of users can
grant system privileges to other users or revoke those privileges
For this reason, only grant these privileges to trusted users.