Step-by-step explanation of ORACLE (Lecture 4)

Step-by-step explanation of ORACLE (Lecture 4)
1. Database Administrator1. Differences between sys and system (instance description)In the first lecture, I said the following two sentences: (1) a sys user is a super user with the highest permissions and has the dba role and special permissions for sysdba and sysoper database management, the base tables and views of all oracle data dictionaries are stored in sys users. These base tables and views are crucial for oracle operation and are maintained by the database, no user can change it manually. sys has the permission to create a database. (2) The system user is the management operator and has the same permissions as the administrator and has the dba role. He does not have the create database permission.

SQL> select * from dba_roles where role='DBA';ROLE                           PASSWORD_REQUIRED------------------------------ -----------------DBA                            NO

Dba_roles is used to query all system roles. From the result, DBA is a system role, not a permission. Similarly, if dba_roles does not contain sysdba or sysoper, these two roles are not roles.

SQL> select * from dba_role_privs where granted_role='DBA' and (grantee='SYS' or grantee='SYSTEM');GRANTEE                        GRANTED_ROLE                   ADMIN_OPTION DEFAULT_ROLE------------------------------ ------------------------------ ------------ ------------SYS                            DBA                            YES          YESSYSTEM                         DBA                            YES          YES

DBA_ROLE_PRIVS is used to query all users or roles. From the result, both sys and system have DBA roles.

SQL> select * from v$pwfile_users;USERNAME                       SYSDBA SYSOPER------------------------------ ------ -------SYS                            TRUE   TRUE

