用户权限

存储过程对象

SQL> create table account(account_id varchar2(5),name varchar2(10),sum number(10,2));

SQL> insert into account values('10001','tom',50000);

SQL> insert into account values('10002','kate',0);

SQL> select * from account;

模拟银行转账

create or replace procedure pro_zz(zc_zh in varchar2,zr_zh in varchar2,zz_je in int)

as

  var_sum int:=0;

begin

  select sum into var_sum from account where account_id=zc_zh;

  if var_sum<zz_je then

     dbms_output.put_line('meiqianle!');

  else

     update account set sum=sum-zz_je where account_id=zc_zh;

     dbms_output.put_line('zhuanchuchengong!');

     update account set sum=sum+zz_je where account_id=zr_zh;

     dbms_output.put_line('zhuanruchenggong!');

     commit;

  end if;

end;

/

(zc_zh 转出帐户,zr_zh 转入帐户,zz_je 转账金额)

SQL> set serveroutput on (显示过程)

SQL> exec pro_zz('10001','10002',10000);

SQL> select * from account;(tom少10000,kate多10000)

超过2w不让转帐

  var_vvv int:=0;

    update account set sum=sum-zz_je where account_id=zc_zh;

     insert into jy (account_id,zr_id,je)values(zc_zh,zr_zh,zz_je);

   end if;

    select sum(je) into var_vvv from jy where account_id=zc_zh;

   if 

   var_vvv > 20000 then

    dbms_output.put_line('burangzhuanle');

    rollback;

   else

   dbms_output.put_line('chenggong!!!!!');

   commit;

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

加一个限制约束条件

1.

SQL> alter table account add constraint ch_01 unique(account_id);

(account_id 唯一)

2.(帐户的金额不能大于40000)

alter table account

add constraint ch_02

check (sum<=45000)

enable novalidate;(表中已经有数据时,不对之前的数据进行验证)

3.SQL> select * from account;

4.SQL> exec pro_zz('10001','10002',40000);(不成功因为不能大于40000)

SQL> alter table account drop constraint ch_01;

(删除表的约束)

SQL> select constraint_name,table_name from user_constraints;

(查看所有表的约束)

SQL> select * from v$sqltext where SQL_TEXT like '%pro_zz%';

(执行的储存过程)

业务中储存过程的好处:前端只需要传输值就可以,不用传输sql语句了.数据库自行调用储存过程,减少了网络的传输量

SQL> create tablespace tbs_icbc datafile '/u01/app/oracle/oradata/orcl/tbs_icbc.dbf' size 10M;

SQL> create temporary tablespace tbs_temp tempfile '/u01/app/oracle/oradata/orcl/tbs_temp.dbf' size 10M;

查看库下用户

select * from all_users;

查看用户信息

select * from user_users;

创建用户(undo只能用一个)

SQL> create user icbc identified by icbc 

    default tablespace tbs_icbc

    temporary tablespace tbs_temp

    account unlock;

SQL> conn icbc/icbc(不能登陆,没有创建会话权限)

ERROR:

ORA-01045: user ICBC lacks CREATE SESSION privilege; logon denied

SQL> grant create session to icbc;(sys用户登陆下)[系统权限]

SQL> conn icbc/icbc(可以正常登陆)

SQL> show user

USER is "ICBC"

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

查看用户默认表空间

select default_tablespace from dba_users where username='SCOTT';

修改用户默认表空间

SQL> alter user scott default tablespace system;

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

SQL> create table tt(id int);(报错 没有创建表的权限)

create table tt(id int)

*

ERROR at line 1:

ORA-01031: insufficient privileges

2.

SQL> grant create table to icbc;(sys用户登陆下)

3.

SQL> create table tt(id int);(在表空间上权限不足,没有给用户空间配额)

4.SQL> alter user icbc quota 10M on tbs_icbc;(赋予用户空间配额,sys用户下)

  或

  SQL> alter user icbc quota unlimited on tbs_icbc;(不限制空间)

5.SQL> create table tt(id int); 可以创建

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

SQL> conn scott/abc

SQL> grant select on emp to icbc;(scott用户下)

(赋予icbc用户读 scott用户emp权限)

SQL> create table t_big as select * from scott.emp;(icbc用户下)[对象权限]

SQL> insert into t_big select * from t_big;

SQL> / (多次执行一直到超出配额限制)

SQL> select count(*) from t_big;

  COUNT(*)

