oracle怎么建profile,学习笔记:Oracle用户管理 DBA常用profile管理 创建 授权 删除用户操作案例...

天萃荷净

oracle之user 管理，汇总运维DBA日常工作中对Oracle user管理命令，如：profile管理、创建、授权、删除用户等操作案例

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

二、创建Oracle user用户

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;

三、修改解锁Oracle user用户

alter user abc account unlock;

四、删除用户

drop user abc cascade;--表示删除该用户下的所有objects

五、Oracle授权和回收用户权限

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实现限制功能

六、Oracle用户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

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之学习笔记:Oracle用户管理 DBA常用profile管理 创建 授权 删除用户操作案例