ORACLE_基础十四(Privileges Auditing)

Two  types of Oracle user privileges:

1.System: Enable users to perform particular actions in the dtabase

2.Object : Enables users to access and manipulate a specific object

System privileges

1.More than 100 distinct system privileges

2. ANY keyword in privileges signifies that users have the privilege in any schema

3. GRANT command adds a privilege to a user or group of users

4.REVOKE command deletes the privileges

Granting System Privileges

1.Use the GRANT command to grant system privileges

2. The grantee can further grant the system privilege with the ADMIN option

    GRANT CREATE SESSION To emi

    GRANT CREATE SESSION to emi WITH ADMIN OPTION

Who Can Grant ro Revoke

Only two types of users can grant system privileges to other users or revoke those privileges from them;

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

2. Users with the system privilege GRAN ANY PRIVILEGE

For this reason, only grant these privilege to trusted users

The PUBLIC

 Pbblic is a user group defined in the database; it is not a database user or a role.Every user in the database belongs to this group . Therefore, if you grant privileges to PUBLIC , they are available to all users of the database.

SYSDBA and SYSOPER

查看自己有哪些权限

select * from session_privs;

如果一般的用户也要能看到 数据字典，则设置07_DICTINARY_ACCESSIBILITY=ture scope=spfile

一般设置为false. 安全问题

如果一个用户有 create session,Create any proceduere ,  execute any procedure

然后可以通过:create procedure system.h1(h1_str in varchar 2 ) as begi execute immediate h1_str; end; /

然后执行： execute system.h1('grant dba to hacker');  获得了所有权限

Some  Points

1.To connect to the database, you need the CREATE SESSION privilege.

2. To truncate a table that belongs to another schema, you need the DROP ANY TABLE privilege.

3. The CREATE ANY PROCEDURE (or EXECUTE ANY PROCEDURE ) privilege allows the user to create , replace, or drop (or execute ) prcedures , packages, and functins this includes Java classes.

4. The CRATE TABLE privilege gives you the ability to create , alter ,drop, and query tables in a schema.

5. SELECT , INSERT, UPDATE and DELETE are object privilege, but SELECT ANY, INSERT ANY,UPDATE ANY, and DELETE ANY are system privileges(in other wrds they do not apply to a particular object)

Object Privileges

Granting Object Privileges

1.Use the GRANT command to grant object privileges

2. Grant must be in grantor's schema or grantor must have grant OPTION

   GRANT EXECUTE on dbms_output TO jeff;

   GRANT UPDATE ON emi.customers TO jeff WITH GRANT OPTION

   also: GRANT ALL on emi.customers TO jeff;

对于Object privileges中 撤销 也有传递性

GRANT的时候  system privileges 和 object privileges 不能混着写

Getting Privileges Inform

DBA_SYS_PRIVS

SESSIN_PRIVS

DBA_TAB_PRIVS    Object privilege

DBA_COL_PRIVS   Object privilege

role_role_prive 查询角色中有哪些角色

role_sys_privs 角色中有哪些系统权限

role_tab_privs: 角色中有哪些object权限

Predefined Roles

CONNECT : privilege toconnect ot he database, to create a cluster , a database link, a sequence, a synonym, a table, and a view, and to alter a sesion.

RESOURCE: Privilege tocreate a cluster, a table , and sequence, and to create programmatic objects such as procedures , functions, packages, indextypes, types, triggers, and operators.

DB: All system privileges with the ADMIN option , so the system privileges can be ranted to other users f the database r t roles

SELECT_CATALOG_ROLE  Ability to query the dictinary views and tables

EXECUTE_CATALG_ROLE Privilege to execute the dictionary packages

DELETE _CATALG_ROLE Abileti to dorp r re-create the dictionary packages also,when yu run the catproc.sql scripts as part of the database

EXP_FULL_DATABASE: ability to make full and incremental exports of the database using the Export utility.

IMP_FULL_DATABASE: ability to perform full database imprts using the Import utility.This is a very powerful role

Establishing Default Roles:

A user can be assigned many roles.

A user can be assigned a default role.

Limit the number of default roles for a user.

Enable & Disable Roles

1.Disable a role t temporarily revoke the rle form a user.

2.Enable a role to grant it temporarily.

3.The SET ROLE command  enables and disables roles.

4. Default roles are enabled for a user at login.

5. A password may be required to enable a role.

ALTER USER scott DEFAULT ROLE hr_clerk, oe_cler;
ALTER USER scott DEFAULT ROLE ALL;
ALTER USER scott DEFAULT ROLE ALL EXCEPT hr_clear;

Revoking Roles from Users

1.Revoking roles from users requires  the ADMIN OPTION or GRANT ANY ROLE privilege

2. To revoke a role:

    a.REVOKE oe_clerk FROM scott;

    b.REVOKE hr_manager FROM PUBLIC;

 Droping Roles

1.Dropping a role:

    a.Removes it from all users and roles it was granted

    b. Removes it from the database

2.Requires the ADMIN OPTION or DROP ANY ROLE privilege

3.To drop a role

   DROP ROLE hr_manager;

Application Roles:

1.Application roles can be enabled only by authorized PL/SQL packages

2. the USING package clause creates an application role.

CREATE ROLE admin_role INENTIFIED USING hr.employee;

Auditing

1.Auditing is the monitoring of selected user database actions and is used t :

   a. Investigate suspicious database activity

   b . Gather informatin about specific database activities

2.Auditing can be performed by sessin or access

   session:多条同类型的记录只记录一条

   access: 记录每一条SQL

Auditing Types

Auditing Categories

1.Audited by default:

   a.Instance startup and instance shutdown

   b.Administrator privileges

2.Database auditing

   a. Enabled by the DBA

   b.Cannot record column values

3.Value-based or application auditing

   a.Implemented through code

   b.Can record column values

   c.Used to track changes to tables

然后重新打开一个窗口: U1登录  然后退出

noaudit select on u1.t;

注意： audit select any table 的时候，查自己的表不会被审计

基表是： $.audit