用户管理

CREATE USER jward
 IDENTIFIED BY password
 DEFAULT TABLESPACE data_ts
 QUOTA 100M ON test_ts
 QUOTA 500K ON data_ts
 TEMPORARY TABLESPACE temp_ts
 PROFILE clerk;
GRANT CREATE SESSION TO jward;

从上面这个例子我们可以看出创建一个用户需要指定密码，默认表空间，表空间的限额，用户默认临时表空间，profile，还要给用户分配相应的权限。

 

一、用户的创建

新建的用户是不能连接到数据库的，必须有CREATE SESSION的系统权限才能连接到数据库，这是最小权限。如果用户要访问em，那么就必须给SELECT ANY DICTIONARY 的权限

create user identified by  mima;   ---创建用户指定密码

 

给普通用户修改密码

方法一、

SQL> password u1
更改 u1 的口令
新口令:
重新键入新口令:
口令已更改

方法二、

alter user u1 identifiy by u1

 

给sys用户修改密码

• The SYS user account is used by most of the internal recursive SQL. Therefore, if you try to use theALTER USER statement to change this password while the database is open, then there is a chance that deadlocks will result.

• If you try to use ALTER USER to change the SYS user password, and if the instance initialization parameterREMOTE_LOGIN_PASSWORDFILE has been set toSHARED, then you cannot change theSYS password. The ALTER USER statement fails with an ORA-28046: Password change for SYS disallowed error.

 如果alter usesr 语句来修改sys密码，有可能会造成死锁。如果实例的初始化参数 REMOTE_LOGIN_PASSWORDFILE=shared 你是不能改变密码的

orapwd file='orapworcl'
Enter password for SYS: new_password

 

 

 

二、指定表空间

给用户指定默认表空间的目的是为了有create table 权限的用户创建表的时候没有指定表空间，就会把表创建到users表空间，为了减少系统表空间出问题的可能性，最好把用户数据和系统数据分开。设置成用户的默认永久表空间是不能被删除的，除非为用户指定其他默认临时表空间。

alter user u1 default tablespace test;

 

三、指定表空间的限额

 默认情况下，用户对表空间没有任何限额的。你可以为用户在个别表空间指定限额，或者所有表空间都unlimited，指定表空间限额的目的是为了防止使用过多的磁盘空间。

If a new quota is less than the old one, then the following conditions remain true:

• If a user has already exceeded a new tablespace quota, then the objects of a user in the tablespace cannot be allocated more space until the combined space of these objects is less than the new quota.

• If a user has not exceeded a new tablespace quota, or if the space used by the objects of the user in the tablespace falls under a new tablespace quota, then the user's objects can be allocated space up to the new quota.

  

在用户指定新的配额时， 如果用户已经超过了表空间的限额，那么这个用户就不能获取更多的磁盘空间，如果用户没有超过表空间的限额或者表空间中对象使用了一个新的表空间配额，那么用户对象可以获取到新的空间大小到达新配额

修改用户表空间的限额

alter user u1 quota 100M on test；---test是表空间名

alter user u1quota unlimited on test

 

 

四、指定临时表空间

 如果你不指定用户的临时表空间，那么就会使用用户的默认临时表空间。如果database没有默认的临时表空间，就是使用system表空间作为临时表空间。专门指定一个表空间有利于消除临时表空间段和其他段类型的争用

 If your SYSTEM tablespace is locally managed, then users must be assigned a specific default (locally managed) temporary tablespace. They may not be allowed to default to using theSYSTEM tablespace because temporary objects cannot be placed in locally managed permanent tablespaces.

  如果system表空间是本地管理，那么用户必须指定默认的临时表空间，他们不能用system表空间，因为临时对象不能放在本地管理的永久表孔；

 

 

 

五、指定profile

create pfile 创建一个profile的目的是为了限制资源的使用，使用用户不会超过profile限定的资源。

 

使用profile的先决条件

1、有创建profile的系统权限  grant CREATE PROFILE  to user；

2、 打开RESOURCE_LIMIT

       You can specify resource limits for users regardless of whether the resource limits are enabled. However, Oracle Database does not enforce the limits until    you enable them.

