数据库权限实验

SQL> alter table reader enable row movement;

Table altered

SQL> flashback table reader to timestamp to_timestamp('2018-11-23
19:00','YYYY-MM-DD:HH24:MI');

Done

创建用户

SQL> create user huyao identified by huyaoyao;

User created

分配表空间
SQL> alter user huyao default tablespace data166;

User altered

拥有角色
SQL> grant connect, resource to huyao;

Grant succeeded

添加权限

SQL> grant select,update,delete,insert on S5120165564.book to huyao;

Grant succeeded

SQL> grant select,update,delete,insert on S5120165564.bookdetail to huyao;

Grant succeeded

SQL> grant select,update,delete,insert on S5120165564.cat to huyao;

Grant succeeded
SQL> grant select,update,delete,insert on S5120165564.fine to huyao;

Grant succeeded

SQL> grant select,update,delete,insert on S5120165564.lend to huyao;

Grant succeeded

SQL> grant select,update,delete,insert on S5120165564.reader to huyao;

Grant succeeded

查看用户拥有的...
直接看

SQL> create role huyao_role;

Role created

#权限
SQL> grant execute on S5120165564.bookingbook to huyao_role;

Grant succeeded

SQL> grant execute on S5120165564.lendbook to huyao_role;

Grant succeeded

SQL> grant execute on S5120165564.sendback to huyao_role;

Grant succeeded

#connect权限
SQL> grant connect to huyao_role;

Grant succeeded

# 用户_oper
SQL> create user huyao_oper identified by huyaoyao;

User created

SQL> grant huyao_role to huyao_oper;

Grant succeeded

借还书
SQL> call S5120165564.lendBook('20151001','2001232');

Method called

SQL> call S5120165564.sendback('20151001', '2001232',NULL);

Method called

9.创建视图

SQL> create or replace view view_reader
  2  as
  3  select bookdetail.isbn,bookdetail.bname, bookdetail.bauther, bookdetail.bpublish, cat.cname from cat, bookdetail where cat.cid = bookdetail.cid;

View created

10创建用户有视图权限
SQL> create user huyao_user1 identified by huyaoyao;

User created

SQL> grant select on S5120165564.view_reader to huyao_user1;

Grant succeeded

11.创建锁
SQL> create profile login_hy_lock limit failed_login_attempts 3;

Profile created

SQL> alter user huyao_user1 profile login_lock;

User altered

12.解锁
SQL> alter user huyao_user1 account unlock;

User altered

13. 撤销删除
SQL> revoke select on S5120165564.view_reader from huyao_user1;

Revoke succeeded