Oracle11g温习-第十六章：用户管理
2013年4月27日 星期六
10:50
|
1、概念 |
|
(1)schema ： user.object 就是用户创建的对象 (2)用户认证方式： os 认证 database 认证 |
|
2、建立 database认证的用户 |
|
SQL @ prod > create user rose identified by oracle default tablespace users temporary tablespace temp quota 10m on users 【用户配额限制】 password expire; ——【用户一登录密码就过期，需要重新设定】
User created.
SQL @ prod > grant create session to rose; Grant succeeded.
SQL @ prod > conn rose/oracle ERROR: ORA-28001: the password has expired
Changing password for rose …. New password: ... Retype new password: ... Password changed Connected.
|
|
3、建立OS认证(操作系统认证)用户(sys 用户属于os 认证) |
|
SQL @ prod > show parameter auth
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ os_authent_prefix string ops$ 【——创建OS认证的用户名前面一定要加上这个参数】 remote_os_authent boolean FALSE
SQL @ prod > create user ops$oracle ——创建用户，不要加双引号 identified externally profile default default tablespace users temporary tablespace temp quota 10m on users
SQL @ prod > select username,account_status from dba_users;
USERNAME ACCOUNT_STATUS --------------- ------------------------- OUTLN OPEN SYS OPEN SYSTEM OPEN ROSE OPEN SCOTT OPEN ops$oracle OPEN TOM OPEN DBSNMP EXPIRED & LOCKED TSMSYS EXPIRED & LOCKED DIP EXPIRED & LOCKED
SQL @ prod > select username ,password ,PROFILE,DEFAULT_TABLESPACe,TEMPORARY_TABLESPACE from dba_users;
USERNAME PASSWORD PROFILE DEFAULT_TABLESP TEMPORARY_TABLE --------------- -------------------- --------------- --------------- --------------- OUTLN 4A3BA55E08595C81 DEFAULT SYSTEM TEMP SYS 8A8F025737A9097A DEFAULT SYSTEM TEMP SYSTEM 2D594E86F93B17A1 DEFAULT SYSTEM TEMP ROSE 1166A1F535AF6EFB DEFAULT USERS TEMP SCOTT F894844C34402B67 DEFAULT USERS TEMP ops$oracle EXTERNAL DEFAULT USERS TEMP TOM 0473A0A9140BFBD7 DEFAULT USERS TEMP DBSNMP E066D214D5421CCC DEFAULT SYSAUX TEMP TSMSYS 3DF26A8B17D0F29F DEFAULT USERS TEMP DIP CE4A36B8E06CA59C DEFAULT USERS TEMP
SQL @ prod > grant create session to ops$oracle; Grant succeeded.
SQL @ prod > exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production With the Partitioning, OLAP and Data Mining options
[oracle@solaris10 ~]$ id uid=100(oracle) gid=100(oinstall)
[oracle@solaris10 ~]$ sqlplus / ——【登录不需要提供用户名和密码(oracle用户必须属于os的 oinstall 组 )】
SQL*Plus: Release 10.2.0.2.0 - Production on Wed Mar 14 16:07:43 2012 Copyright (c) 1982, 2005, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production With the Partitioning, OLAP and Data Mining options
SQL @ prod > show user USER is "OPS$ORACLE" |
|
4、quota 管理：(对象的最大存储空间，用户在表空间上建立对象，必须在相应的tablespace 上获得quota) |
|
SQL @ prod > select TABLESPACE_NAME,USERNAME,BYTES/1024,MAX_BYTES/1024/1024 from dba_ts_quotas;
TABLESPACE_NAME USERNAME BYTES/1024 MAX_BYTES/1024/1024 -------------------- --------------- ---------- ------------------- USERS ROSE 0 10 USERS OPS$ORACLE 0 10
——【BYTES 已经使用过的配额，MAX_BYTES所分配的配额】
SQL @ prod > grant create table to rose; Grant succeeded.
SQL @ prod > grant select on scott.emp to rose; Grant succeeded.
SQL @ prod > conn rose/rose Connected.
SQL @ prod > create table emp1 as select * from scott.emp; Table created.
SQL @ prod > conn /as sysdba Connected.
SQL @ prod > select TABLESPACE_NAME,USERNAME,BYTES/1024,MAX_BYTES/1024/1024 from dba_ts_quotas where username='ROSE';
TABLESPACE_NAME USERNAME BYTES/1024 MAX_BYTES/1024/1024 -------------------- --------------- ---------- ------------------- USERS ROSE 64 10
——回收quota【只能回收用户未使用的磁盘配额】 SQL @ prod > alter user rose quota 0 on users;
User altered.
SQL @ prod > select TABLESPACE_NAME,USERNAME,bytes/1024,max_bytes/1024/1024 from dba_ts_quotas where username='ROSE';
no rows selected 【——已经回收，但仍然可以插入数据，因为之前已经使用的磁盘配额没用完】
SQL @ prod > conn rose/rose Connected.
ROSE @ prod > insert into emp1 select * from emp1;
14 rows created.
ROSE @ prod > /
28 rows created.
ROSE @ prod > /
56 rows created.
ROSE @ prod > insert into emp1 select * from emp1 * ERROR at line 1: 【ORA-01536: space quota exceeded for tablespace 'USERS'
ROSE @ prod > analyze table emp1 compute statistics; ——没磁盘配额了 】
Table analyzed.
ROSE @ prod > select table_name,num_rows ,blocks,empty_blocks from user_tables;
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS ------------------------------ ---------- ---------- ------------ EMP1 448 8 0 |
|
5、删除用户【会话中的用户不能被删除】 |
|
SYS @ prod > select username,sid,serial# from v$session where username is not null;
USERNAME SID SERIAL# ---------- ---------- ---------- SYS 31 84 ROSE 39 31
SYS @ prod > drop user rose;——会话中的用户不能被删除 drop user rose * ERROR at line 1: ORA-01940: cannot drop a user that is currently connected
——强制关闭用户会话 SYS @ prod > alter system kill session '39,31';——‘SID，SERIAL#’ System altered. SYS @ prod > select * from emp1; select * from emp1 * ERROR at line 1: ORA-00028: your session has been killed SYS @ prod > drop user rose cascade; ——将用户所有的对象都一起删除
User dropped. |
|
磁盘配额
create user xxx quota 50m on system 指定用户在system 上50M 的磁盘空间。 alter system kill session 'sid,serial#' 杀掉用户进程 desc v$process 查找 ADDR SPID desc session_privs 用户权限。 desc session_roles set role develogment , manager desc dba_role_privs; alter user xxxx default role |