SQL> show parameter resource

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------
resource_limit                       boolean     FALSE
resource_manager_cpu_allocation      integer     4
resource_manager_plan                string
SQL> alter system set resource_limit=true;

系统已更改。

SQL> show parameter resource

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------
resource_limit                       boolean     TRUE
resource_manager_cpu_allocation      integer     4
resource_manager_plan                string

3、create profile

SQL> create profile pr limit sessions_per_user 1 idle_time 2  connect_time  2;

配置文件已创建

 

4、设置用户的profile

SQL> alter user u1 profile pr;

用户已更改。

创建profile的语法：

resource_parameters::

password_parameters ::=

基中：
SESSION_PER_USER: 用户可以同时连接的会话数量限额；
CPU_PER_SESSION:用户在一次数据库会期间可占用的CPU时间总量限额，单位为百分之一秒；
USER_PER_CALL:用户一次ＳＱＬ调用可用的ＣＰＵ时间总量限额，单位为百分之一秒；
LOGICAL_READS_PER_SESSION：在一次数据库会话期间能够读取的数据库块的个数限额；
LOGICAL_READS_PER_CALL:一次ＳＱＬ调用可以读取的数据库块数限额；
IDLE_TIME：用户连接到数据库后的可空闲时间限额，单位为分钟，若空闲时间超过此值，则连接被断开；
CONNECT_TIME:一次连接的时间总量限额，单位为分钟，连接时间超过此值时，连接被断开；
PRIVATE_SGA：用户么有的ＳＧＡ区的大小，单位为数据库块，默认值为UNLIMITED；
COMPOSITE_LIMIT：这是一项由上述限制参数构成的组合资源项。举例来说，假设资源设置如下：
　　IDLE_TIME 20
    CONNECT_TIME 120
    CPU_PER_CALL 750
    COMPOSITE_LIMT 800
那么，当会话空间超过20分钟，或者连接时间超过120分钟，又或者执行一个ＳＱＬ耗费超过7.5秒，再或者这几个资源限制加起来的总数超过800，则系统自动终止会话。
FAILED_LOGIN_ATTEMPTS:用户登录时，允许用户名/密码校验失败致使用登录失败的次数限额，超过该次数，帐户被锁定；
PASSWORD_LIFE_TIME：口令有效时间，单位为天数，超过这一时间，拒绝登录，须重新设置口令，默认值为UNLIMITED；
PASSWORD_REUSE_TIME:一个失效口令经过多少天后才可重新利用，默认为UNLIMITED；
PASSWORD_REUSE_MAX：一个口令可重复使用的次数；
PASSWORD_LOCK_TIME:当登录失败达到FAILED_LOGIN_ATTEMPS时，帐户被锁定，该参数用于设定被锁定的天数，如故忽略默认为1天；

 PASSWORD_GRACE_TIME  :修改 密码的宽限期，在宽限期间，用户还可以正常登陆，如果忽略，默认是7天

 

 举例:

CREATE PROFILE app_user LIMIT 
   SESSIONS_PER_USER          UNLIMITED 
   CPU_PER_SESSION            UNLIMITED 
   CPU_PER_CALL               3000 
   CONNECT_TIME               45 
   LOGICAL_READS_PER_SESSION  DEFAULT 
   LOGICAL_READS_PER_CALL     1000 
   PRIVATE_SGA                15K
   COMPOSITE_LIMIT            5000000; 

CREATE PROFILE app_user2 LIMIT
   FAILED_LOGIN_ATTEMPTS 5
   PASSWORD_LIFE_TIME 60
   PASSWORD_REUSE_TIME 60
   PASSWORD_REUSE_MAX 5
   PASSWORD_VERIFY_FUNCTION verify_function
   PASSWORD_LOCK_TIME 1/24
   PASSWORD_GRACE_TIME 10;

 

Oracle Database enforces resource limits in the following ways:

• If a user exceeds the CONNECT_TIME or IDLE_TIME session resource limit, then the database rolls back the current transaction and ends the session. When the user process next issues a call, the database returns an error.

• If a user attempts to perform an operation that exceeds the limit for other session resources, then the database aborts the operation, rolls back the current statement, and immediately returns an error. The user can then commit or roll back the current transaction, and must then end the session.

