37.管理用户安全性
1.管理用户安全性
--创建用户
create user YZJ identifid by 'YZJ123';
default tablespace '表空间名字' --如果不指定，默认使用用户表空间
default temporary tablespace '表空间名字' --如果不指定，默认使用临时表空间
profile '资源概要文件' --如果没有指定，默认使用default
sys@ORCL 2023-03-04 09:38:04> select * from database_properties where PROPERTY_NAME like 'DEFAULT%';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ----------------------------------- -------------------------------------------------------
DEFAULT_TBS_TYPE SMALLFILE Default tablespace type
DEFAULT_EDITION ORA$BASE Name of the database default edition
DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
Elapsed: 00:00:00.00
sys@ORCL 2023-03-04 10:15:18> col username for a25;
sys@ORCL 2023-03-04 10:15:29> r
1* select username,default_tablespace,temporary_tablespace from dba_users where username = 'YZJ'
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------- ------------------------------ ------------------------------
YZJ USERS TEMP
--每个程序，分配独立的用户表空间，独立的临时表空间，独立用户
sys@ORCL 2023-03-04 10:16:51> create tablespace YZJ datafile 'YZJ01.DBF' size 10M autoextend on maxsize 1024m;
Tablespace created.
Elapsed: 00:00:00.08
sys@ORCL 2023-03-04 10:17:32> create temporary tablespace YZJ_TEMP tempfile 'YZJ_TEMP01.DBF' size 10M autoextend on maxsize 1024m uniform size 1m;
Tablespace created.
Elapsed: 00:00:00.02
sys@ORCL 2023-03-04 10:20:05> create user YZJ identified by YZJ123 default tablespace YZJ temporary tablespace YZJ_TEMP ;
User created.
Elapsed: 00:00:00.02
sys@ORCL 2023-03-04 10:21:27> select username,default_tablespace,temporary_tablespace,account_status from dba_users where username = 'YZJ';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ACCOUNT_STATUS
------------------------- ------------------------------ ------------------------------ --------------------------------
YZJ YZJ YZJ_TEMP OPEN
Elapsed: 00:00:00.012.权限管理
[root@yuanzj ~]# useradd admin -g oinstall -G oinstall
[root@yuanzj ~]# id admin
uid=54322(admin) gid=54321(oinstall) groups=54321(oinstall)
[root@yuanzj ~]#
[root@yuanzj ~]# su - admin
[admin@yuanzj ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
[admin@yuanzj ~]$ export ORACLE_SID=orcl
[admin@yuanzj ~]$ /u01/app/oracle/product/19.3.0/dbhome_1/bin/sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 4 10:53:43 2023
Version 19.18.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[admin@yuanzj ~]$ /u01/app/oracle/product/19.3.0/dbhome_1/bin/sqlplus sys/Oracle_4U as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 4 10:54:09 2023
Version 19.18.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.18.0.0.0
sys@ORCL 2023-03-04 10:54:09>
--新建用户admin，必须拥有oinstall组
--新建用户admin，不能使用os认证登录数据库，需要使用账号，密码登录数据库，如果想用admin登录，需要登录数据库创建外部用户
sys@ORCL 2023-03-04 11:05:55> create user ops$admin identified externally;
User created.
Elapsed: 00:00:00.15
--远程操作系统认证
sys@ORCL 2023-03-04 11:10:44> show parameter remote;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_dependencies_mode string TIMESTAMP
remote_listener string
remote_login_passwordfile string EXCLUSIVE
remote_os_authent boolean FALSE
remote_os_roles boolean FALSE
remote_recovery_file_dest string
result_cache_remote_expiration integer 0
sys@ORCL 2023-03-04 11:10:55> alter system set remote_os_authent=true scope=spfile;
System altered.
Elapsed: 00:00:00.01
sys@ORCL 2023-03-04 11:11:34> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORCL 2023-03-04 11:12:04> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1191178264 bytes
Fixed Size 9162776 bytes
Variable Size 352321536 bytes
Database Buffers 822083584 bytes
Redo Buffers 7610368 bytes
Database mounted.
Database opened.
sys@ORCL 2023-03-04 11:12:18>
--只要使用admin用户，就可以远程连接到数据库里面
--建立用户，授权，回收权限系统权限
grant connect，resource to YZJ identified by YZJ123;
sys@ORCL 2023-03-04 11:23:13> grant create tablespace to A2;
Grant succeeded.
Elapsed: 00:00:00.01
sys@ORCL 2023-03-04 11:23:58>
sys@ORCL 2023-03-04 11:23:59> select 'revoke ' || PRIVILEGE || ' from ' || grantee || ';' from dba_sys_privs where grantee = 'A2';
'REVOKE'||PRIVILEGE||'FROM'||GRANTEE||';'
--------------------------------------------------------
revoke CREATE TABLESPACE from A2;
Elapsed: 00:00:00.01
--授权，回收对象权限
sys@ORCL 2023-03-04 11:24:02> grant select on scott.emp to A2;
Grant succeeded.
Elapsed: 00:00:00.02
sys@ORCL 2023-03-04 11:25:04> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
Elapsed: 00:00:00.01
sys@ORCL 2023-03-04 11:28:36> select 'revoke ' || PRIVILEGE || ' on ' || owner||'.'||table_name|| ' from ' || grantee || ';' from dba_tab_privs where grantee = 'A2';
'REVOKE'||PRIVILEGE||'ON'||OWNER||'.'||TABLE_NAME||'FROM'||GRANTEE||';'
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
revoke SELECT on SCOTT.EMP from A2;
Elapsed: 00:00:00.003.资源概要文件
sys@ORCL10G 2023-03-04 13:37:26> select * from dba_profiles where profile like 'DEFAUL%';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED
--1.拷贝资源概要文件
cp /u01/app/ora10g/product/10.2.0/db_1/rdbms/admin/utlpwdmg.sql .
--2.修改资源概要文件
vi utlpwdmg.sql
[oracle@yuanzj.com:/home/oracle]$ cat utlpwdmg.sql
Rem
Rem $Header: utlpwdmg.sql 31-aug-2000.11:00:47 nireland Exp $
Rem
Rem utlpwdmg.sql
Rem
Rem Copyright (c) Oracle Corporation 1996, 2000. All Rights Reserved.
Rem
Rem NAME
Rem utlpwdmg.sql - script for Default Password Resource Limits
Rem
Rem DESCRIPTION
Rem This is a script for enabling the password management features
Rem by setting the default password resource limits.
Rem
Rem NOTES
Rem This file contains a function for minimum checking of password
Rem complexity. This is more of a sample function that the customer
Rem can use to develop the function for actual complexity checks that the
Rem customer wants to make on the new password.
Rem
Rem MODIFIED (MM/DD/YY)
Rem nireland 08/31/00 - Improve check for username=password. #1390553
Rem nireland 06/28/00 - Fix null old password test. #1341892
Rem asurpur 04/17/97 - Fix for bug479763
Rem asurpur 12/12/96 - Changing the name of password_verify_function
Rem asurpur 05/30/96 - New script for default password management
Rem asurpur 05/30/96 - Created
Rem
-- This script sets the default password resource parameters
-- This script needs to be run to enable the password features.
-- However the default resource parameters can be changed based
-- on the need.
-- A default password complexity function is also provided.
-- This function makes the minimum complexity checks like
-- the minimum length of the password, password not same as the
-- username, etc. The user may enhance this function according to
-- the need.
-- This function must be created in SYS schema.
-- connect sys/<password> as sysdba before running the script
CREATE OR REPLACE FUNCTION verify_function_a1
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
n boolean;
m integer;
differ integer;
isdigit boolean;
ischar boolean;
ispunct boolean;
digitarray varchar2(20);
punctarray varchar2(25);
chararray varchar2(52);
BEGIN
digitarray:= '0123456789';
chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
punctarray:='!"#$%&()``*+,-/:;<=>?_';
-- Check if the password is same as the username
IF NLS_LOWER(password) = NLS_LOWER(username) THEN
raise_application_error(-20001, 'Password same as or similar to user');
END IF;
-- Check for the minimum length of the password
IF length(password) < 4 THEN
raise_application_error(-20002, 'Password length less than 4');
END IF;
-- Check if the password is too simple. A dictionary of words may be
-- maintained and a check may be made so as not to allow the words
-- that are too simple for the password.
IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd') THEN
raise_application_error(-20002, 'Password too simple');
END IF;
-- Check if the password contains at least one letter, one digit and one
-- punctuation mark.
-- 1. Check for the digit
isdigit:=FALSE;
m := length(password);
FOR i IN 1..10 LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(digitarray,i,1) THEN
isdigit:=TRUE;
GOTO findchar;
END IF;
END LOOP;
END LOOP;
IF isdigit = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation');
END IF;
-- 2. Check for the character
<<findchar>>
ischar:=FALSE;
FOR i IN 1..length(chararray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(chararray,i,1) THEN
ischar:=TRUE;
GOTO findpunct;
END IF;
END LOOP;
END LOOP;
IF ischar = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one \
digit, one character and one punctuation');
END IF;
-- 3. Check for the punctuation
<<findpunct>>
ispunct:=FALSE;
FOR i IN 1..length(punctarray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(punctarray,i,1) THEN
ispunct:=TRUE;
GOTO endsearch;
END IF;
END LOOP;
END LOOP;
IF ispunct = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one \
digit, one character and one punctuation');
END IF;
<<endsearch>>
-- Check if the password differs from the previous password by at least
-- 3 letters
IF old_password IS NOT NULL THEN
differ := length(old_password) - length(password);
IF abs(differ) < 3 THEN
IF length(password) < length(old_password) THEN
m := length(password);
ELSE
m := length(old_password);
END IF;
differ := abs(differ);
FOR i IN 1..m LOOP
IF substr(password,i,1) != substr(old_password,i,1) THEN
differ := differ + 1;
END IF;
END LOOP;
IF differ < 3 THEN
raise_application_error(-20004, 'Password should differ by at \
least 3 characters');
END IF;
END IF;
END IF;
-- Everything is fine; return TRUE ;
RETURN(TRUE);
END;
/
-- This script alters the default parameters for Password Management
-- This means that all the users on the system have Password Management
-- enabled and set to the following values unless another profile is
-- created with parameter values set to different value or UNLIMITED
-- is created and assigned to the user.
create PROFILE pa1 LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 2/1440
PASSWORD_VERIFY_FUNCTION verify_function_a1;
[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 10.2.0.5.0 - Production on Sat Mar 4 13:48:02 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
sys@ORCL10G 2023-03-04 13:48:02> @utlpwdmg
Function created.
Elapsed: 00:00:00.06
Profile created.
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-04 13:48:16>
sys@ORCL10G 2023-03-04 13:48:17> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 10.2.0.5.0 - Production on Sat Mar 4 13:51:52 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
sys@ORCL10G 2023-03-04 13:51:52>
sys@ORCL10G 2023-03-04 13:51:52> select username,profile from dba_users where username = 'A2';
USERNAME PROFILE
------------------------------ ------------------------------
A2 DEFAULT
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-04 13:51:58> alter user A2 profile pa1;
User altered.
Elapsed: 00:00:00.01
sys@ORCL10G 2023-03-04 13:52:24> select username,profile from dba_users where username = 'A2';
USERNAME PROFILE
------------------------------ ------------------------------
A2 PA1
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-04 13:52:26> conn A2/A123
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
sys@ORCL10G 2023-03-04 13:52:39> conn A2/A123
ERROR:
ORA-01017: invalid username/password; logon denied
sys@ORCL10G 2023-03-04 13:52:41> conn A2/A123
ERROR:
ORA-01017: invalid username/password; logon denied
sys@ORCL10G 2023-03-04 13:52:45> conn A2/A123
ERROR:
ORA-28000: the account is locked
sys@ORCL10G 2023-03-04 13:52:52> conn A2/A1234
ERROR:
ORA-28000: the account is locked
sys@ORCL10G 2023-03-04 13:52:57> alter user A2 account unlock;
SP2-0640: Not connected
sys@ORCL10G 2023-03-04 13:53:18> conn / as sysdba
Connected.
sys@ORCL10G 2023-03-04 13:53:26> alter user A2 account unlock;
User altered.
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-04 13:53:29> conn A2/A1234
Connected.
[oracle@yuanzj.com:/home/oracle]$ sqlplus A2/A12345
SQL*Plus: Release 10.2.0.5.0 - Production on Sat Mar 4 13:56:25 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
ERROR:
ORA-28000: the account is locked
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@yuanzj.com:/home/oracle]$
[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 10.2.0.5.0 - Production on Sat Mar 4 13:56:28 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
sys@ORCL10G 2023-03-04 13:56:28> alter user A2 account unlock;
User altered.
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-04 13:56:36> alter user A2 identified by A1234;
alter user A2 identified by A1234
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20003: Password should contain at least one \
digit, one character and one punctuation
Elapsed: 00:00:00.01
sys@ORCL10G 2023-03-04 13:56:50> alter user A2 identified by A12345;
alter user A2 identified by A12345
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20003: Password should contain at least one \
digit, one character and one punctuation
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-04 13:56:54> alter user A2 identified by A123456;
alter user A2 identified by A123456
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20003: Password should contain at least one \
digit, one character and one punctuation
Elapsed: 00:00:00.01
sys@ORCL10G 2023-03-04 13:56:56> alter user A2 identified by A12345#;
User altered.
Elapsed: 00:00:00.02
sys@ORCL10G 2023-03-04 13:57:06> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
[oracle@yuanzj.com:/home/oracle]$ sqlplus A2/A12345#
SQL*Plus: Release 10.2.0.5.0 - Production on Sat Mar 4 13:57:18 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options在Oracle11g，Oracle12c增加函数检查
4.Oracle 12c用户安全性的管理
sys@ORCL 2023-03-04 14:05:17> r
1* select username, COMMON from dba_users
USERNAME COM
------------------------- ---
SYS YES
SYSTEM YES
XS$NULL YES
OJVMSYS YES
LBACSYS YES
OUTLN YES
SYS$UMF YES
DBSNMP YES
APPQOSSYS YES
DBSFWUSER YES
GGSYS YES
ANONYMOUS YES
CTXSYS YES
SI_INFORMTN_SCHEMA YES
DVSYS YES
DVF YES
GSMADMIN_INTERNAL YES
ORDPLUGINS YES
MDSYS YES
OLAPSYS YES
ORDDATA YES
XDB YES
WMSYS YES
ORDSYS YES
GSMCATUSER YES
MDDATA YES
SYSBACKUP YES
REMOTE_SCHEDULER_AGENT YES
GSMUSER YES
SYSRAC YES
AUDSYS YES
DIP YES
SYSKM YES
ORACLE_OCM YES
SYSDG YES
35 rows selected.
Elapsed: 00:00:00.01
sys@ORCL 2023-03-04 14:05:18> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB01 READ WRITE NO
sys@ORCL 2023-03-04 14:05:24> alter session set container = ORCLPDB01;
Session altered.
Elapsed: 00:00:00.03
sys@ORCL 2023-03-04 14:05:37> select username, COMMON from dba_users;
USERNAME COM
------------------------- ---
SYS YES
SYSTEM YES
XS$NULL YES
LBACSYS YES
OUTLN YES
DBSNMP YES
APPQOSSYS YES
DBSFWUSER YES
GGSYS YES
ANONYMOUS YES
HR NO
CTXSYS YES
SI_INFORMTN_SCHEMA YES
DVSYS YES
DVF YES
GSMADMIN_INTERNAL YES
ORDPLUGINS YES
MDSYS YES
OLAPSYS YES
ORDDATA YES
XDB YES
WMSYS YES
ORDSYS YES
GSMCATUSER YES
MDDATA YES
SYSBACKUP YES
REMOTE_SCHEDULER_AGENT YES
PDBADMIN NO
GSMUSER YES
SYSRAC YES
OJVMSYS YES
AUDSYS YES
DIP YES
SYSKM YES
ORACLE_OCM YES
SYS$UMF YES
SCOTT NO
SYSDG YES
--common值是YES的，是数据库公有用户，是NO的是本地用户
--在cdb中建立用户必须使用c##开头，在pdb中建立用户，使用常规名字就行
sys@ORCL 2023-03-04 14:07:44> alter session set container = cdb$root;
Session altered.
Elapsed: 00:00:00.00
sys@ORCL 2023-03-04 14:07:55> create user c##YZJ identified by YZJ123;
User created.
Elapsed: 00:00:00.21
sys@ORCL 2023-03-04 14:08:19> alter session set container = ORCLPDB01;
Session altered.
Elapsed: 00:00:00.01
sys@ORCL 2023-03-04 14:08:25> create user YZJ identified by YZJ123;
User created.
Elapsed: 00:00:00.09
sys@ORCL 2023-03-04 14:16:31> create role c##role2 container = all;
Role created.
Elapsed: 00:00:00.06
sys@ORCL 2023-03-04 14:16:38> grant connect to c##role2;
Grant succeeded.
Elapsed: 00:00:00.02
sys@ORCL 2023-03-04 14:16:51> grant connect to c##role2 container = all;
Grant succeeded.
Elapsed: 00:00:00.04
sys@ORCL 2023-03-04 14:17:13> grant c##role2 to c##YZJ;
Grant succeeded.
Elapsed: 00:00:00.00
sys@ORCL 2023-03-04 14:17:41> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@yuanzj.com:/home/oracle]$ sqlplus c##YZJ/YZJ123
SQL*Plus: Release 12.2.0.1.0 Production on Sat Mar 4 14:18:04 2023
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
c##yzj@ORCL 2023-03-04 14:18:04> quit
--在容器书库里面建立角色和用户
--- cdb ---> c## container = all
--- pdb ---> container = current
--- pdb ---> dba_users ---> common = YES在cdb中授给所有容器，用ALL，使用current，只授给当前用户。
