10g-11g DBA 基本操作3

1 Oracle 系统用户与权限管理

本章基本操作需要采用 connect sys as sysdba 进行登录，在自己的笔记本上确保数据库已经启动成功才能进行登录：

C:\\>sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 11月 2 09:40:16 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

输入口令:(按\键即可，不需要输入口令)

1.1 Oracle 系统默认用户与一般维护

1.1.1

提醒：查询所有数据字典信息，要以sysdba 连接 -- 版本：9i/10g/11g

C:\\>sqlplus sys as sysdba … …

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

--用户信息数据字典为DBA_USERS SQL>desc dba_users

SQL> desc dba_users

名称 是否为空? 类型

----------------------------------------- -------- --------------- USERNAME NOT NULL VARCHAR2(30) USER_ID NOT NULL NUMBER PASSWORD VARCHAR2(30) ACCOUNT_STATUS NOT NULL VARCHAR2(32) LOCK_DATE DATE EXPIRY_DATE DATE

DEFAULT_TABLESPACE NOT NULL VARCHAR2(30) TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)

查询默认用户信息

CREATED NOT NULL DATE

PROFILE NOT NULL VARCHAR2(30) INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30) EXTERNAL_NAME VARCHAR2(4000) PASSWORD_VERSIONS VARCHAR2(8) EDITIONS_ENABLED VARCHAR2(1)

1.1.2

查询被锁定的用户

SQL> connect system/zhaoabc@orcl 已连接。

select username, lock_date from dba_users

where lock_date is not null;

1.1.3 用户被锁定与解锁

? 连接到SYS或SYSTEM 看到： --

--10g 11g 版本： --

--用户被锁定后，登录到Oracle实例，系统会提示：ORA-28000错误： --

SQL> connect SCOTT/tiger@orcl ERROR:

ORA-28000: the account is locked

警告: 您不再连接到 ORACLE。

SQL> connect system/zhaoabc@orcl 已连接。

SQL> alter user scott account unlock;

用户已更改。

1.1.4 如何修改用户口令

? 连接到SYS或SYSTEM 可修改某个用户的口令及其权限等：

ALTER USER… 和GRANT都可修改用户口令:

--版本：9i/10g/11g

alter user SCOTT identified by SCOTT_123; --

GRANG connect to SCOTT IDENTIFIED BY SCOTT_123 ; --

1.2 创建Oracle系统用户与授权

1.2.1 创建Oracle系统用户与授权

连接到SYS或SYSTEM 可创建用户与授权，如：

--版本：9i/10g/11g SQL> conn / as sysdba --

SQL> show user USER 为 \--

--创建用户时，用户名称和口令要符合Oracle系统的命名规则，否则创建失败： --(必须是字母数字型)

SQL> create user 123abc identified by zhao; create user 123abc identified by zhao *

第 1 行出现错误:

ORA-01935: 缺失用户或角色名

SQL> create user abc123 identified by zhao;

用户已创建。

SQL> grant connect to abc123;

授权成功。

SQL> grant resource to abc123;

授权成功。

SQL> spoo off

1.2.2 查询用户默认的永久与临时表空间信息

以sysdba 连接，可查询用户的所有信息：

-- 版本：9i/10g/11g --

SQL> connect sys/zhaoabc@orcl as sysdba

已连接。 --

Select USERNAME, LOCK_DATE, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE FROM DBA_USERS;

1.2.3 查询用户可对那些表拥有操作权限

? 以DBA登录到Oracle实例，可查询角色信息 ，如：

--版本：9i/10g/11g SQL> conn / as sysdba

select b.owner || '.' || b.table_name obj, b.privilege what_granted, b.grantable, a.username from sys.dba_users a, sys.dba_tab_privs b where a.username = b.grantee order by 1,2,3;

1.2.4 查询用户与角色信息

? 以DBA登录到Oracle实例，可查询角色信息 ，如：

--版本：9i/10g/11g select b.privilege what_granted, b.admin_option, a.username from sys.dba_users a, sys.dba_sys_privs b where a.username = b.grantee order by 1,2;

1.3 Oracle系统审计

1.3.1 审计环境确认

? 以DBA登录到Oracle实例，用如下命令对审计是否已经安装进行确认：

--版本：9i/10g/11g SQL> conn / as sysdba SQL> col value for a40

SQL> select value from v$parameter where name='audit_trail';

VALUE

---------------------------------------- DB

--如果没有找到这个结果，则没有安装审计，需要运行： $ORACLE_HOME/rdbms/audit/

--下面查询审计文件所在OS路径

select value from v$parameter where name='audit_file_dest'; --一般查询结果为：

SQL> select value from v$parameter where name='audit_file_dest';

VALUE

