Oracle database objects and user Management ___ database

Oracle database objects and user management

www.ncn.cn The management and maintenance of the pattern object of Oracle Database This section focuses on the management and maintenance of schema objects for Oracle databases, which include table spaces, tables, views, indexes, sequences, synonyms, aggregates, and integrity constraints. For each pattern object, it first describes its definition, explains its functionality, and finally explains how to manage it for maintenance based on an instance of SQL language. 1.1 Table Space Because Tablespace is the logical space that contains these pattern objects, it is necessary to maintain it first. Create a table space Sql>create tablespace Jxzy >datafile '/usr/oracle/dbs/jxzy.dbf ' >ONLINE; Modify Table Space Sql>alter tablespace jxzy OFFLINE NORMAL; Sql>alter tablespace Jxzy >rename datafile '/usr/oracle/dbs/jxzy.dbf ' >to '/usr/oracle/dbs/jxzynew.dbf ' >online Sql>create tablespace Jxzy ONLINE Delete a table space Sql>drop tablespace Jxzy >including CONTENTS 1.2 Watch Maintenance A table is the basic unit of data storage in a database, and a table contains several columns, each with column names, types, lengths, and so on. The establishment of the table Sql>create TABLE Jxzy.switch ( >office_num Number (3,0) is not NULL, >switch_code Number (8,0) is not NULL, >switch_name VARCHAR2 () not NULL); Modification of the table Sql>alter TABLE Jxzy.switch >add (DESC VARCHAR2 (30)); Deletion of the table Sql>drop TABLE Jxzy.switch >cascade CONSTRAINTS Remove the integrity constraints for other tables referencing the table 1.3 View Maintenance A view is a collection of data that is produced by one or more base tables, but the view does not occupy storage space. Building a view protects data security (allowing users to query for changes visible), simplifies query operations, and protects data independence. The establishment of a view Sql>create VIEW Jxzy.pole_well_view as > (SELECT pole_path_num as Path, Pole as Device_num from pole >union >select Pipe_path_num as Path, > as Device_num from-OK); Replacement of views Sql>replace VIEW Jxzy.pole_well_view as > (SELECT pole_path_num as Path, Pole as Support_device from pole >union >select Pipe_path_num as Path, As Support_device from; Deletion of views Sql>drop VIEW Jxzy.pole_well_view; 1.4 Sequence Maintenance A sequence is a unique integer generated by a sequence generator. The establishment of a sequence Sql>create SEQUENCE jxzy.sequence_cable >start with 1 >increment by 1 >NO_MAXVALUE; A sequence is established, Jxzy.sequence_cable.currval returns the current value, Jxzy.sequence_cable.nextval returns the new value after the current value plus 1 Changes to the sequence Sql>alter SEQUENCE jxzy.sequence_cable >start with 1//starting point can not be modified, if modified, should be deleted first, and then redefine >inctement by 2 >maxvalue 1000; Deletion of sequences Sql>drop SEQUENCE jxzy.sequence_cable 1.5 Index maintenance An index is a table-related structure that is built to improve the speed of data retrieval. Therefore, to increase the indexing speed on a table, you can set up one or more indexes on a table, and one index can be built on one or several columns. For query-type tables, setting up multiple indexes can greatly improve query speed, and for newer tables, if there are too many indexes, overhead will be increased. Indexed unique and non-unique indexes The establishment of the index Sql>create INDEX Jxzy.idx_switch >on switch (switch_name) >tablespace Jxzy; Modification of indexes Sql>alter INDEX Jxzy.idx_switch >on switch (office_num,switch_name) >tablespace Jxzy; Deletion of indexes Sql>drop INDEX Jxzy.idx_switch; 1.6 Integrity Constraint Management The integrity of database data refers to the correctness and compatibility of data. Data integrity checking prevents inconsistent semantic data in the database. An integrity constraint is a set of rule description methods that define a table's columns. Oracle provides the following integrity constraints. A. NOT null Non-null B. Unique unique keywords C. primaty key primary key A table can only have one, not null D. Foreiga key foreign keys E.check each row of the table must be true or unknown for the specified condition (for null values) For example: A column defines a non-empty constraint Sql>alter TABLE office_organization >modify (desc VARCHAR2 (20) >constraint Nn_desc not NULL) A column defines a unique keyword Sql>alter TABLE office_organization >modify (Office_name VATCHAR2 (20) >constraint uq_officename UNIQUE) Define PRIMARY KEY constraint, primary key requires Non-empty Sql>create TABLE Switch (switch_code number (8) >constraint pk_switchcode PRIMARY KEY,) Make PRIMARY KEY constraint invalid Sql>alter TABLE switch DISABLE PRIMARY KEY Defining foreign keys Sql>create TABLE POLE (pole_code number (8), >office_num Number (3) >constraint Fk_officenum >references office_organization (Office_num) >on DELETE CASCADE); Definition Check Sql>create TABLE Office_organization ( >office_num Number (3), >constraint Check_officenum >check (Office_num BETWEEN, 99); Ii. Oracle database users and Rights management Oracle is a multiuser system that allows many users to share system resources. In order to ensure the security of database system, the database management system is equipped with good security mechanism. 2.1 Oracle Database Security Policy Establish a system-level security guarantee System-level privileges are implemented by granting users system-level rights, and system-level rights (System privileges) include: Creating tablespaces, creating users, modifying user rights, deleting users, and so on. System privileges can be granted to users, or they can be recycled at any time. There are more than 80 Oracle system privileges. Establish object-level security assurance Object-level privileges are implemented by giving users the right to manipulate (query, add, and remove) specific tables, views, sequences, and so on in the database. Establish user-level security assurances User-level security is implemented through user passwords and role mechanisms (a set of rights). The purpose of introducing role mechanism is to simplify the authorization and management of users. The practice is to group users by their functions, to create roles for each user, and then to assign roles to users who have the same privileges as the same roles. 2.2 User Management The content of Oracle user management mainly includes user's establishment, modification and deletion User's establishment Sql>create USER Jxzy >identified by Jxzy_password >default tablespace System >quata 5M on system; Maximum space limit for user use User's modifications Sql>create USER Jxzy >identified by JXZY_PW >quata 10M on system; To delete a user and the objects it has built Sql>drop USER Jxzy CASCADE; Delete its established entities at the same time 2.3 System Privilege Management and control ORACLE provides more than 80 system privileges, each of which allows users to perform one or a class of database operations. Granting System privileges Sql>grant CREATE user,alter User,drop USER >to jxzy_new >with ADMIN OPTION; Reclaim System Privileges Sql>revoke CREATE user,alter User,drop USER >from jxzy_new But no Cascade recycle feature Displays system privileges that have been granted (System-level privileges for a user) Sql>select*from Sys.dba_sys_privs 2.4 Object Privilege Management and control Oracle Object privilege refers to the right of a user to perform special operations on a specified table. These special operations include adding, deleting, changing, viewing, executing (stored procedures), referencing (other table fields as foreign keys), indexing, and so on. Grant Object Privileges Sql>grant Select,insert (Office_num,office_name), >update (DESC) on Office_organization >to New_adminidtrator >with GRANT OPTION; Cascade Authorization Sql>grant all on Office_organization >to New_administrator Reclaim Object Privileges Sql>revoke UPDATE on Office_orgaization >from New_administrator Cascade Recycle function Sql>revoke all on Office_organization >from New_administrator Show all object privileges that have been granted Sql>select*from Sys.dba_tab_privs 2.5 Management of roles Oracle's role is to name related privileged groups, including system privileges and object privileges, that Oracle uses to simplify privileged management and grant it to users or other roles. Oracle database systems have predefined five roles for Connect, RESOURCE, DBA, Exp_full_database, and Imp_full_database. Connect has privileges to create tables, views, and sequences, resource has privileges to create procedures, triggers, tables, sequences, and DBA with all system privileges; exp_full_database, Imp_full_database has privileges to unload and mount databases. By querying Sys.dba_sys_privs, you can understand the rights that each role has. Granting user Roles Sql>grant DBA to New_administractor >with GRANT OPTION;