1、
使用sqlplus 登陆system
先设置本地字符集:
export NLS_LANG="AMERICAN_AMERICA.AL32UTF8";
sqlplus system/0P-0P-0P-@192.168.1.66:1521/orcl
sqlplus usr1/usr1@192.168.1.66:1521/orcl
sqlplus usr2/usr2@192.168.1.66:1521/orcl
2、创建新用户
create user usrName identified by usrPasswd;
create user usr1 identified by usr1;
create user usr2 identified by usr2;
create user usr3 identified by usr3;
create user usr4 identified by usr4;
create user usr5 identified by usr5;
create user usr6 identified by usr6;
修改密码:
alter user usrName identified by newPasswd;
3、查看所有用户所在表空间
select username,default_tablespace from dba_users;
查看指定用户所在表空间:
SELECT DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME = 'USR1';
4、创建新的表空间
mkdir -p /home/oracle/DataInstance
create tablespace usr1_tbspace datafile 'G:\app\wenlz\usr1_data.dbf' size 2048M;
create tablespace usr2_tbspace datafile 'G:\app\wenlz\usr2_data.dbf' size 2048M;
create tablespace usr1_tbspace datafile '/home/oracle/DataInstance/usr1_data.dbf' size 1024M;
create tablespace usr2_tbspace datafile '/home/oracle/DataInstance/usr2_data.dbf' size 1024M;
create tablespace usr3_tbspace datafile '/home/oracle/DataInstance/usr3_data.dbf' size 1024M;
create tablespace usr4_tbspace datafile '/home/oracle/DataInstance/usr4_data.dbf' size 1024M;
create tablespace usr5_tbspace datafile '/home/oracle/DataInstance/usr5_data.dbf' size 1024M;
create tablespace usr6_tbspace datafile '/home/oracle/DataInstance/usr6_data.dbf' size 1024M;
> create tablespace test_space datafile 'd:\app\HX\test_space.dbf' size 4096M;
> Tablespace created.
5、表空间分配给用户
alter user usr1 default tablespace test_space;
alter user usr2 default tablespace test_space;
6、为用户分配权限
grant create session,create table,create view,create sequence,create trigger, unlimited tablespace to user;
grant create session,create table,create view,create sequence,unlimited tablespace to usr1;
grant create session,create table,create view,create sequence,unlimited tablespace to usr2;
grant create session,create table,create view,create sequence,create trigger, unlimited tablespace to usr1;
grant create session,create table,create view,create sequence,create trigger, unlimited tablespace to usr2;
grant create session,create table,create view,create sequence,create trigger, unlimited tablespace to usr3;
grant create session,create table,create view,create sequence,create trigger, unlimited tablespace to usr4;
grant create session,create table,create view,create sequence,create trigger, unlimited tablespace to usr5;
grant create session,create table,create view,create sequence,create trigger, unlimited tablespace to usr6;
7、登录用户并查看权限
export NLS_LANG="AMERICAN_AMERICA.AL32UTF8";
sqlplus usr1/usr1@192.168.1.66:1521/orcl
8、删除用户及其相关对象
drop user usr1 cascade;
grant给表赋权限:
1、grant 权限 on 表 to 用户。
grant select/update on table to username;
例如:
grant select on table_name to user_name;
2、grant 给存储过程赋权限:
grant execute on 过程、包、方法 to user
grant execute on package/function/procedure to username;
例如:
授权某个触发器到数据库其它用户
grant execute on func_name to username;
3、grant 用户序列授权
grant select on sequence_name to username;
4、一次把权限给完(过程、包、方法):
grant execute any procedure to username;
给其创建触发器权限:
grant create trigger to username;
grant execute any procedure to usr1;
grant execute any procedure to usr2;
触发器权限
要想创建触发器,必须要有CREATE TRIGGER,CREATE ANY TRIGGER的权限。如:
要想使用户tt有创建触发器的权限,则执行命令:
Grant CREATE TRIGGER to tt;
要想使用户tt有在其他模式(any schema)创建触发器的权限,则执行命令:
Grant CREATE ANY TRIGGER to tt;