oracle truct,Oracle user and resource学习与测试_20100110

Oracle user and resource学习与测试

参考文档

<>P595

<>P721

目录

1 overview user security

2 User Authentication

3 Authentication of Database Administrator

4 The User Group PUBLIC

5 User Resource Limits and profiles

6 Security Checklist

7 Notes

1 overview user security

when concerning a user’s security domain, something must be considered include:

1) user authentication

2) user default tablespace and default temporary tablespace

3) quotas for each tablespace

4) user profile about system resource limit

5) privileges ,roles and security policies for user.

(当谈及到用户相关的问题时，必须清晰的东西)

2 User Authentication

1) The operating system OS

2) A network service

以上两种归为External Authentication Method.

When you choose external authentication for a user, the user account is maintained by Oracle, but password administration and user authentication is performed by an external service. This external service can be the operating system or a network service, such as Oracle Net.(用户由Oracle维护，但密码由操作系统或网络服务维护)

OS authentication method

1)设置OS_AUTHENT_PREFIX=OPS$(大小写敏感)

2)设置REMOTE_OS_AUTHENT=true.(必须重启生效)

3)建立操作系统用户test

4)建立Oracle用户CREATE USER OPS$test IDENTIFIED EXTERNALLY

5)以test用户登录操作系统和Oracle

Conn test不用指定密码.

Oracle系统组合操作系统用户test加前缀OPS$,即OPS$test,在Oracle中查找是否有对应的用户OPS$test.如存在则登录成功.

Setting the initialization parameter REMOTE_OS_AUTHENT to TRUE allows the RDBMS to accept the client operating system user name received over a nonsecure connection and use it for account access. The change take effect the next time you start the instance and mount the database.

OS authentications Advantages优势:连接更快且方便,不用单独指定用户密码;数据库和操作系统双重审计.

Network authentication method

Network authentication is performed using Oracle Advanced Security,which can be configured to use a third party service such as Kerberos.The settiing of the parameter REMOTE_OS_AUTHENT is irrelevant.

Network authentication Advantages: More choice of authentication mechanism are available, such as smart cards, fingerprints, or the operating system.

3) The associated Oracle database

平时所用的就是这种方式

CREATE USER scott IDENTIFIED BY tiger;

Advantages:不依赖于外部程序,提供了安全的策略保证.

4) The Oracle database of a middle-tier application that performs transactions on behalf of the user.

5) The Secure Socket Layer(SSL) protocol

3 Authentication of Database Administrator

Database administrators perform. special operation that should not be performed by normal database users. Oracle provides a more secure authentication schema for database administrator usernames. You can choose between operating system authentication or password files to authenticate database administrator.(这就是平常所讨论的关于用户认证方式．其实是针对于Database Administrator．详细参考<>)

On most operating systems, operating system authentication for database administrators involves placint the operating system username of the database administrator in a special group(oracle in dba group) or giving that operating system username a special process right.(操作系统认证方式，将oracle用户放到dba组)

The database uses password file to keep track of database usernames who have been granted the SYSDBA and SYSOPER privileges.(orapwSID file.平常用的就是这种方式)

1) SYSOPER lets database administrators perfore STARTUP, SHUTDOWN, ALTER DATABASE OPEN/MOUNT, ALTER DATABASE BACKUP,ARCHIVE LOG, and RECOVER, and includes the RESTRICTED SESSION privileges

2) SYSDBA contains all system privileges with ADMIN OPTION, and SYSOPER system privileges.Permits CREATE DATABASE and TIME-BASED revovery.

这个应与User Authentication Methods区分开来．即2, 3应区分开来．

4 The User Group PUBLIC

Each database contains a user group called PUBLIC. Every user automatically belongs to the PUBLIC user group.As members of PUBLIC, users can see(select from) all data dictionary tables prefixed with USER and ALL. Additionally, a user can grant a privilege or a role to PUBLIC. All users can use the privileges granted to PUBLIC.

PUBLIC has the following restrictions:

1)you cannot assign tablespace quotas to PUBLIC, although you can assign the UNLIMITED TABLESPACE system privileges to PUBLIC.

2)You can create database links and sysnonyms as PUBLIC(create public database link /synonym), but no other schema object can be owned by PUBLIC. (CREATE TABLE PUBLIC.employees xxx is illegal)

5 User Resource Limits and profiles

5.1 User Resource Limts

You can set limits on the amount of various system resources available to each user as part of a user’s security domain. A slight degradation in performance occurs when users create sessions.This is because Oracle loads all resource limit data for the user when a user connects to a database.(性能有影响．建立session时，装载所有资源)

You can control each of these resources at the session level, the call level, or both.

1)Session Level(会话级,在每一个Session内部，就是每一条语句)

If a user exceeds a session-level resource limit, Oracle terminates(rollback) the current statement and returns a message indicating the session limit has been reached. At this point, all previous statements in the current transaction are intact.(如果超出限制，则只回滚当前语句不是当前事务所有的语句)

