Oracle创建用户、赋予权限的过程

zsmgcqxxmy

原文地址：Oracle创建用户、赋予权限的过程作者：haihan
cmd 进入oracle命令
<wbr><wbr>sqlplus /nolog</wbr></wbr>
<wbr> conn jrw/jrw@orcl</wbr>
create用户
　　sql 代码SQL> create user visiontv identified by visiontv default tablespace visiontv quo
　　ta 10m on users;


<wbr><wbr> //创建空间 <wbr>create tablespace test <wbr>datafile 'c:oracleoradataorcl9test.dbf' size 50M <wbr>default storage (initial 500K <wbr> Next 500K <wbr>minextents 1 <wbr>maxextents unlimited <wbr>pctincrease 0);</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>


　　//Oracle创建用户权限//创建用户 <wbr>create user lxg identified by lxg default tablespace test; <wbr></wbr></wbr>
<wbr><wbr> //授权　 <wbr>grant resource,connect,dba to test;</wbr></wbr></wbr>
<wbr><wbr> //删除表空间 <wbr> drop tablespace "空间名" including contents and datafiles</wbr></wbr></wbr>


　　删除用户 drop user "lxg" cascade


　　增加表空间 alter tablespace chinawater add datafile 'c:oracleoradataorcl9ADDCHI<wbr>NAWATER.dbf' size 200M</wbr>


　　创建用户 create user userName identified by password;


　　创建用户 userName,密码为 password


　　grant dba to lxg;--授予DBA权限<wbr></wbr>
<wbr><wbr> grant unlimited tablespace to lxg;--授予不限制的表空间 <wbr></wbr></wbr></wbr>
<wbr><wbr> grant select any table to lxg;--授予查询任何表 <wbr></wbr></wbr></wbr>
<wbr><wbr> grant select any dictionary to lxg;--授予 查询 任何字典</wbr></wbr>
<wbr><wbr> grant dba to lxg; <wbr></wbr></wbr></wbr>
<wbr><wbr> grant unlimited tablespace to lxg; <wbr></wbr></wbr></wbr>
<wbr><wbr> grant select any table to lxg; <wbr></wbr></wbr></wbr>
<wbr><wbr> grant select any dictionary to lxg;</wbr></wbr>


<wbr><wbr> oracle 正在连接的用户不能删除，确实要删除的话</wbr></wbr>


　　1、select sid,serial#,username from v$session where user='USERNAME';


　　2、alter system kill session 'sid,serial#';


　　3、drop user username cascade;




<wbr><wbr> 3.查看用户所拥有的表 <wbr>select table_name from user_tables; 或 <wbr>select *from tab;</wbr></wbr></wbr></wbr>


　　4.查看用户所拥有的视图 select view_name from user_views;


　　5.查看用户所拥有的触发器 <wbr>select trigger_name from user_triggers;</wbr>


　　6.查看用户拥有的序列 <wbr>select sequence_name from user_sequence;</wbr>


　　7.查看用户拥有的索引 <wbr>select index_name from user_indexs;</wbr>


　　8.显示当前用户 show user;


　　9.切换用户 <wbr>conn scott/tiger;</wbr>


　　10.将用户赋予某种角色登录 <wbr>conn scott/tiger as sysdba;</wbr>


　　11.查看所有用户 <wbr>conn system/unis; <wbr>select username from dba_users;</wbr></wbr>


　　12.查看用户所拥有的权限 select *from session_privs;


　　13.给用户加锁 <wbr>alter user scott account lock;</wbr>


　　14.给用户解锁 <wbr>alter user scott account unlock;</wbr>


　　15.修改用户密码 <wbr>alter user zzg identified by zzg123</wbr>


　　16.新建用户 <wbr> create user zzg identified by zzg123;</wbr>


　　17.删除用户及相关对象 <wbr>drop user zzg cascade;</wbr>


　　18.给用户赋权(多个采用逗号间隔) <wbr>grant create session,create table to zzg;</wbr>


　　19.分配表空间给用户 <wbr>alter user zzg default tablespace ts_zzg;<br>================ORACLE创建实例====================<br>create user local_twsms identified by local_twsms;<br>grant dba to local_twsms;--授予DBA权限 <br>grant unlimited tablespace to local_twsms;--授予不限制的表空间 <br>grant select any table to local_twsms;--授予查询任何表 <br>grant select any dictionary to local_twsms;--授予 查询 任何字典<br>grant dba to local_twsms; <br>grant unlimited tablespace to local_twsms; <br>grant select any table to local_twsms; <br>grant select any dictionary to local_twsms;<br>grant create session,create table to local_twsms;<br>==================================================<br></wbr>