OCP学习笔记

xiaodong_1567 · 发表于 2008-7-22 13:47

前半部分，和书上差不多

后半部分，有些自己的东西

大家多提提意见

xiaodong_1567 · 发表于 2008-7-22 13:48

用户管理
系统权限
conn / as sysdba;
create user t1 identified by t1;
create user t2 identified by t2;
grant connect to t1,t2;
grant create table, create view to t1 with admin option;
conn t1/t1
grant create table to t2;
conn / as sysdba;
select * from dba_sys_privs where grantee in('T1', 'T2');

GRANTEE                      PRIVILEGE                               ADM
------------------------------ ---------------------------------------- ---
T1                            CREATE TABLE                            YES
T1                            CREATE VIEW                            YES
T2                            CREATE TABLE                            NO

with admin option选项，可使被授予权限者有权转授其他人

revoke  create table, create view from t1;
select * from dba_sys_privs where grantee in('T1', 'T2');

GRANTEE                      PRIVILEGE                               ADM
------------------------------ ---------------------------------------- ---
T2                            CREATE TABLE                            NO

不会级联删除已经授予的系统权限
对象权限
conn test/test
grant select on t to t1 with grant option;
conn t1/t1
grant select on test.t to t2;
conn test/test
select * from dba_tab_privs where grantee in('T1', 'T2');

GRANTEE                      OWNER                         TABLE_NAME                   GRANTOR                      PRIVILEGE
------------------------------ ------------------------------ ------------------------------ ------- --------------------------------
T2                            TEST                         T                            T1                               SELECT
T1                            TEST                         T                            TEST                            SELECT

revoke select on t from t1;
select * from dba_tab_privs where grantee in('T1', 'T2');

SQL> select * from dba_tab_privs where grantee in('T1', 'T2');

未选定行

级联删除已经授予的对象权限

create role ttt;
grant create table, create user, create session to ttt with admin option;
grant select on test.t to ttt with grant option;

SQL> grant select on test.t to ttt with grant option;
grant select on test.t to ttt with grant option
*
第 1 行出现错误:
ORA-01926: 无法将 WITH GRANT OPTION GRANT 角色

无法使用with grant option选项给角色授权

grant select on test.t to ttt;
create user d identified by d;
create user e identified by e;
grant ttt to d with admin option;

conn d/d
grant ttt to e;
select * from test.t;

SQL> select * from test.t;

A                B
---------- ----------
测试             111

conn / as sysdba
select * from dba_role_privs where grantee in('D', 'E');

GRANTEE                      GRANTED_ROLE                ADM DEF
------------------------------ ------------------------------ --- ---
E                            TTT                         NO  YES
D                            TTT                         YES YES

drop role ttt;
select * from dba_role_privs where grantee in('D', 'E');

SQL> select * from dba_role_privs where grantee in('D', 'E');

未选定行

conn d/d

SQL> conn d/d
ERROR:
ORA-01045: 用户 D 没有 CREATE SESSION 权限; 登录被拒绝

角色级联删除
配置文件profile
drop profile test_profile cascade;
create profile test_profile LIMIT
SESSIONS_PER_USER 1
CPU_PER_CALL 10
CPU_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL 1000
LOGICAL_READS_PER_SESSION UNLIMITED
CONNECT_TIME 1000
IDLE_TIME 1;

select * from dba_profiles where profile ='TEST_PROFILE';

PROFILE                      RESOURCE_NAME                   RESOURCE LIMIT
------------------------------ -------------------------------- -------- ---------------------------
TEST_PROFILE                COMPOSITE_LIMIT                KERNEL DEFAULT
TEST_PROFILE                SESSIONS_PER_USER             KERNEL 1
TEST_PROFILE                CPU_PER_SESSION                KERNEL UNLIMITED
TEST_PROFILE                CPU_PER_CALL                   KERNEL 10
TEST_PROFILE                LOGICAL_READS_PER_SESSION       KERNEL UNLIMITED
TEST_PROFILE                LOGICAL_READS_PER_CALL          KERNEL 1000
TEST_PROFILE                IDLE_TIME                      KERNEL 1
TEST_PROFILE                CONNECT_TIME                   KERNEL 1000
TEST_PROFILE                PRIVATE_SGA                   KERNEL DEFAULT
TEST_PROFILE                FAILED_LOGIN_ATTEMPTS          PASSWORD DEFAULT
TEST_PROFILE                PASSWORD_LIFE_TIME             PASSWORD DEFAULT

PROFILE                      RESOURCE_NAME                   RESOURCE LIMIT
------------------------------ -------------------------------- -------- ---------------------------
TEST_PROFILE                PASSWORD_REUSE_TIME             PASSWORD DEFAULT
TEST_PROFILE                PASSWORD_REUSE_MAX             PASSWORD DEFAULT
TEST_PROFILE                PASSWORD_VERIFY_FUNCTION       PASSWORD DEFAULT
TEST_PROFILE                PASSWORD_LOCK_TIME             PASSWORD DEFAULT
TEST_PROFILE                PASSWORD_GRACE_TIME             PASSWORD DEFAULT

SESSIONS_PER_USER 用户最大并发会话数
CPU_PER_CALL 单语句的最大CPU时间，超过语句会终止（单位：百分之一秒）
LOGICAL_READS_PER_CALL 单语句的最大读取数据块数（包括读缓存和物理磁盘），没完成语句终止，数据回滚
CONNECT_TIME 连接的最长时间（单位：分钟）
IDLE_TIME 不执行任何数据活动的情况下保持连接的最长时间（单位：分钟）

alter user test profile test_profile;

为用户指定配置文件

show parameter resource_limit

NAME                               TYPE       VALUE
------------------------------------ ----------- ------------------------------
resource_limit                      boolean    FALSE

alter system set resource_limit = TRUE;

只有resource_limit为TRUE时，资源限制才有效

conn test/test

SQL> conn test/test
ERROR:
ORA-02391: 超出同时存在的 SESSIONS_PER_USER 限制

因为设定只能有一个会话，所以出错