SQL> select * from system_privilege_map; PRIVILEGE NAME                                       PROPERTY---------- ---------------------------------------- ----------        -3 ALTER SYSTEM                                      0        -4 AUDIT SYSTEM                                      0        -5 CREATE SESSION                                    0        -6 ALTER SESSION                                     0        -7 RESTRICTED SESSION                                0       -10 CREATE TABLESPACE                                 0       -11 ALTER TABLESPACE                                  0       -12 MANAGE TABLESPACE                                 0       -13 DROP TABLESPACE                                   0       -15 UNLIMITED TABLESPACE                              0       -20 CREATE USER                                       0       -21 BECOME USER                                       0       -22 ALTER USER                                        0       -23 DROP USER                                         0       -30 CREATE ROLLBACK SEGMENT                           0       -31 ALTER ROLLBACK SEGMENT                            0       -32 DROP ROLLBACK SEGMENT                             0       -40 CREATE TABLE                                      0       -41 CREATE ANY TABLE                                  0       -42 ALTER ANY TABLE                                   0 PRIVILEGE NAME                                       PROPERTY---------- ---------------------------------------- ----------       -43 BACKUP ANY TABLE                                  0       -44 DROP ANY TABLE                                    0       -45 LOCK ANY TABLE                                    0       -46 COMMENT ANY TABLE                                 0       -47 SELECT ANY TABLE                                  0       -48 INSERT ANY TABLE                                  0       -49 UPDATE ANY TABLE                                  0       -50 DELETE ANY TABLE                                  0       -60 CREATE CLUSTER                                    0       -61 CREATE ANY CLUSTER                                0       -62 ALTER ANY CLUSTER                                 0       -63 DROP ANY CLUSTER                                  0       -71 CREATE ANY INDEX                                  0       -72 ALTER ANY INDEX                                   0       -73 DROP ANY INDEX                                    0       -80 CREATE SYNONYM                                    0       -81 CREATE ANY SYNONYM                                0       -82 DROP ANY SYNONYM                                  0       -83 SYSDBA                                            0       -84 SYSOPER                                           0       -85 CREATE PUBLIC SYNONYM                             0 PRIVILEGE NAME                                       PROPERTY---------- ---------------------------------------- ----------       -86 DROP PUBLIC SYNONYM                               0       -90 CREATE VIEW                                       0       -91 CREATE ANY VIEW                                   0       -92 DROP ANY VIEW                                     0      -105 CREATE SEQUENCE                                   0      -106 CREATE ANY SEQUENCE                               0      -107 ALTER ANY SEQUENCE                                0      -108 DROP ANY SEQUENCE                                 0      -109 SELECT ANY SEQUENCE                               0      -115 CREATE DATABASE LINK                              0      -120 CREATE PUBLIC DATABASE LINK                       0      -121 DROP PUBLIC DATABASE LINK                         0      -125 CREATE ROLE                                       0      -126 DROP ANY ROLE                                     0      -127 GRANT ANY ROLE                                    0      -128 ALTER ANY ROLE                                    0      -130 AUDIT ANY                                         0      -135 ALTER DATABASE                                    0      -138 FORCE TRANSACTION                                 0      -139 FORCE ANY TRANSACTION                             0      -140 CREATE PROCEDURE                                  0 PRIVILEGE NAME                                       PROPERTY---------- ---------------------------------------- ----------      -141 CREATE ANY PROCEDURE                              0      -142 ALTER ANY PROCEDURE                               0      -143 DROP ANY PROCEDURE                                0      -144 EXECUTE ANY PROCEDURE                             0      -151 CREATE TRIGGER                                    0      -152 CREATE ANY TRIGGER                                0      -153 ALTER ANY TRIGGER                                 0      -154 DROP ANY TRIGGER                                  0      -160 CREATE PROFILE                                    0      -161 ALTER PROFILE                                     0      -162 DROP PROFILE                                      0      -163 ALTER RESOURCE COST                               0      -165 ANALYZE ANY                                       0      -167 GRANT ANY PRIVILEGE                               0      -172 CREATE MATERIALIZED VIEW                          0      -173 CREATE ANY MATERIALIZED VIEW                      0      -174 ALTER ANY MATERIALIZED VIEW                       0      -175 DROP ANY MATERIALIZED VIEW                        0      -177 CREATE ANY DIRECTORY                              0      -178 DROP ANY DIRECTORY                                0      -180 CREATE TYPE                                       0 PRIVILEGE NAME                                       PROPERTY---------- ---------------------------------------- ----------      -181 CREATE ANY TYPE                                   0      -182 ALTER ANY TYPE                                    0      -183 DROP ANY TYPE                                     0      -184 EXECUTE ANY TYPE                                  0      -186 UNDER ANY TYPE                                    0      -188 CREATE LIBRARY                                    0      -189 CREATE ANY LIBRARY                                0      -190 ALTER ANY LIBRARY                                 0      -191 DROP ANY LIBRARY                                  0      -192 EXECUTE ANY LIBRARY                               0      -200 CREATE OPERATOR                                   0      -201 CREATE ANY OPERATOR                               0      -202 ALTER ANY OPERATOR                                0      -203 DROP ANY OPERATOR                                 0      -204 EXECUTE ANY OPERATOR                              0      -205 CREATE INDEXTYPE                                  0      -206 CREATE ANY INDEXTYPE                              0      -207 ALTER ANY INDEXTYPE                               0      -208 DROP ANY INDEXTYPE                                0      -209 UNDER ANY VIEW                                    0      -210 QUERY REWRITE                                     0 PRIVILEGE NAME                                       PROPERTY---------- ---------------------------------------- ----------      -211 GLOBAL QUERY REWRITE                              0      -212 EXECUTE ANY INDEXTYPE                             0      -213 UNDER ANY TABLE                                   0      -214 CREATE DIMENSION                                  0      -215 CREATE ANY DIMENSION                              0      -216 ALTER ANY DIMENSION                               0      -217 DROP ANY DIMENSION                                0      -218 MANAGE ANY QUEUE                                  1      -219 ENQUEUE ANY QUEUE                                 1      -220 DEQUEUE ANY QUEUE                                 1      -222 CREATE ANY CONTEXT                                0      -223 DROP ANY CONTEXT                                  0      -224 CREATE ANY OUTLINE                                0      -225 ALTER ANY OUTLINE                                 0      -226 DROP ANY OUTLINE                                  0      -227 ADMINISTER RESOURCE MANAGER                       1      -228 ADMINISTER DATABASE TRIGGER                       0      -233 MERGE ANY VIEW                                    0      -234 ON COMMIT REFRESH                                 0      -235 EXEMPT ACCESS POLICY                              0      -236 RESUMABLE                                         0 PRIVILEGE NAME                                       PROPERTY---------- ---------------------------------------- ----------      -237 SELECT ANY DICTIONARY                             0      -238 DEBUG CONNECT SESSION                             0      -241 DEBUG ANY PROCEDURE                               0      -243 FLASHBACK ANY TABLE                               0      -244 GRANT ANY OBJECT PRIVILEGE                        0      -245 CREATE EVALUATION CONTEXT                         1      -246 CREATE ANY EVALUATION CONTEXT                     1      -247 ALTER ANY EVALUATION CONTEXT                      1      -248 DROP ANY EVALUATION CONTEXT                       1      -249 EXECUTE ANY EVALUATION CONTEXT                    1      -250 CREATE RULE SET                                   1      -251 CREATE ANY RULE SET                               1      -252 ALTER ANY RULE SET                                1      -253 DROP ANY RULE SET                                 1      -254 EXECUTE ANY RULE SET                              1      -255 EXPORT FULL DATABASE                              0      -256 IMPORT FULL DATABASE                              0      -257 CREATE RULE                                       1      -258 CREATE ANY RULE                                   1      -259 ALTER ANY RULE                                    1      -260 DROP ANY RULE                                     1 PRIVILEGE NAME                                       PROPERTY---------- ---------------------------------------- ----------      -261 EXECUTE ANY RULE                                  1      -262 ANALYZE ANY DICTIONARY                            0      -263 ADVISOR                                           0      -264 CREATE JOB                                        0      -265 CREATE ANY JOB                                    0      -266 EXECUTE ANY PROGRAM                               0      -267 EXECUTE ANY CLASS                                 0      -268 MANAGE SCHEDULER                                  0      -269 SELECT ANY TRANSACTION                            0      -270 DROP ANY SQL PROFILE                              0      -271 ALTER ANY SQL PROFILE                             0      -272 ADMINISTER SQL TUNING SET                         0      -273 ADMINISTER ANY SQL TUNING SET                     0      -274 CREATE ANY SQL PROFILE                            0      -275 EXEMPT IDENTITY POLICY                            0      -276 MANAGE FILE GROUP                                 1      -277 MANAGE ANY FILE GROUP                             1      -278 READ ANY FILE GROUP                               1      -279 CHANGE NOTIFICATION                               0      -280 CREATE EXTERNAL JOB                               0166 rows selected

