[Oracle] Chapter 1 create authorization, Chapter 1 Authorization of oracle

[Oracle] Chapter 1 create authorization, Chapter 1 Authorization of oracle

Chapter 1 create authorization in ORACLE

Run the following command to view control files, data files, and log files:

Control File: Desc v $ controlfile;

Data File: desc v $ datafile;

Log File: desc v $ logfile;

1. Execute the import. SQL File

The command syntax is as follows:

Directory_path \ store_schema. SQL

Directory_path is the Directory and path for saving the store_schema. SQL script.

For example, the following code imports an SQL script file from disk C:

@ C: \ SQL \ store_schema. SQL

2. Switch different users in SQL Plus, such as the following command to switch to system User Login

Connect system/manager @ oracle where oracle refers to the service name

3. After oracle is installed, dual is the default table name entered in SQL Plus:

Select sysdate from dual;

It returns the current date.

* *** Note: the SQL statement ends with a semicolon (;).

4. Databases in oracle exist in tablespaces. The following command creates a tablespace named yyaccp and specifies the path of the data file.

Create tablespace yyaccp

Datafile 'd: \ oracle \ oradata \ oracle \ yyaccp. dbf'

Size 5 M autoextend on;

5. You can create a user name using commands in oracle. The following command creates a yyaccp user name,

And determine the default tablespace and temporary tablespace for it

Create user yyaccp identified by yyaccp

Default tablespace yyaccp

Temporary tablespace temp;

6. If the user to be authorized can perform certain operations in the database, the user should be assigned

The permission to perform these operations. For example, to connect a user to a database

The User grants the "Create session permission". The following command creates the session permission for the yyaccp user

(Use the grant Statement)

Grant create session to yyaccp;

After the permission is granted, you can connect to the database through the yyaccp User:

Connect yyaccp/yyaccp @ oracle;

7. You can use alter to modify the password of a user using the following command:

Alter user yyaccp identified by accp;

8. The password command can be used to modify the password of the user currently logged on. After logging on, enter the password

Command, SQL plus will prompt to enter the original password, and then enter the new password twice, for example:

Connect yyaccp/accp;

Input: password;

SQL plus display:

Change the password of YYACCP

Old password :******

New Password :******

Retype the new password :******

Password Changed

9. You can delete a user by dropping the user. The following command deletes the yyaccp user:

Connect system/manager @ oracle;

Drop user yyaccp;

10. grant different privileges to users using grant: the following uses the system user to grant yyaccp users the permissions to create sessions, create users, and create tables.

Connect system/manager @ oracle;

Grant create session, create user, create table to yyaccp;

Common system privileges:

Create session	Connect to the database
Create sequence	Create Sequence
Create table	Create a table
Create any table	Create a table in any mode
Drop table	Delete table
Drop any table	Delete tables in any mode
Create procedure	Create a stored procedure
Execute any procedure	Execute the stored procedure in any mode
Create user	Create user
Drop user	Delete a user
Create view	Create View

11. Use the with admin option. Authorized users can grant privileges to other users.

The following command grants the execute any procedure privilege to the yyaccp user and specifies

With admin option.

Connect system/manager @ oracle;

Grant execute any procedure to yyaccp with admin option;

12. Check the permissions granted to the user (through the user_sys_privs table) to check which permissions a user has:

The following command demonstrates that the yyaccp user connects to the database and queries user_sys_privs.

Connect yyaccp/accp @ oracle;

Select * from user_sys_privs;

14. Revoke SYSTEM privileges. The following system revokes the dba permission of a yyaccp user

Revoke dba from yyaccp;

15. when an authorized user creates a table in a tablespace or inserts data into an existing table, the user still prompts that the permission is insufficient. For example: if you have created a yyaccp user but the yyaccp user has insufficient permissions when creating a table in the yyaccp tablespace, run the following command:

Connect system/manager;

Alter user yyaccp quota unlimited on yyaccp;

Run the following command to delete a tablespace and delete the corresponding data file:

Drop tablespace yyaccp including contents and datafiles;

Object privileges:

16. Object privileges allow users to perform specific operations on database objects, such as adding, modifying, deleting, and querying tables.

The following table lists Common Object privileges:

Object privileges	Permitted operations
Select	Perform query operations
Insert	Insert
Update	Perform Modification
Delete	Delete
Execute	Execute the Stored Procedure

Grant object privileges to users:

17. You can use the grant statement to grant the Object Privilege to the user. In the following example, the user connects to the database as a yyaccp user and grants the following privileges to the tuser: select for the tusertable table,

Insert and update object privileges for authorization

Connect yyaccp/accp;

Grant select, insert, update on yyaccp. tusertable to tuser;

 

18. The following command grants the tuser the update privilege of the name and address columns in the tusertable table

Grant update (name, ddress) on yyaccp. tusertable to tuser;

19. The grant option allows authorized users to grant such privileges to other users. The following command grants the tuser user the select privilege for the tusertable table and specifies the with grant option

Grant select on yyaccp. tusertable to tuser with grant option

20. Check the granted object privileges (by querying user_tab_privs_made) to check which tables a user has granted to other users:

Select * from user_tab_privs_made

User_tab_privs_made Columns

Column name	Description
Grante	User authorized by this privilege
Table_name	Name of the object (for example, table) operated by the granted privileges
Grantor	Grantor
Privilege	Privileges granted to this object
Grantable	Can a user authorized by a privilege grant this privilege to another user? The value is yes or no.
Hierarchy	Whether the privilege constitutes a hierarchical relationship. The value is equal to yes or no.

User_col_privs_made can be used to check which columns of objects a user has opened.

Select * from user_col_privs_made;

From user_col_privs_made partial Columns

Column name	Column description
Grantee	User authorized by this privilege
Table_name	The name of the object to which the operation is performed.
Column_name	The name of the object to which the operation is performed.
Grantor	Grantor
Privilege	Privileges granted to this object
Grantable	Can a privileged user grant this permission to another user? The value is yes or no.

If you connect to the database as a tuser, you can search for yyaccp. sttable using the public object name sttable:

Connect tuser/tuser;

Select * from sttable;

23. revoke the privileges of a user object:

Use the revoke statement to revoke the object privileges of a user. The following command connects to the database as a yyaccp user and revokes the insert privilege of tuser on the sttable table

Connect yyaccp/accp;

Revoke insert on sttable from tuser

Data blocks, zones, segments, and tablespaces in Oracle databases

At the finest granularity, Oracle stores data into data blocks (also called logical blocks, Oracle blocks, or pages ), A data block corresponds to a certain number of bytes of physical database storage space on the disk.

The next storage level of the logical database bucket is a zone. A zone is a continuous data block allocated to store specific information.

The logical database storage level above the partition is called a segment. A segment is a set of partitions. Each partition is allocated with a specific data structure. All partitions are stored in the same tablespace. For example, the data of each table is stored in its own data segment, and the data of each index is stored in its own index segment. If a table or index is partitioned, each partition is stored in its own segment.

Oracle allocates space for segments in a zone. When the existing zone in the segment is full, Oracle allocates another zone for the segment. Because zones are allocated on demand, the zones of a specific segment may or may not be consecutive.

Segments and all their zones are stored in one tablespace. In a tablespace, segments can be distributed across multiple files, that is, segments can span data files. In any case, each partition must be in a data file.