---------------------------------------- C:\\APP\\ZHAO\\ADMIN\\ORCL\\ADUMP

1.3.2

审计参数设置

? 以DBA登录，用如下命令对审计参数AUDIT_TRAIL进行设置，可能的值： ? DB/TRUE ? OS

? NONE/FALSE

--版本：9i/10g/11g --

sql>connect / as sysdba;

SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;

System altered.

SQL> SHUTDOWN Database closed.

Database dismounted.

ORACLE instance shut down. SQL> STARTUP

ORACLE instance started.

Total System Global Area 289406976 bytes Fixed Size 1248600 bytes Variable Size 71303848 bytes Database Buffers 213909504 bytes Redo Buffers 2945024 bytes Database mounted. Database opened.

1.3.3

审计激活

? 以DBA登录，设置审计：

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?

SYS.AUD$

sql>connect / as sysdba;

SQL> select * from AUDIT_ACTIONS;

audit create session by access; audit audit system by access;

audit grant any privilege by access;

audit grant any object privilege by access; audit grant any role by access; audit create user by access;

audit create any table by access;

audit create public database link by access; audit create any procedure by access; audit alter user by access;

audit alter any table by access;

audit alter any procedure by access; audit alter database by access; audit alter system by access; audit alter profile by access; audit drop user by access;

audit drop any procedure by access; audit drop any table by access; audit drop profile by access;

ACTION NAME

---------- ---------------------------- 0 UNKNOWN

1 CREATE TABLE 2 INSERT 3 SELECT …

218 CREATE FLASHBACK ARCHIVE 219 ALTER FLASHBACK ARCHIVE 220 DROP FLASHBACK ARCHIVE 225 ALTER DATABASE LINK

305 ALTER PUBLIC DATABASE LINK

已选择177行。

AUDIT SESSION BY JOHN, ALEX WHENEVER NOT SUCCESSFUL; AUDIT UPDATE, DELETE ON scott.emp BY ACCESS; NOAUDIT UPDATE, DELETE ON scott.emp;

1.3.4

审计信息查询

? 以DBA登录，查询数据字典：SYS_AUD$

--SYS.AUD$

sql>connect / as sysdba; SQL> desc aud$

Name Null? Type

----------------------------------------- -------- -------------- SESSIONID NOT NULL NUMBER ENTRYID NOT NULL NUMBER STATEMENT NOT NULL NUMBER TIMESTAMP# NOT NULL DATE

USERID VARCHAR2(30) USERHOST VARCHAR2(128) TERMINAL VARCHAR2(255) ACTION# NOT NULL NUMBER RETURNCODE NOT NULL NUMBER

OBJ$CREATOR VARCHAR2(30) OBJ$NAME VARCHAR2(128) AUTH$PRIVILEGES VARCHAR2(16)

AUTH$GRANTEE VARCHAR2(30) NEW$OWNER VARCHAR2(30) NEW$NAME VARCHAR2(128) SES$ACTIONS VARCHAR2(19) SES$TID NUMBER LOGOFF$LREAD NUMBER LOGOFF$PREAD NUMBER LOGOFF$LWRITE NUMBER LOGOFF$DEAD NUMBER LOGOFF$TIME DATE

COMMENT$TEXT VARCHAR2(4000) SPARE1 VARCHAR2(255) SPARE2 NUMBER OBJ$LABEL RAW(255) SES$LABEL RAW(255) PRIV$USED NUMBER

SQL> audit index by SCOTT; Audit succeeded.

--在HR.JOBS表上创建了一个索引： SQL> create index job_title_idx on hr.jobs(job_title);Index created. --查询DBA_AUDIT_TRAIL中的审计跟踪，可看到SCOTT创建索引：

SQL> select username, to_char(timestamp,'MM/DD/YY HH24:MI') Timestamp,2 obj_name, action_name, sql_text from dba_audit_trail3 where username = ‘SCOTT';

USERNAME TIMESTAMP OBJ_NAME ACTION_NAME SQL_TEXT

--------- -------------- -------------- -------------- ---------------- SCOTT 10/04/07 15:15 JOB_TITLE_IDX CREATE INDEX create index hr.job_title_idx onhr.jobs(job_title)1 row selected. --

SQL> noaudit index by SCOTT; Noaudit succeeded.

1.3.5

审计信息的维护

? 以DBA登录到Oracle实例，用如下命令对审计是否已经安装进行确认：

--版本：9i/10g/11g

1.4 Oracle安全高级用法

1.4.1 创建Profile与用户

--版本：11g

SQL> conn / as sysdba

CREATE PROFILE app_user LIMIT

SESSIONS_PER_USER UNLIMITED CPU_PER_SESSION UNLIMITED CPU_PER_CALL 3000 CONNECT_TIME 45

