联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
一、profile管理
1、profile创建
create profile fei_profile limit sessions_per_user 1 cpu_per_session 30000 cpu_per_call 600 connect_time 300 idle_time 60 logical_reads_per_session 300 logical_reads_per_call 100 failed_login_attempts 2 password_life_time 1 password_reuse_time 3 password_reuse_max 10 password_lock_time 1;
2、修改profile
alter profile fei_profile limit connect_time 400 idle_time 200;
3、删除profile
drop profile fei_profile;
二、创建用户
1、准备工作
--查看表空间 select tablespace_name,contents from dba_tablespaces; --查看默认临时表空间 select * from database_properties where property_name like '%TEMP_TABLE%';
2、创建用户
2.1数据库认证
create user abc identified by abc default tablespace abc temporary tablespace temp quota 10m on abc quota 5m on users password expire account lock;
2.2、操作系统认证
--查看os_authent_prefix（前缀+操作系统用户名=数据库登陆名） show parameter os_aut--查看结果为：OPS$ --创建用户 create user OPS$ff identified externally default tablespace abc;
三、修改用户
alter user abc account unlock;
四、删除用户
drop user abc cascade;--表示删除该用户下的所有objects
五、授权和回收
5.1、system级别权限
grant create session,create table to abc with admin option;--可以传递该授权（续传的权限不会被回收） revoke create session,create table from abc; grant create session,create table to fei identified by fei; --用户不存在直接创建该用户，存在则修改用户密码
5.1.1权限类型
select * from session_privs;--查看本用户所具有的权限 ALTER SYSTEM AUDIT SYSTEM CREATE SESSION ALTER SESSION RESTRICTED SESSION CREATE TABLESPACE ALTER TABLESPACE MANAGE TABLESPACE DROP TABLESPACE UNLIMITED TABLESPACE CREATE USER BECOME USER ALTER USER DROP USER CREATE ROLLBACK SEGMENT ALTER ROLLBACK SEGMENT DROP ROLLBACK SEGMENT CREATE TABLE CREATE ANY TABLE ALTER ANY TABLE BACKUP ANY TABLE DROP ANY TABLE LOCK ANY TABLE COMMENT ANY TABLE SELECT ANY TABLE INSERT ANY TABLE UPDATE ANY TABLE DELETE ANY TABLE CREATE CLUSTER CREATE ANY CLUSTER ALTER ANY CLUSTER DROP ANY CLUSTER CREATE ANY INDEX ALTER ANY INDEX DROP ANY INDEX CREATE SYNONYM CREATE ANY SYNONYM DROP ANY SYNONYM SYSDBA SYSOPER CREATE PUBLIC SYNONYM DROP PUBLIC SYNONYM CREATE VIEW CREATE ANY VIEW DROP ANY VIEW CREATE SEQUENCE CREATE ANY SEQUENCE ALTER ANY SEQUENCE DROP ANY SEQUENCE SELECT ANY SEQUENCE CREATE DATABASE LINK CREATE PUBLIC DATABASE LINK DROP PUBLIC DATABASE LINK CREATE ROLE DROP ANY ROLE GRANT ANY ROLE ALTER ANY ROLE AUDIT ANY ALTER DATABASE FORCE TRANSACTION FORCE ANY TRANSACTION CREATE PROCEDURE CREATE ANY PROCEDURE ALTER ANY PROCEDURE DROP ANY PROCEDURE EXECUTE ANY PROCEDURE CREATE TRIGGER CREATE ANY TRIGGER ALTER ANY TRIGGER DROP ANY TRIGGER CREATE PROFILE ALTER PROFILE DROP PROFILE ALTER RESOURCE COST ANALYZE ANY GRANT ANY PRIVILEGE CREATE MATERIALIZED VIEW CREATE ANY MATERIALIZED VIEW ALTER ANY MATERIALIZED VIEW DROP ANY MATERIALIZED VIEW CREATE ANY DIRECTORY DROP ANY DIRECTORY CREATE TYPE CREATE ANY TYPE ALTER ANY TYPE DROP ANY TYPE EXECUTE ANY TYPE UNDER ANY TYPE CREATE LIBRARY CREATE ANY LIBRARY ALTER ANY LIBRARY DROP ANY LIBRARY EXECUTE ANY LIBRARY CREATE OPERATOR CREATE ANY OPERATOR ALTER ANY OPERATOR DROP ANY OPERATOR EXECUTE ANY OPERATOR CREATE INDEXTYPE CREATE ANY INDEXTYPE ALTER ANY INDEXTYPE DROP ANY INDEXTYPE UNDER ANY VIEW QUERY REWRITE GLOBAL QUERY REWRITE EXECUTE ANY INDEXTYPE UNDER ANY TABLE CREATE DIMENSION CREATE ANY DIMENSION ALTER ANY DIMENSION DROP ANY DIMENSION MANAGE ANY QUEUE ENQUEUE ANY QUEUE DEQUEUE ANY QUEUE CREATE ANY CONTEXT DROP ANY CONTEXT CREATE ANY OUTLINE ALTER ANY OUTLINE DROP ANY OUTLINE ADMINISTER RESOURCE MANAGER ADMINISTER DATABASE TRIGGER MERGE ANY VIEW ON COMMIT REFRESH EXEMPT ACCESS POLICY RESUMABLE SELECT ANY DICTIONARY DEBUG CONNECT SESSION DEBUG ANY PROCEDURE FLASHBACK ANY TABLE GRANT ANY OBJECT PRIVILEGE CREATE EVALUATION CONTEXT CREATE ANY EVALUATION CONTEXT ALTER ANY EVALUATION CONTEXT DROP ANY EVALUATION CONTEXT EXECUTE ANY EVALUATION CONTEXT CREATE RULE SET CREATE ANY RULE SET ALTER ANY RULE SET DROP ANY RULE SET EXECUTE ANY RULE SET EXPORT FULL DATABASE IMPORT FULL DATABASE CREATE RULE CREATE ANY RULE ALTER ANY RULE DROP ANY RULE EXECUTE ANY RULE ANALYZE ANY DICTIONARY ADVISOR CREATE JOB CREATE ANY JOB EXECUTE ANY PROGRAM EXECUTE ANY CLASS MANAGE SCHEDULER SELECT ANY TRANSACTION DROP ANY SQL PROFILE ALTER ANY SQL PROFILE ADMINISTER SQL TUNING SET ADMINISTER ANY SQL TUNING SET CREATE ANY SQL PROFILE EXEMPT IDENTITY POLICY MANAGE FILE GROUP MANAGE ANY FILE GROUP READ ANY FILE GROUP CHANGE NOTIFICATION CREATE EXTERNAL JOB --note：如果授权select any table 在默认情况下不能访问数据字典（O7_DICTIONARY_ACCESSIBILITY=false） --如果修改O7_DICTIONARY_ACCESSIBILITY=true则可以访问数据字典
5.2、object级别授权与回收
grant select,update,delete on abc.abc_a to abc with grant option;--权限可以传递（如果被回收，续传下去的权限一并回收） grant all on fei_a to abc;--把fei_a的所有相关操作授权给abc revoke select,update,delete on abc.abc_a from abc; --note：update，insert可以指定表的列，而select不行，只能通过view实现限制功能
六、role的管理
6.1、role的创建
create role r_a; create role r_b identified by r_b;
6.2/授权
grant create session to r_a; grant create table to r_b; grant r_a,r_b to b;--b为用户，把role授权给用户
6.3、查看role相关信息
select * from role_tab_privs where ROLE LIKE 'R_%'; SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE LIKE 'R_%'; SELECT * FROM ROLE_ROLE_privs where role like 'R_%';
6.4、设置default role 和enabl/disable role
alter user b default role r_a; set role r_a,r_b identified by r_b; --使用b用户登录，然后enable role r_a,r_b(注意密码) --没有在set中写出来的role，表似乎disable
