Impact of Oracle drop table and TRUNCATE TABLE on grant authorization

1, to the SYS login, build table to give permission to prepare test table Z2

[Email protected] ~]$ Rlwrap sqlplus/ asSysdbasql*plus:release11.2.0.4.0Production onTue May -  -: -: -  .Copyright (c)1982, -, Oracle. All rights reserved. Connected to: Oracle Database OneG Enterprise Edition Release11.2.0.4.0- -Bit Production withThe partitioning, OLAP, Data Mining andReal Application Testing Optionssql>CreateTable Z2 as Select 1  asA fromDual Table created. Sql> GrantSelect  onZ2 toDwetl; Grant succeeded. Sql>

2, to dwetl account login, query permissions on the table, query OK, access to data

[Email protected] ~]$ Rlwrap sqlplus' Dwetl/"pwd2017"' @DB1SQL *plus:Release 11.2. 0. 4. 0Production onTue May -  -: -: on  .Copyright (c)1982, -, Oracle. AllRights reserved. Connected to: Oracle Database OneG Enterprise EditionRelease 11.2. 0. 4. 0- -bitProduction withThe partitioning, OLAP, Data Mining andReal Application Testing Optionssql>Select* FROM SYS.Z2; A----------     1Sql>

3, then to sys login, delete the table Z2, once again establish the table Z2

[Email protected] ~]$ Rlwrap sqlplus/ asSysdbasql*plus:release11.2.0.4.0Production onTue May -  -: -: -  .Copyright (c)1982, -, Oracle. All rights reserved. Connected to: Oracle Database OneG Enterprise Edition Release11.2.0.4.0- -Bit Production withThe partitioning, OLAP, Data Mining andReal Application Testing optionssql> drop table sys.z2; Table dropped. Sql>CreateTable Z2 as Select 1  asA fromDual Table created. Sql>sql>Select* fromSYS.Z2; A----------1Sql>

4, again to Dwetl landing, see if you can query, the answer is not found, error table or view does not exist

[Email protected] ~]$ Rlwrap sqlplus' Dwetl/"pwd2017"' @DB1SQL *plus:Release 11.2. 0. 4. 0Production onTue May -  the:Geneva:Ten  .Copyright (c)1982, -, Oracle. AllRights reserved. Connected to: Oracle Database OneG Enterprise EditionRelease 11.2. 0. 4. 0- -bitProduction withThe partitioning, OLAP, Data Mining andReal Application Testing Optionssql>Select* FROM SYS.Z2;Select* FROM SYS.Z2 *error on line1: ora-00942: TableorView does notExistsql>

5, again with the Sys login, give the table Z2 to dwetl permissions

[Email protected] ~]$ rlwrap sqlplus/as sysdbasql*plus:Release 11.2. 0. 4. 0Production onTue May -  the: -: -  .Copyright (c)1982, -, Oracle. AllRights reserved. Connected to: Oracle Database OneG Enterprise EditionRelease 11.2. 0. 4. 0- -bitProduction withThe partitioning, OLAP, Data Mining andReal Application Testing Optionssql> GrantSelect  onZ2 toDwetl; Grant succeeded. Sql>

6, again to Dwetl Landing, try whether you can query to the SYS.Z2 table, OK, you can query the data

[Email protected] ~]$ Rlwrap sqlplus' Dwetl/"pwd2017"' @DB1SQL *plus:Release 11.2. 0. 4. 0Production onTue May -  the: -: $  .Copyright (c)1982, -, Oracle. AllRights reserved. Connected to: Oracle Database OneG Enterprise EditionRelease 11.2. 0. 4. 0- -bitProduction withThe partitioning, OLAP, Data Mining andReal Application Testing Optionssql>Select* FROM SYS.Z2; A----------     1Sql>

7. Does truncate have any influence on authority?

Log in with SYS, execute the TRUNCATE operation table Z2, and then exit to dwetl the query table Z2 and discover that you can perform a select operation

sql> truncate TABLE Z2; Table truncated. Sql>Exiterror:ora-04088: Error during execution ofTrigger' Timdba. Trig_logoff_audit ' ora-01403: No Data foundora-06512: At line5Disconnected from Oracle Database OneG Enterprise EditionRelease 11.2. 0. 4. 0- -bitProduction withThe partitioning, OLAP, Data Mining andReal Application Testing Options ( withcomplications) [[email protected] ~]$ Rlwrap sqlplus' Dwetl/"pwd2017"' @DB1SQL *plus:Release 11.2. 0. 4. 0Production onTue May -  the: -: ,  .Copyright (c)1982, -, Oracle. AllRights reserved. Connected to: Oracle Database OneG Enterprise EditionRelease 11.2. 0. 4. 0- -bitProduction withThe partitioning, OLAP, Data Mining andReal Application Testing Optionssql>Select* FROM Sys.z2;no rows selectedsql>

The above test results identify:

(1): When the table is dropped, other users of the operation of the table invalid permissions, please remember to re-grant permissions, otherwise users will not be able to operate the table;

(2): After the table has been truncate, other users of the table operation permissions are still valid.

Impact of Oracle drop table and TRUNCATE TABLE on grant authorization