Oracle日常运维（一线DBA必备技能）（三）

本篇介绍Oracle日常运维（一线DBA必备技能）之数据库链接设置，用户权限安全管理。

1. Oracle远程连接

(1) 本地系统验证(sys用户)
sqlplus / as sysdba
(2) 远程登录

netca
listener.ora
sqlplus system/oracle@192.168.56.100:1521/orcl
sqlplus sys/oracle@192.168.83.20:1521/orcldg as sysdba
tnsnames.ora
sqlplus system/oracle@test

1.1 lisener配置

[oracle@rac01 admin]$ more listener.ora
listener.ora Network Configuration File:
/u01/app/oracle/product/12.2.0/db_1/network/admin/listener.ora
Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/db)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/db)
(SID_NAME = orcldg)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.83.20)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle

1.2 启动监听
lsnrctl start
停止监听
lsnrctl stop
查看状态
lsnrctl status
监听文件的位置：
$ORACLE_HOME/network/admin/listener.ora；

1.2 tnsnames.ora配置

[oracle@rac01 admin]$ more tnsnames.ora
tnsnames.ora Network Configuration File:
/u01/app/oracle/product/12.2.0/db_1/network/admin/tnsnames.ora
Generated by Oracle configuration tools.
TESTDB_1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.60)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testdb)
)
)
orcl=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.83.20)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

2. 用户、角色、权限

创建用户
SQL> Create user [username] identified by [passwd] default tablespace users ;
查询用户默认表空间、临时表空间
SQL> select username, default_tablespace, temporary_tablespace from dba_users;
修改口令字：
SQL>Alter user test identified by “888888888”;
修改用户缺省表空间：
SQL> Alter user test default tablespace users;
修改用户临时表空间
SQL> Alter user test temporary tablespace temp_data;
强制用户修改口令字：
SQL> Alter user test password expire;

SQL>
SQL> conn test/“88888888”;
ERROR:
ORA-28001: the password has expired
Changing password for test
New password:
将用户加锁
SQL> Alter user test account lock; // 加锁
SQL> Alter user test account unlock; // 解锁
SQL> conn test/88888888
ERROR:
ORA-28000: The account is locked.
Warning: You are no longer connected to ORACLE.
删除用户
SQL> drop user 用户名; //用户没有实际数据
SQL> drop user 用户名 CASCADE; // 将用户及其关联数据全部删除
查询用户会话信息：
SQL> select username, sid, serial#, machine from vKaTeX parse error: Expected 'EOF', got '#' at position 63: …on 'sid, serial#̲'; Alter system…open_cursor where user_name not in
(‘SYS’,‘SYSTEM’);
select user_name, sql_text from v$ope{n}_{c}ursorwhereuse{r}_{n}ame{=}^{\prime }tes{t}^{\prime };selectuse{r}_{n}ame,sq{l}_{t}extfromv$open_cursor where user_name LIKE ‘%test%’;
创建用户及授权权限
SQL> Create user user50 identified by user50;
SQL> grant connect, resource,UNLIMITED TABLESPACE to user50;
SQL> Create user user60 identified by user60;
SQL> grant dba to user50;
建一个角色
sql>create role role1;
授权给角色
sql>grant create any table,create procedure to role1;
授予角色给用户
sql>grant role1 to user1;
查看角色所包含的权限
sql>select * from role_sys_privs;
DBA_ROLE_PRIVS
系统权限只能由DBA用户回收
Revoke connect, resource from user50;

本篇到此结束，关于用户权限管理知识，一线DBA掌握以上基本操作就能够应付平时95%以上的工作了，无需再去花时间精力去研究一些不常用到的操作。

码字不易，宝贵经验分享不易，请各位支持原创，转载注明出处，多多关注作者，后续不定期分享DB基本知识和排障案例及经验、性能调优等。