First article on Oracle Database Learning

Permission management:

The default three usernames and passwords in oracle 9 are as follows:

Sys change_on_install // the Administrator with the highest Permissions

System manager // common Administrator

Scott tiger // common user

In oracle 10, these three users are still used as default users. However, the passwords of sys and system users are no longer default. You can specify this parameter when installing the database. From the security perspective, scott is locked by default. To use this user, you must first unlock it.

Note: To use the oracle database, we must start at least two services, one is the listening service and the other is the database instance.

Create a user;

Log in as a system administrator.

Statement: create user lisi identified by lisi; // create a user called lisi, And the password is also lisi

Although a user is created, the user does not have any permissions. You do not have the permission to log on to the database. If you use sqlplus lisi/lisi to log on to the database, an error is returned, indicating that you do not have the create session permission.

Therefore, the system administrator should first specify the login permission for the lisi user.

Statement: grant create session to lisi;

After authorization, lisi can log on to the database. However, you do not have the permission to create a database table. You still need to specify the permission.

Statement: grant create table to lisi;

Use the lisi account to create a database table: create table mytable (id int );

After execution, an error is displayed: The table space 'users' has no permission. Each database table has its own tablespace, which is equivalent to a file that must be located in a folder.

Although the lisi user has the permission to create a table, but does not have the permission to use the tablespace, the table cannot be created. This is like you have the key to my room, but you cannot enter my room without the key to my door.

The system administrator grants the lisi user the permission to use the tablespace:

Grant unlimited tablespace to lisi; in this way, the use of the table space by the user lisi is no longer limited.

Under the lisi account, create table: create table mytable (id int );

Insert a record: insert into mytable values (1 );

Inserted successfully.

You can also delete a table: drop table mytable;

Some people may have doubts. Since the database permission management is so strict, we only grant the permission of the lisi user to create a table. It has not been granted the insert or delete permissions. Here we can understand that the current user has created a table, and the table belongs to this user. Since the user has created a table, he naturally has all permissions on the table.

Moreover, the database does not have the permission to drop the table. Use: grant drop table to lisi; error message: Permission missing or invalid.

The preceding example shows how to revoke a user's permission?

Use the following statement to revoke the permission to create a table from lisi: revoke create table from lisi;

When you use the lisi account to create a table, the following error occurs: insufficient permissions.

In most cases, how do we know what permissions a user has if we change the user's permissions frequently?

By default, the database maintains a view that provides external system information (called data dictionary), allowing you to view your specific permissions.

Use the following statement to view the system permissions of the current user:

Select * from user_sys_privs;

USRENAME PRIVILEGE ADM

-------------------------------------------------------------------------------------------

LISI CREATE SESSION NO

LISI UNLIMITED TABLESPACE NO

Permissions in oracle are divided into system permissions and object permissions.

System permissions are some of the permissions we mentioned above.

Object permission refers to: for example, if you create a table in lisi, the table can be viewed as an object. Does another user have the permission to access the table. This is the so-called object permission management.

We are creating a user wangwu. The password is also wangwu. Create a table mytab under this user. If the lisi user accesses the mytab table, will it succeed?

In the lisi window, input: select * from mytab; error: the table or view does not exist. We know that the table belongs to the table creator.

Here we directly query the mytab table. The database will search for the table under the current user. Obviously, the current user lisi does not have the mytab table. The system prompts that the table or view does not exist.

Then we specify the table owner and query again: select * from wangwu. mytab; the window displays the error message "insufficient Permissions. Therefore, although the mytab table is found, the table has no access permission.

Only the table owner can grant related permissions to the table to other users. Use the following statement in the wangwu operation window to grant the query statement to lisi;

Grant select on mytab to lisi;

After executing this statement, lisi can query the mytab table of user wangwu.

If you want to obtain other permissions for the mytab table, you still need to specify them (Multiple permissions must be specified at the same time, separated by commas ):

Grant update, select, delete on mytab to lisi;

