oracle9i学习笔记

*由于 O7_dictionary_accessiblity为静态参数，不能动态改变，故加scope=spfile,下次启动时才生效*/
alter system set O7_dictionary_accessiblity=true scope=spfile;

/*授予对象中的某些字段的权限，如select 某表中的某些字段的权限*/
grant [object_privs(column,....)],[...] on object_name to user/role/public,... with grant option;

/*oracle不允许授予select某列的权限,但可以授insert ,update某列的权限*/
grant insert(column_name1,column_name2,...) on table_name to user_name with grant option;

select * from dba_sys_privs/session_privs/dba_tab_privs/user_tab_privs/dba_col_privs/user_col_privs;

/*db/os/none 审计被记录在 数据库/操作系统/不审计 缺省是none*/
show parameter audit_trail;

/*启动对表的select动作*/
audit select on user.table_name by session;

/*by session在每个session中发出command只记录一次，by access则每个command都记录*/
audit [create table][select/update/insert on object by session/access][whenever successful/not successful];

desc dbms_fga;---进一步设计，则可使用dbms_fgs包

/*取消审计*/
noaudit select on user.table_name;

/*查被审计信息*/
select * from all_def_audit_opts/dba_stmt_audit_opts/dba_priv_audit_opts/dba_obj_audit_opts;

/*获取审计记录*/
select * from dba_audit_trail/dba_audit_exists/dba_audit_object/dba_audit_session/dba_audit_statement;

########### Managing Role #################

create role role_name; grant select on table_name to role_name; grant role_name to user_name; set role role_name;

create role role_name;
create role role_name identified by password;
create role role_name identified externally;

set role role_name ; ----激活role
set role role_name identified by password;

alter role role_name not identified;
alter role role_name identified by password;
alter role role_name identified externally;

grant priv_name to role_name [W99vH ADMIN OPTION];
grant update(column_name1,col_name2,...) on table_name to role_name;
grant role_name1 to role_name2;

/*建立default role,用户登录时，缺省激活default role*/
alter user user_name default role role_name1,role_name2,...;
alter user user_name default role all;
alter user user_name default role all except role_name1,...;
alter user user_name default role none;

set role role1 [identified by password],role2,....;
set role all;
set role except role1,role2,...;
set role none;

revoke role_name from user_name;
revoke role_name from public;

drop role role_name;

select * from dba_roles/dba_role_privs/role_role_privs/dba_sys_privs/role_sys_privs/role_tab_privs/session_roles;

########### Basic SQL SELECT ################

select col_name as col_alias from table_name ;

select col_name from table_name where col1 like '_o%'; ----'_'匹配单个字符

/*使用字符函数(右边截取,字段中包含某个字符,左边填充某字符到固定位数,右边填充某字符到固定位数)*/
select substr(col1,-3,5),instr(col2,'g'),LPAD(col3,10,'$'),RPAD(col4,10,'%') from table_name;

/*使用数字函数(往右/左几位四舍五入,取整,取余)*/

上一页  [1] [2] [3] [4] [5] [6] [7] [8] [9] [10] 下一页    >>