1.Two Types of Oracle user privileges

1
2
3
4
5
6
7
8

. System privilege:Enables users to perform particular actions in the database
. Object:Enables users to access and manipulate a specific object
. There is no CREATE INDEX privilege.
. CREATE TABLE includes the CREATE INDEX and the ANALYZE commands. The user
  must have a quota for the tablespace or must have been granted UNLIMITE TABLESPACE.
. Privileges such as CREATE TABLE, CREATE PROCEDURE, or CREATE CLUSTER include the dropping of these objects.
. UNLIMITED TABLESPACE cannot be granted to a role.
. For truncating a table in another schema, the DROP ANY TABLE privilege is necessary.

2.Granting System Privileges

1
2
3
4
5

GRANT {system_privilege|role}
[, {system_privilege|role} ]...
TO {user|role|PUBLIC}
[, {user|role|PUBLIC} ]...
[WITH ADMIN OPTION]

The grantee can further grant the system privilege to other users with the ADMIN option.

3.SYSDBA and SYSOPER Privileges

Catetory|Examples
----------------------------------------------------
SYSOPER |Examples
----------------------------------------------------
        | STARTUP
        | SHUTDOWN
        | ALTER DATABASE OPEN | MOUNT
        | ALTER DATABASE BACKUP CONTROLFILE TO
        | RECOVER DATABASE
        | ALTER DATABASE ARCHIVELOG
 
SYSDBA  |
----------------------------------------------------
        | SYSOPER PRIVILEGES WITH ADMIN OPTION
        | CREATE DATABASE
        | ALTER DATABASE BEGIN/END BACKUP
        | RESTRICTED SESSION
        | RECOVER DATABASE UNTILL
----------------------------------------------------

4.System Privilege Restrictions
O7_DICTIONARY_ACCESSIBILITY parameter
If set to TRUE,access to objects in SYS schema is allowed.Default is FALSE
Ensures that system privileges that allow access to any scheman do not allowed access to SYS schema.

5.Revoking System privileges

System privileges can be revoked using the SQL statement REVOKE. Any user with the ADMIN
OPTION for a system privilege can revoke the privilege from any other database user.

Syntax

1
2
3
4

REVOKE {system_privilege|role}
[, {system_privilege|role} ]...
FROM {user|role|PUBLIC}
[, {user|role|PUBLIC} ]...

Note:
. The REVOKE command can only revoke privileges that have been granted directly with a GRANT command.
. Revoking system privileges may have an effect on some dependent objects. For example, if
SELECT ANY TABLE is granted to a user, and that user has created any procedures or
views that use a table in some other schema, revoking the privilege invalidates the procedures or views.
. There are no cascading effects when a system privilege is revoked, regardless of whether it was given WITH ADMIN OPTION.

6.Object Privileges
A grant on a synonym is converted to a grant upon the base table referenced by the synonym

1
2
3
4
5
6

GRANT { object_privilege [(column_list)]
[, object_privilege [(column_list)] ]...
|ALL [PRIVILEGES]}
ON [schema.]object
TO {user|role|PUBLIC}[, {user|role|PUBLIC} ]...
[WITH GRANT OPTION]

where:

column_list: specifies a table or view column (This can be specified only when granting the INSERT, REFERENCES, or UPDATE privileges.)
ALL: grants all privileges for the object that have been granted WITH GRANT OPTION ON object identifies the object on 
which the privileges are to be granted
WITH GRANT OPTION: enables the grantee to grant the object privileges to other users or roles

7.Revoking Object Privileges
The REVOKE statement is used to revoke object privileges. To revoke an object privilege, the
revoker must be the original grantor of the object privilege being revoked.
Use the following command to revoke an object privilege:

1
2
3
4
5
6
7

REVOKE { object_privilege
[, object_privilege ]...
| ALL [PRIVILEGES] }
ON [schema.]object
FROM {user|role|PUBLIC}
[, {user|role|PUBLIC} ]...
[CASCADE CONSTRAINTS]

CASCADE CONSTRAINTS
drops any referential integrity constraints that the revoke has defined using REFERENCES or ALL privileges

8.Obtaining Privileges Information
-DBA_SYS_PRIVS
-SESSION_PRIVS
-DBA_TAB_PRIVS
-DBA_COL_PRIVS

9.Auditing
1.Protecting the Audit Trail
You should protect the audit trail so that the audit information cannot be added,modified, or delete.
Issue the command:

AUDIT DELETE ON sys.aud$ BY ACCESS;

To protect the audit trail from unauthorized deletions ,only the DBA should have the DELETE_CATALOG_ROLE role.
To move AUD$ to the AUDIT_TAB tablespce:
. Ensure that auditing is currently disabled.
. Ensure the following command:

    ALTER TABLE aud$ MOVE TABLESPACE AUDIT_TAB;

.Enter the following command:

     CREATE INDEX i_aud1 ON aud$(sessionid,ses$tid)
     TABLESPACE AUDIT_IDX;

.Enable auditing for the instance.

2.Enabling and Disabling Database Auditing
AUDIT_TRAIL = value
where value can be one of the following:
DB enables auditing and directs all audit records to the database audit trail (SYS.AUD$)
OS enables auditing and directs all audit records to the operating system audit trail (if permitted on the operating system)
NONE disables auditing (this is the default value)

3.Viewing Auditing Options
-ALL_DEF_AUDIT_OPTS :Default audit options
-DBA_STMT_AUDIT_OPTS:Statement audting options
-DBA_PRIV_AUDIT_OPTS:Privilege auditing options
-DBA_OBJ_AUDIT_OPTS:Schema object auditing options

4.Obtaining Audit Records
-DBA_AUDIT_TRAIL:All audit trail entries
-DBA_AUDIT_EXISTS:Records for AUDIT EXISTS/NOT EXISTS
-DBA_AUDIT_OBJECT:Records concerning schema objects
-DBA_AUDIT_SESSION:All connect and disconnect entries
-DBA_AUDIT_STATEMENT:Statement auditing records

Popularity: 26% [?]