Oracle_Privileges1.hm
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 OBJECT privileges.

Database Users:

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 roles,
    • 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 DB
    • 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 revering db.  
  • Resources: 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.



 
Reference: a58397/ch21.htm , ch19.htm, a96524/c24privs.htm
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 documents, (System_Privileges.htm). 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 privileges.

Users with explicit object privileges and the SYSDBA can access dictionary objects.

Below is the properties of the user sys : Roles, and System Privileges

The user "Scott" has fewer roles but has system privileges
Role Name :

DBA :

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 users. 
    • 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 files
  • Data Security (preferences) : eg. May use select or insert but not allowed to use Delete statement.
Creating Role:

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;
INSERT Privileges:
     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 from them:

  • Users who were granted a specific system privilege with the ADMIN OPTION

  • Users with the system privilege GRANT ANY PRIVILEGE

For this reason, only grant these privileges to trusted users.