Oracle Course archive, day ten

User Management

Authentication: Authentication

Aaa:
Authentication: Authentication

Authorization: Rights Management

Audition: Audit

Grant: Authorization

unset: Undo (Eliminate)

Sql>echo $ORACLE _sid ORCL query database is named ORCL

Sql>vnset ORACLE_SID Delete database name is ORCL

Sql>export ORACLE_SID=ORCL re-add the variable database name is ORCL

Authentication for administrators:
Local connection:
Local connection, pre-set ORACLE_SID, OS user is a member of DBA Group

Id
UID=1001 (Oracle) gid=1000 (oinstall) groups=1000 (Oinstall), 1031 (DBA), 1032 (Oper)
$ sqlplus/as SYSDBA
Sql> Show User
USER is "SYS"
$ Su-
# usermod-g Oper Oracle or-G: Additional Group-G: Primary Group-D: Delete-A: add
# gpasswd-d Oracle DBA
# exit
$ sqlplus/as SYSDBA
Error, insufficient authority

As long as you are a member of the DBA group, you do not need to know the password of SYS and log in directly with Sqlplus/as SYSDBA
And the identity is sys.

Recovery:
# gpasswd-a Oracle DBA

Remote Client Connection:
$ sqlplus sys/[email protected] as Sysdba
$ ls $ORACLE _HOME/DBS/ORAPWORCL
$ orapwd

Create a user in the operating system:
$ Su-
Password:
[Email protected] ~]# Useradd Osuser
[Email protected] ~]# passwd Osuser
$ sqlplus/as SYSDBA
The external user uses a fixed prefix:
Sql> Show Parameter Os_auth
Sql> Create user ops$osuser identified externally;
Sql> Grant create session to Ops$osuser;
Do not su-osuser, environment variables are reserved:
$ su Osuser
Password:
[Email protected] admin]$ Sqlplus/
Sql> Show User
USER is "Ops$osuser"

or create with a command:
Sql> create user User01 identified by password;
Sql> Grant create session to User01; Grant: Authorization

Test:
$ sqlplus User01/password

Authorization: (Rights Management)

Pre-Create test tables
sql> CREATE TABLE t1 (x int);
Sql> create user User01 identified by password;
Sql> Grant Create session to user01;★
Sql> Grant Select any table to user01;★

User01 Test:
$ sqlplus User01/password
Sql> Select COUNT (*) from hr.employees (hr.departments scott.emp);
Sql> Delete from scott.emp; failure!
Sql> select * from Sys.t1;

Select any table n-1 mode Note: (Any does not include SYS) ★
SYS re-authorizes:
Sql> Grant Select any dictionary to User01;
User01 Test:
Sql> select * from Sys.t1; Success
Select any table (n-1) +select any dictionary (1)
SYS authorization:
Sql> grant CREATE table to User01;
User01 Test:
sql> CREATE TABLE t1 (x int);
SYS authorization:
Sql> Grant Unlimited tablespace to User01;
User01 Test:
sql> INSERT INTO T1 values (1);

Object permissions: The keyword is "on"

SYS authorization:
Sql> Grant Select on Hr.employees to User01;
User01 Test:
Sql> Select COUNT (*) from hr.employees;
Sql> Delete from Hr.employees;
Sql> Select COUNT (*) from hr.departments;
SYS authorization:
Sql> Grant Index on hr.employees to User01;
Sql> Grant Unlimited tablespace to User01;
User01 Test:
Sql> CREATE index Emp_sal_idx on hr.employees (salary);
Sql> Select Index_name from user_indexes where table_name= ' EMPLOYEES ';

Create any table system-level options, which can be created in any one of the tables at the system level option, and the permissions for the system
Alter any TABLE permission at the system level to modify any one of the tables (any without SYS) ALTER TABLE object permissions
Drop any table system-level permissions, can delete any one of the tables the drop table has no permissions

Cascading deletions of permissions:
System permissions:
SYS preparation:
sql> drop user User01 cascade;
sql> drop user User02 cascade;
Sql> create user User01 identified by password;
Sql> create user User02 identified by password;
Sql> Grant create session to User01;
Sql> Grant create session to User02;
SYS authorization:
Sql> Grant Select any table to User01 with admin option;
User01 tested successfully and authorized to User02:
Sql> Select COUNT (*) from hr.employees;
Sql> Grant Select any table to user02 with admin option;
User02 Test Success:
Sql> Select COUNT (*) from hr.employees;
SYS REVOKE permissions:
Sql> Revoke Select any table from User01;
User01 operation failed:
Sql> Select COUNT (*) from hr.employees;
User02 Test Success:
Sql> Select COUNT (*) from hr.employees;
Object permissions:
Sql> Grant Select on Hr.employees to User01 with GRANT option;

Dba+sysdba=sys

Revoke: Retract (revoke permission)

Pre-defined roles:
Sql> select role from Dba_roles;
To create a role:
sql> Create role Hr_mgr;
sql> Create role Hr_clerk;
Sql> Grant Select any table to Hr_mgr;
Sql> Grant Select on Hr.employees to Hr_clerk;
Sql> Grant Hr_mgr to User01;
Sql> Grant Hr_clerk to User02;
User01/user02 Test:
Role must be re-signed in effect

Audit (Audit)

Turn on switch parameters:
Sql> Show Parameter Audit_trail

Set audit options:
Each time a new audit option is set, the test user needs to reconnect
SYS preparation:
sql> drop user User01 cascade;
Sql> create user User01 identified by password;
Sql> Grant Create session, create TABLE, create any table to User01;

Oracle Course archive, day ten