oracle之user 管理

一、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