oracle常用命令大汇总(四)

第十章：managing privileges

    1.system privileges: view => system_privilege_map ,dba_sys_privs,session_privs

    2.grant system privilege

    sql> grant create session,create table to managers;

    sql> grant create session to scott with admin option;

    with admin option can grant or revoke privilege from any user or role;

    3.sysdba and sysoper privileges:

    sysoper: startup,shutdown,alter database open|mount,alter database backup controlfile,

    alter tablespace begin/end backup,recover database

    alter database archivelog,restricted session

    sysdba: sysoper privileges with admin option,create database,recover database until

    4.password file members: view:=> v$pwfile_users

    5.o7_dictionary_accessibility =true restriction access to view or tables in other schema

    6.revoke system privilege

    sql> revoke create table from karen;

    sql> revoke create session from scott;

    7.grant object privilege

    sql> grant execute on dbms_pipe to public;

    sql> grant update(first_name,salary) on employee to karen with grant option;

    8.display object privilege : view => dba_tab_privs, dba_col_privs

    9.revoke object privilege

    sql> revoke execute on dbms_pipe from scott [cascade constraints];

    10.audit record view :=> sys.aud$

    11. protecting the audit trail

    sql> audit delete on sys.aud$ by access;

    12.statement auditing

    sql> audit user;

    13.privilege auditing

    sql> audit select any table by summit by access;

    14.schema object auditing

    sql> audit lock on summit.employee by access whenever successful;

    15.view audit option : view=> all_def_audit_opts,dba_stmt_audit_opts,dba_priv_audit_opts,
dba_obj_audit_opts

    16.view audit result: view=> dba_audit_trail,dba_audit_exists,dba_audit_object,
dba_audit_session,dba_audit_statement



    第十一章： manager role

    1.create roles

    sql> create role sales_clerk;

    sql> create role hr_clerk identified by bonus;

    sql> create role hr_manager identified externally;

    2.modify role

    sql> alter role sales_clerk identified by commission;

    sql> alter role hr_clerk identified externally;

    sql> alter role hr_manager not identified;

    3.assigning roles

    sql> grant sales_clerk to scott;

    sql> grant hr_clerk to hr_manager;

    sql> grant hr_manager to scott with admin option;

    4.establish default role

    sql> alter user scott default role hr_clerk,sales_clerk;

    sql> alter user scott default role all;

    sql> alter user scott default role all except hr_clerk;

    sql> alter user scott default role none;

    5.enable and disable roles

    sql> set role hr_clerk;

    sql> set role sales_clerk identified by commission;

    sql> set role all except sales_clerk;

    sql> set role none;

    6.remove role from user

    sql> revoke sales_clerk from scott;

    sql> revoke hr_manager from public;

    7.remove role

    sql> drop role hr_manager;

    8.display role information

    view: =>dba_roles,dba_role_privs,role_role_privs,dba_sys_privs,
role_sys_privs,role_tab_privs,session_roles