If you want to grant all table permissions to the user lisi, you can write it like this;

Grantallon mytab to lisi;

In the wangwu window, insert several pieces of data to the mytab. Then, the query displays "unselected rows ". It indicates that the insert is not synchronized to the database.

In oracle, SQL statements must be submitted manually by default. Therefore, after several insert statements, you can execute the commit statement and submit the statement. When you query again, the table has data.

If you want to grant a permission to all users, you can use the public Keyword:

Grant create session topublic;

To view the object permissions of the current user, use the following statement:

Select * from user_tab_privs;

The permission control granularity of oracle is very small, and can even be precise to the permissions of a column.

Grant update (name) on mytab to lisi;

The result of this statement is that the lisi user only has the permission to update the name column of the table mytab.

Grant insert (id) on mytab to lisi;

View the permissions of the current user on the columns of the database table:

Select * from user_col_privs;

Run update wangwu. mytab set name = fdsfa, id = "dfs" where id = 1;

Insufficient permissions are displayed after execution.

Update wangwu. mytab set name = "fsa" where id = 1;

In this way, you can.

Similarly, execute the insert into wangwu. mytab values (4, "asf") statement. After the statement is executed, the system displays that the permission is insufficient.

The modification statement is: inset into wangwu. mytab (id) values (4); successfully executed.

You can only set the update and insert settings to the permission control of a column, but not the query and delete settings.

Command: show user can view the current user

The database has three types of statements:

Ddl: The data definition language. It specifies operations such as creating and deleting database tables.

Dml: data manipulation language. For addition, deletion, modification, and query of tables, only dml needs to be submitted.

Dcl: The data control language used to manage system and object permissions.

Transfer permissions:

Transfer of system permissions:

The sys user authorizes some system permissions to the lisi user.

Grant alter any table to lisi;

To view the system permissions of lisi, you have the alter any table permission.

Lisi wants to grant the permission to the wangwu user and execute the following statement: grant alter any table to wangwu. After the statement is executed, the system reports that the permission is insufficient ".

If you want lisi to pass permissions, you can add the with admin option during sys user authorization. This option also shows that you have the permission management capability.

Grant alter any table to lisiwith admin option; then, lisi can pass the alter any table permission to wangwu.

You can also pass this permission to wangwu and use this admin option.

View the system permission of lisi. The value of the adm field in the same row of the alter any table permission is changed from NO to YES, which indicates that lisi has assigned this permission.

Object permission transfer:

The transfer of system permissions is similar, but the subsequent options are changed:

Add sys to create A table. Grant the select permission to lisi:

Grant select On A to lisi;

To grant lisi the select permission to Table A, you only need to change it:

Grant select On A to lisiwithgrantoption;

Think: If the sys administrator revokes the lisi permission, is the wangw permission also revoked?

Manage permissions through roles

If you use the preceding permission management method to assign permissions to each user one by one, it will inevitably be messy and cause management difficulties. Therefore, oracle provides roles to collectively manage permissions.

A role is a set of permissions.

Create a role under sys:

Create role myrole;

Add permissions to a role:

Grant create session to myrole;

Grant create table to myrole;

Create a user:

Create user zhangsan;

Grant myrole to zhangsan; // grant the preceding two permissions to zhangsan.

Some system permissions cannot be directly granted to the role because the permission is too large, such as unlimited tablespace.

For example, run grant unlimited tablespace to myrole;

Error message: the unlimited tablespace cannot be assigned a role.

Delete a role:

Drop role myrole;

Permission example:

Create table create any table

[Alter table] alter any table

[Delete table] delete any table

Addition: oracle databases do not contain purple permission types. With the create table permission, everything in the table belongs to the Creator. You do not need to specify the alter table and drop table permissions. By default, the alter table and drop table permissions are available.

The create any table permission indicates that the user can create tables for other users.

Example: wangwu creates a table temp for lisi

Create tablelisi. temp (id int );//