Impact of Oracle drop table and TRUNCATE TABLE on grant authorization

Source: Internet
Author: User
Tags sqlplus

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.