----------

    114688

SQL> desc t_big

SQL> select * from t_big order by MGR desc;

(用户排序时,首先把排序结果放到pga中,pga空间不够时,使用用户默认的临时表空间)

查看临时表空间,排序会使用到临时表空间(windows客户端查看)

主键  外键

zs 用户

SQL> create table t1 (id int primary key);

SQL> insert into t1 values(1);

SQL> commit;

SQL> grant select on t1 to ls;  

SQL> grant references on t1 to ls;(赋予 参考 权限，供ls用户做外键使用）

ls 用户

SQL> create table t2 (id int);

SQL> alter table t2 add constraint tf foreign key(id) references zs.t1(id); （添加外键）

验证：

SQL> insert into t2 values(1);  （可以 因为zs.t1表中存在）

SQL> insert into t2 values(2);   （不可以  因为zs.t1表中不存在）

用户资源限制

SQL> show parameter process (默认150个连接数 包含所有进程)

SQL> alter system set processes=100 scope=spfile;

SQL> startup force

测试(如果设置 processes 为10 结果的情况?)

SQL> select count(*) from v$process;

查看当前启动的进程(每个会话为一个进程)

用户会话限制

1.创建配置文件 限制用户3个会话

SQL> create profile PRO_TEST limit

  2  sessions_per_user 3;

SQL> alter user SCOTT profile PRO_TEST;(给scott用户应用)

SQL> show parameter limit(限制默认为关闭状态)

SQL> alter system set resource_limit=true; 打开限制

SQL> show parameter limit

4.[oracle@sq ~]$ sqlplus /nolog

SQL> conn scott/abc  (操作三次)

5.第四次

ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

SQL> alter profile PRO_TEST limit idle_time 1;(限制会话空闲1分钟 断开)

SQL> drop profile PRO_TEST cascade;(已分配的profile，删除时必须加cascade选项,DEFAULT为默认profile，不能删除)

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

profile 里参数

LOGICAL_READS_PER_CALL：指定一次执行SQL（解析、执行和提取）调用所允许读的数据块的最大数目。

CONNECT_TIME：指定会话的总的连接时间。（单位：分钟）

PRIVATE_SGA：指定一个会话可以在共享池（SGA）中所允许分配的最大空间（单位：字节）

注：该限制只在使用共享服务器结构时才有效，会话在SGA中的私有空间包括私有的SQL和PL/SQL，但不包括共享的SQL和PL/SQL

PASSWORD_LIFE_TIME:指定同一密码所允许使用的天数。

password_reuse_time指定了密码不能重用前的天数

password_reuse_max则指定了当前密码被重用之前密码改变的次数

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

删除icbc 并把用户下所有对象

SQL> drop user icbc cascade;

SQL> select * from dba_users;

查看表空间配额

SQL> select * from dba_ts_quotas;(dba不受配额限制)

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

用户角色

1.预定义角色

CONNECT, RESOURCE, DBA

DBA: 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。

RESOURCE(资源):拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。

CONNECT:拥有Connect权限的用户只可以登录Oracle，

SQL> grant dba to scott;

SQL> revoke dba from scott;(回收角色)

查询用户拥有哪里权限

select * from dba_role_privs;(查看哪个用户创建的角色)

select * from dba_sys_privs; (查看用户权限)

select * from role_sys_privs;(查看角色中的权限)

2.实体权限

select, update, insert, alter, index, delete, all

3.自建立角色

SQL> create role role1;

给角色赋权限（创建表和创建储存过程）

SQL> grant create any table,create procedure to role1;

把角色 赋予用户

SQL> grant role1 to scott;

删除角色

SQL> drop role role1;

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

验证模式:

1.系统认证

2.密码文件认证

[oracle@oracle1 admin]$ vi sqlnet.ora 

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

此时本地sys用户的认证方式为系统认证，即可以 sqlplus / as sysdba 方式登录

如果在sqlnet.ora 中额外添加：

  SQLNET.AUTHENTICATION_SERVICES= (NTS)   或  SQLNET.AUTHENTICATION_SERVICES= (NONE)

  NTS:为windows专用     linux用 NONE

把SQLNET.AUTHENTICATION_SERVICES= (NONE) 添加到NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)上面

再次登陆时 sys需要密码

     本文转自陈继松 51CTO博客，原文链接：http://blog.51cto.com/chenjisong/1737388，如需转载请自行联系原作者