LOGICAL_READS_PER_SESSION DEFAULT LOGICAL_READS_PER_CALL 1000 PRIVATE_SGA 15K

COMPOSITE_LIMIT 5000000;

--创建用户：

CREATE USER USER01 IDENTIFIED BY out_standing1 DEFAULT TABLESPACE USERS QUOTA 10M ON USERS

TEMPORARY TABLESPACE temp PROFILE app_user PASSWORD EXPIRE;

1.4.2 限制用户使用表空间

SQL>SQLPLUS SYSTEM/password --

SQL>ALTER USER ZHAO QUOTA 100M ON USERS; --

SQL>ALTER USER ZHAO QUOTA 0M ON SYSTEM;

1.4.3 授予哪些权限给Public

--版本：11g

SQL> conn / as sysdba SELECT table_name

FROM dba_tab_privs

WHERE grantee = 'PUBLIC' AND owner = 'SYS'

AND PRIVILEGE = 'EXECUTE' AND table_name LIKE 'DBMS%' OR table_name LIKE 'UTL%' ORDER BY 1;

1.4.4 查询用户的权限元数据

--版本：11g

SQL> conn / as sysdba SET LONG 1000000

SELECT dbms_metadata.get_ddl(object_type, object_name) FROM user_objects WHERE object_name = 'ABC'; --

CREATE USER PLSQL_USER IDENTIFIED BY PLSQL_USER; GRANT CONNECT, RESOURCE, DBA TO PLSQL_USER; GRANT SELECT ON SCOTT.EMP TO PLSQL_USER;

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','PLSQL_USER') FROM DUAL; --

GRANT UNLIMITED TABLESPACE TO PLSQL_USER --

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','PLSQL_USER') FROM DUAL;

GRANT CONNECT TO PLSQL_USER GRANT RESOURCE TO PLSQL_USER GRANT DBA TO PLSQL_USER

1.4.5 11g口令大小写敏感参数

--版本：11g

SQL> conn / as sysdba

SQL> show parameter sec_case_sensitive_logon

NAME_COL_PLUS_SHOW_PARAM

--------------------------------------------

TYPE

-----------

VALUE_COL_PLUS_SHOW_PARAM

-------------------------------------------- sec_case_sensitive_logon boolean TRUE --

SELECT username, password_versions FROM dba_users

WHERE username='SCOTT';

USERNAME PASSWORD

------------------------------ -------- SCOTT 10G 11G --

ALTER SYSTEM set sec_case_sensitive_logon = false;

1.4.6 11g查询使用默认口令的用户

--版本：11g

SQL> conn / as sysdba

SQL> SELECT * FROM DBA_USERS_WITH_DEFPWD;

USERNAME

------------------------------ DIP MDSYS WK_TEST CTXSYS OLAPSYS OUTLN EXFSYS SCOTT

1.4.7 USER vs SCHEMA

--版本：11g

SQL> conn / as sysdba --

SQL> show user

USER 为 \

select user, schemaname from sys.gv_$session

where sid =(select sid from sys.gv$mystat where rownum = 1);

USER SCHEMANAME

------------------------------------------------------ ------------------------ SYS SYS

select count(*) from dict;

COUNT(*) ---------- 2553

alter session set current_schema = system;

会话已更改。

select user, schemaname from sys.gv_$session

where sid =(select sid from sys.gv$mystat where rownum = 1);

USER SCHEMANAME

----------------------------- --------------------------- SYS SYSTEM

select count(*) from dict;

COUNT(*) ---------- 2553

alter session set current_schema = SCOTT;

会话已更改。

select count(*) from dict;

COUNT(*) ---------- 2553

SQL> show user

USER 为 \

SQL> select user, schemaname 2 from sys.gv_$session

3 where sid =(select sid from sys.gv$mystat where rownum = 1);

USER SCHEMANAME

----------------------------------- ---------------------------------- SYS SCOTT

SQL> create table my_tab ( name varchar2(20),sal number(9,2))tablespace users;

表已创建。

select owner,table_name,tablespace_name from dba_tables where table_name ='MY_TAB';

OWNER TABLE_NAME TABLESPACE_NAME

--------------------------- ---------------------- ----------------------------- SCOTT MY_TAB USERS

1.4.8 显示当前用户拥有的权限

SQL> show user

USER 为 \

SQL> select * from session_privs;

PRIVILEGE

--------------------------------------------------------------- CREATE SESSION

UNLIMITED TABLESPACE CREATE TABLE CREATE CLUSTER CREATE SEQUENCE CREATE PROCEDURE CREATE TRIGGER CREATE TYPE

CREATE OPERATOR CREATE INDEXTYPE

本文来源：https://www.bwwdw.com/article/v7yr.html