• If a user attempts to perform an operation that exceeds the limit for a single call, then the database aborts the operation, rolls back the current statement, and returns an error, leaving the current transaction intact.

   如果用户超过了profile限定的 connect_time or idle_time,那么正在进行的事物会被回滚，结束当前session，下次处理会被报错

create profile pr limit sessions_per_user 1 idle_time 2  connect_time  2;   

alter user u1 profile pr;

conn u1/u1

insert。。。

 两分钟后执行truncate table ，报错

        truncate table t2
*
第 1 行出现错误:
ORA-02396: 超出最大空闲时间, 请重新连接

 如果用户超过的sesion的资源，那么当前语句会被rollback，并且会报错

 

resource_parameters::

password_parameters::

举例：

ALTER PROFILE app_user LIMIT
   FAILED_LOGIN_ATTEMPTS 5
   PASSWORD_LOCK_TIME 1;

 

SQL> alter profile pr  limit failed_login_attempts 2;

配置文件已更改

SQL> conn u1/u
ERROR:
ORA-01017: invalid username/password; logon denied

警告: 您不再连接到 ORACLE。
SQL> conn u1/uu
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> conn u1/u1
ERROR:
ORA-28000: the account is locked

 

 

查看profile

SQL> select * From dba_profiles where profile='PR'
  2  ;

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- ---------------
PR                             COMPOSITE_LIMIT                  KERNEL   DEFAULT
PR                             SESSIONS_PER_USER                KERNEL   1
PR                             CPU_PER_SESSION                  KERNEL   DEFAULT
PR                             CPU_PER_CALL                     KERNEL   DEFAULT
PR                             LOGICAL_READS_PER_SESSION        KERNEL   DEFAULT
PR                             LOGICAL_READS_PER_CALL           KERNEL   DEFAULT
PR                             IDLE_TIME                        KERNEL   2
PR                             CONNECT_TIME                     KERNEL   2
PR                             PRIVATE_SGA                      KERNEL   DEFAULT
PR                             FAILED_LOGIN_ATTEMPTS            PASSWORD DEFAULT
PR                             PASSWORD_LIFE_TIME               PASSWORD DEFAULT
PR                             PASSWORD_REUSE_TIME              PASSWORD DEFAULT
PR                             PASSWORD_REUSE_MAX               PASSWORD DEFAULT
PR                             PASSWORD_VERIFY_FUNCTION         PASSWORD DEFAULT
PR                             PASSWORD_LOCK_TIME               PASSWORD DEFAULT
PR                             PASSWORD_GRACE_TIME              PASSWORD DEFAULT

已选择16行。

SQL> SELECT USERNAME, PROFILE, ACCOUNT_STATUS, AUTHENTICATION_TYPE FROM DBA_USERS where username='U1';

USERNAME                       PROFILE                        ACCOUNT_STATUS                   AUTHENTI
------------------------------ ------------------------------ -------------------------------- --------
U1                             PR                             LOCKED(TIMED)                    PASSWORD

 

删除profile

drop profile pr;

 

 

六、用户权限的查询

ORACLE中数据字典视图分为3大类,     用前缀区别，分别为：USER，ALL 和 DBA，许多数据字典视图包含相似的信息。

USER_*:有关用户所拥有的对象信息，即用户自己创建的对象信息

ALL_*：有关用户可以访问的对象的信息，即用户自己创建的对象的信息加上其他用户创建的对象但该用户有权访问的信息

DBA_*：有关整个数据库中对象的信息

（这里的*可以为TABLES， INDEXES， OBJECTS， USERS等。

1.查看所有用户：
select * from dba_user;
select * from all_users;
select * from user_users;
2.查看用户系统权限：
select * from dba_sys_privs;
select * from all_sys_privs;
select * from user_sys_privs;
3.查看用户对象权限：
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
4.查看所有角色：
select * from dba_roles;
5.查看用户所拥有的角色：
select * from dba_role_privs;
select * from user_role_privs;

6.查看当前用户的缺省表空间
select username,default_tablespace from user_users;

7.查看某个角色的具体权限，如grant connect,resource,create session,create view to TEST;查看RESOURCE具有那些权限，用SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='RESOURCE';

 

参考：http://docs.oracle.com/cd/E11882_01/network.112/e16543/users.htm#DBSEG002