System_privilege_map is used to query all system permissions. It has a total of 166 system permissions, but does not have sysdba or sysoper. V $ PWFILE_USERS is used to query all users with sysdba and sysoper permissions. By default, only sys has sysdba and sysoper permissions. In combination, we can see that the special permissions of sysdba and sysoper database management do not exist in the Database. Next let's look at the differences between sysdba and sysoper permissions (there are a lot of images I only stick to on the Internet ).
In general, the system does not have the sysdba and sysoper permissions, so the system cannot create a database, start or close, or perform other operations. All DBA operations must be enabled when the database is opened, sys logon must be performed as sysdba or sysoper. 2. Non-Dba users in the oracle database can still connect to the database using as sysdba. From the results on the black screen, no matter how it is written, as long as the as sysdba can log on successfully and it is a sys user, this is strange. How Does oracle manage it? In fact, there are three login verification mechanisms during oracle login (1) operating system verification (2) password file verification (3) database Verification
Generally, the third method is the database authentication, because the user name and password are stored in the database. However, SYS users (users with SYSDBA and SYSOPER permissions) are not database verification. When oracle data is not started, SYS users can connect to the database and start the database. Therefore, it is impossible to verify the database. SYS users use the first and second authentication methods. This is clear. There are two solutions below. (1) Find sqlnet In the ADMIN directory of the oracle Installation Directory NETWORK. add SQLNET. AUTHENTICATION_SERVICES = (CNT) is changed to SQLNET. AUTHENTICATION_SERVICES = (NONE) (2) delete a user under ora_dba from the Computer Management User Group
Connect again
3. oracle backup and restoration. (1) Export

Export can be divided into: export table export wizard Data Warehouse three-way format (you can export it under external or exp.exe under oracle's bindirectory ). Export is completed using the exp command. The options used include: userid: Specify the username, password, and connection string for the export operation. Table: Specifies the table for the export operation. Owner: Specifies the scheme for executing the export operation. Full = y: Specifies the database for which the export operation is performed. Rows: Specifies whether to export data in the table. Inctype: Specifies the incremental type of the export operation. File: Specifies the exported file name. Instance: (export table) exp userid = scott/scott @ orcl tables = (emp, dept) file = d: \ data. dmp; (Export Scheme) exp userid = scott/scott @ orcl owner = (scott) file = d: \ data. dmp; (Export Database) exp userid = system/orclt @ orcl full = y inctype = complete file = d: \ data. dmp;

(2) Import

Import is to use the tool import to import the objects and data in the file into the database, but the file to be imported must be the file exported by exp.

Common options of imp are:

 

Userid: Specifies the username, password, and connection string for the export operation. Table: Specifies the table for the export operation. Fromuser: Specifies the source user. Touser: Specifies the target user. File: Specifies the import file name. Full = y: Specifies to import the entire file. Rows: Specifies whether to export data in the table. Inctype: Specifies the incremental type of the export operation. Ignore: If a table exists, only data is imported.