Oracle Database permission management and oracle permission management

Oracle Database permission management and oracle permission management
Permission management: the default three usernames and passwords in oracle 9: sys change_on_install // the Administrator with the highest permissions: system manager // normal administrator scott tiger // normal 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.
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, so this user must be used,

You need to unlock the instance first.

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. We can understand this as follows: if the current user creates a table, the table

This is the user. Since a table is created, the user 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); // it is possible to report the error message "exceeding the tablespace 'users' space limit" because the lisi user may not have the tablespace permission, execute the permission granted to the lisi user unlimited tablespace.

Solution.

Note: A table belongs to a user. The role does not belong to a user.

Oracle three login verification mechanisms

Operating system verification

Password File Verification

Database Verification

For most common users, database verification is used for user login after the database is started.

The sys user has the highest permissions. Its permissions even include starting and shutting down databases. It is connected to the oracle database before it is started. In this case, it is hard to understand that the identity authentication of sys cannot adopt database authentication, because the database was not started yet. Therefore, sys authentication uses operating system authentication and password file authentication (this is not very strict, it should be based on both SYSDBA and SYSOPER connection Identity Authentication ).

When a user connects to the database, the client first connects to the listening service, and the listener sends the request to the database. If the verification succeeds, the client does not need to listen in the future and communicates directly with the database instance.

Oracle, which was run on linux and unix in the early days, had a strict sequence of startup: Start the listener first (you only need to run a command without any permissions ), then start the database instance (permission required)

Command sequence: lsnrctl start // start the listening service

Sqlplus sys/oracle as sysdba // initiate a request to the database instance. It is found that the request is connected as sysdba, so database verification is not performed, but the operating system and password file are used for verification. If the verification succeeds, run the startup database instance

Startup // start the database instance

The command for earlier versions should be written as follows:

Lsnrctl start

Sqlplus/nolog

Conn sys/oracle as sysdba

Startup

The Startup Process of oracle in windows is silly:

Lsnrctl start

Oradim-starup-sid orcl

When connecting to the database, you can write as follows: conn/as sysdba can also be connected, or even randomly specify the user name and password, for example, conn abc/abc as sysdba can be logged on. This is because the connection is based on sysdba and is first verified by the operating system. When we install the database, the account of the current system will be added to the system administrator group of oracle. By default, the connection is verified by the current account of the system. After the system account in the Administrator group is deleted, it will adopt the password verification mechanism and the user name and password must be specified.

Q: What should I do if I lose my password?

We know that if the password of a common user is forgotten, we can modify the password of the user as an administrator (you cannot view the password because it is encrypted and can only be modified)

You can directly modify it using a graphical tool. You can also run the following command:

Alter user scott identified by tiger;

In actual development, we need to cancel the operating system verification. The password will be used for verification later. But what if we forget the password?

We can delete the password file and generate a password file.

Find the location of the password file: .. \ db_2 \ database \ pwdorcl. ora. The red part is the fixed part of the password file name. orcl indicates the database sid, which may be different.

After deleting the password file, generate one. Use the orapwd command as follows:

Orapwdfile = <full path of the password file, the password file should be named according to the previous> password = <specified password> entries = <maximum number of DBAs saved in the password File> force = only whether to forcibly overwrite the file

Example: orapwd file = E: \ oracle \ ora92 \ database \ pwdora9i. ora password = sys entries = 10;

Use the following statement to view the number of privileged users in the password file:

Select * from v $ pwfile_users;

Create a user:

Create user Username

Identified by password

Default tablespace

Temporary tablespace

Quota integer K | M | unlimited on tablespace

Example:

Create user abc

Identified by abc

Default tablespace users // the user's default tablespace is users. You can create a table in this tablespace.

Temporary tablespace temp // user's temporary tablespace, used for indexing, sorting, and other work temporary places, equivalent to a temporary folder in windows

Quota 50 M on users // specify the size of the users tablespace.

Quota unlimited on temp; // specify the size of the temporary tablespace.

Restrict users

User lock

Alter user Username account lock

User unlock

Alter user Username account unlock

The user password is invalid immediately.

Alter user Username password expire

Delete A User:

Drop user Username [cascade]

Cascade is used to force cascading deletion when there are undeleted objects (such as tables) under the deleted user. It indicates deleting all objects of a user.

Types of permission management in oracle databases

1. system permissions 2. Object Permissions
 
Oracle Database permission allocation

Create user A and user B in instance a. In this way, a has only the permission to operate on instance A. The statement is as follows:
Create user
Identified by
Default tablespace USERS
Temporary tablespace tEMP
Profile dEFAULT;
-- Grant/Revoke role privileges
Grant resource to;
Grant connect to;