2) Call Level(调用级，在每一个语句内部)

Each time a SQL statement is executed, several steps are taken to process the statement. During this processing, several calls are made to the database as part of the different execution phases. To prevent any one call from using the system excessively, Oracle lets you set several resource limits at the call level.If a user exceeds a call-level resource limit, Oracle halts the processing of the statement, rolls back the statement, and returns an error. However, all previous statements of the current transaction remain intact, and the user’s session remains connected.

5.2 Types of System Resource

1)CPU t ime

2)Logical reads(memory and disk)

3)Other resource(concurrent sessions for each user, idle time for a session, elapsed time for each session,Private SQL areas)

5.3 Profile

A Profile is a named set of specified resource limits that can be assigned to a valid username of an Oracle database. Profile provide for easy management of resource limits. Profile are also the way in which you administer password policy.

Usually, the best way to determine the appropriate resource limit values for a given user profile is to gather historical information about each type of resource usage.(分配资源前先做好相关统计，实际应用中，一般在大系统中才会做资源限制，普通的系统很少做)

6 Security Checklist

1) Install only what is required

2) Lock and expire default user accounts

3) Change Default user password

4) Enable data dictionary protection

To enable dictionary protection, set the O7_DICTIONARY_ACCESSIBILITY

initialization parameter, in the following manner:

O7_DICTIONARY_ACCESSIBILITY = FALSE

By doing so, only those authorized users making DBA-privileged (for example CONNECT / AS SYSDBA) connections can use the ANY system privilege on the data dictionary. If this parameter is not set to the value recommended above, any user with a DROP ANY TABLE (for example) system privilege will be able to maliciously drop parts of the data dictionary.(9i及以后版本默认为False)

5) practice principle of least privilege(最小特权原则授权)

nGrant necessary privileges only,

nRevoke unnecessary privileges from PUBLIC

6) enforce access controls effectively

To restrict remote authentication and thereby defer client truct to the databae, set the REMOTE_OS_AUTHENT initialization parameter in the following mananer:

REMOTE_OS_AUTHENT = FALSE;

关掉远程OS认证方式

7) RESTRICT OPERATING SYSTEM ACCESS

Restricting the ability to modify the default file and directory permissions for the ORACLE_HOME(installation) directory or its contents.Even privileged operating system users and the Oracle owner should not modify these permissions.(限制更改ORACLE_HOME目录的权限和内容)

This recommendation applies to all types of files: datafiles, log files, trace files, external tables, bfiles,etc.

在操作系统层面保护ORACLE相关的目录和内容．

8) RESTRICT NETWORK ACCESS

Utilize a firewall

Never poke a hole through a firewall

Prevent unauthorized administration of the Oracle Listener

Set the listener.ora security configuration parameter in the following manner.

ADMIN_RESTRICTIONS_listener_name = ON

Doing so will also prevent unauthorized administration of the Oracle Listener.

Check network IP addresses

Utilize the Oracle Net "valid node checking" security feature to allow or deny access to Oracle server processes from network clients with specified IP addresses. To use this feature, set the following protocol.ora (Oracle Net configuration file) parameters: (没看到这个文件?)

tcp.validnode_checking = YES

tcp.excluded_nodes = {list of IP addresses}

tcp.invited_nodes = {list of IP addresses}

Harden the operating system

Close the UDP and TCP ports for each service that is being disabled.

9) apply all security patches and workarounds

打安全相关的补丁

10) Contact oracle security products

7 Notes

1关于临时段的一些东西

1) Temporary segments do not consume any quota that a user might posses.The schema objects that Oracle automatically creates iin temporary segments are owned by SYS and therefore are not subject to quotas.(临时段不占用用户拥有的限额，临时段上的对象归SYS所有)

2) Temporary segments can be created in a tablespace for which a user has no quota.(临时段创建在用户没有限额的表空间上，原因见1.但并不是说临时段不占用空间，只是不占用Quota)

2关于用户限额

1)收回用户在某个表空间上创建对象的权限可以通过将此用户在该表空间上的限额设定为0

2) unlimited tablespace system privilege

To permit a user to use an unlimited amount of any tablespace in the database, grant the user the UNLIMITED TABLESPACE system privilege. This overrides all explicit tablespace quotas for the users.if you later revoke the privilege, explicti quotas again take effect.you can grant this privilege only to user, not to roles.

(授予UNLIMITED TABLESPACE系统权限会override所有显式分配的在各个表空间上的限额)

测试:

SQL> alter user test quota 10M on tools;

User altered.

SQL>grant unlimited tablespace to test;

Grant succeeded.

SQL> select * from user_ts_quotas;

TABLESPACE_NAMEBYTESMAX_BYTESBLOCKS MAX_BLOCKS

------------------------------ ---------- ---------- ---------- ----------

USERS2752512-1336-1

TOOLS01048576001280

测试结果表明并不是这样的．???