Rights Management: The default three username and password in Oracle 9: SYS change_on_install//Administrator System Manager with the highest privileges///General Administrator Scott Tiger//general user in Oracle 10, still use this Three users as default users. However, the SYS and system user passwords are no longer default.
Rights Management:
The default three user names and passwords in Oracle 9:
SYS change_on_install//Administrator with the highest privileges
System Manager//General administrator
Scott Tiger//general user
In Oracle 10, these three users are still used as default users. However, the SYS and system user passwords are no longer default. It can be specified by the user when the database is installed. From a security standpoint, the Scott user is locked out by default, so to use that user,
The lock needs to be unlocked first.
Note: We want to use the Oracle database to start at least two services, one for the listening service and one for the DB instance.
Create user;
Log in as a system administrator.
Using the statement: Create user Lisi identified by Lisi; A user named Lisi is created, and the password is Lisi
Although the user was created, the user now does not have any permissions. There is no permission to log in to the database. If use: Sqlplus lisi/lisi Log in the database, will error, show no create session permissions.
Therefore, first use the system administrator to Lisi this user to specify the login permissions.
The statement is: Grant create session to Lisi;
After authorization, Lisi can log into the database. However, you do not have permission to create a database table, and you still need to specify it.
The statement is: Grant CREATE table to Lisi;
Using the Lisi account, create a database table: Creating table MyTable (id int);
Error after execution: No permissions on tablespace ' USERS '. Each database table has its own tablespace, which is the equivalent of a file that must be located under a folder.
Although Lisi users have permission to create tables, they do not have permission to use tablespaces, and eventually they cannot create tables. It's like you have the key to my room, but without the key to my house, you won't be able to get into my room at last.
Permissions granted by the system administrator to Lisi users to use tablespaces:
Grant Unlimited tablespace to Lisi, so there is no limit to the use of table spaces by user Lisi.
Under Lisi account, CREATE table: Creation table mytable (id int);
Insert a record: INSERT INTO mytable values (1);
Insert succeeded.
You can also delete tables: drop table mytable;
One might question that since database permissions are so strict, we just grant Lisi users permission to create tables. And does not grant it the right to insert, delete, etc. Here we can understand this: the current user creates a table, then the table
It belongs to the user, and since the user has created the table, it naturally has all the permissions on the table.
Also: The database does not have permission to drop table. Use: Grant drop table to Lisi; appears: permission is missing or invalid error prompt.
The above is granted permission, then how to revoke a user's permission?
Use the following statement to revoke the permission to create a table for LISI: revoke. Create tables from Lisi;
Then use the Lisi account to create the table, there will be error: insufficient permissions.
In most cases, if we modify the user's permissions frequently, how do we know what permissions the user has?
The database maintains a view by default provides some system information (called a data dictionary) to see the specific permissions of the user.
Use the following statement to view system permissions for the current user:
SELECT * from User_sys_privs;
Usrename PRIVILEGE ADM
----------------------------------------------- ---------------------------------------- ----
LISI CREATE SESSION NO
LISI UNLIMITED tablespace NO
The permissions in Oracle are divided into system permissions and object permissions.
System permissions are just some of the permissions we've talked about.
Object permissions refer to: For example, a user Lisi creates a table that can be viewed as an object. Does another user have permission to access the table? This is known as Object Rights Management.
We are creating a user Wangwu. The password is also WANGWU. Under the user, create a table mytab. If Lisi user to access table Mytab, will it succeed?
Under Lisi's window, enter: SELECT * from Mytab; error: The table or view does not exist. We know that the table belongs to the creator of the table.
Here we directly query the table Mytab, the database will go to the current user looking for the table, obviously the current user Lisi no table mytab. So the hint table or view does not exist.
Then we specify the owner of the table and query again: SELECT * from wangwu.mytab; window displays "Insufficient permissions" error prompt. The Mytab table, however, does not have access permissions.
Only the owner of the table can grant permissions on the table to other users. Using the User Wangwu Operation window, using the following statement, the query statement is granted to Lisi;
Grant SELECT on Mytab to Lisi;
After executing this statement, Lisi can query the user Wangwu's Mytab table.
If you want to get additional permissions for the Mytab table, you still need to specify (multiple permissions are specified at the same time, separated by commas):
Grant Update,select,delete on Mytab to Lisi;
If you want to give all the permissions of the table to the user Lisi, you can write this;
Grantallon Mytab to Lisi;
In the Wangwu window, insert a few data into the mytab. Then queries, but displays "unselected rows". Indicates that the insertion was not synchronized to the database.
Under Oracle, the SQL statement needs to be manually committed by default. So after a few insert statements, you can execute a commit; 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 for the current user, use the following statement:
SELECT * from User_tab_privs;
Oracle's permissions control is granular and can even be accurate to a single column of permissions.
Grant update (name) on Mytab to Lisi;
The effect of this execution is that the Lisi user has only permission to update the Name column for table Mytab.
Grant Insert (ID) on Mytab to Lisi;
To view the current user's permissions on a column of a database table:
SELECT * from User_col_privs;
Under Lisi permissions, execute: Update wangwu.mytab set name= ' FDSFA ', id= "Dfs" where id=1;
Insufficient display permissions after execution.
Update Wangwu.mytab set name= "FSA" where id=1;
That's all you can do.
Same execution: INSERT into Wangwu.mytab values (4, "ASF"), and also shows insufficient permissions after execution.
The modified statement is: Inset into Wangwu.mytab (ID) values (4); successful execution.
Only updates and inserts can be set to a specific column of permissions control, not to query and delete settings.
Command: Show user can view current users
There are three types of statements in the database:
DDL: A data definition language that specifies operations such as the creation of database tables, deletions, and so on.
DML: Data manipulation language, for table additions and deletions to the operation, only DML need to commit operations.
DCL: Data Control Language, management of system permissions and object permissions.
Transfer of permissions:
System permissions are passed:
SYS user authorizes some system privileges to the Lisi user.
Grant alter any table to Lisi;
To view Lisi's system permissions, you have the ALTER any table permission.
Now Lisi wants to pass this permission to the WANGWU user to execute the following statement: Grant alter any table to WANGWU; "Insufficient permissions" after execution.
If you want to Lisi you can also pass permissions, you can add the option of the WITH ADMIN option when the SYS user authorizes this option, which explains the ability to manage permissions.
That is: Grant alter any table to lisiwith admin option, so that Lisi can pass the ALTER any table permission to Wangwu.
You can also pass this permission if you want to WANGWU, or use the admin option.
To view Lisi's system permissions, the ADM field in the same row of his alter any table permission is changed from No to Yes, indicating that Lisi has an allocation function for that permission.
Object permissions are passed:
Similar to the transfer of system permissions, but the following options change:
Join SYS to create a table. Select permission granted to Lisi:
Grant SELECT on A to Lisi;
If you want Lisi to have the ability to allocate the SELECT permission for table A, simply modify it to:
Grant SELECT on A to Lisiwithgrantoption;
Think: If the SYS administrator revokes lisi permissions, is WANGW's permission also revoked?
Managing Permissions through Roles
If you follow the rights management method above, assigning permissions to each user one by one will inevitably be confusing and lead to management difficulties. So Oracle provides a role to manage permissions in aggregate.
A role is a collection of permissions.
Create a role under SYS:
Create role Myrole;
To add permissions to a role:
Grant create session to Myrole;
Grant CREATE table to Myrole;
To create a user:
Create user Zhangsan;
Grant Myrole to zhangsan;//gives the above two permissions to Zhangsan
Some system permissions cannot be directly assigned to a role because the permission is too large, such as unlimited tablespace.
Example: Execute Grant unlimited tablespace to Myrole;
Error message: Unable to grant unlimited tablespace role
To remove a role:
Drop role Myrole;
Permission examples:
CREATE TABLE create any table
[ALTER TABLE] ALTER ANY table
[Delete Table] Delete any table
Supplemental: The Oracle database does not contain a purple permission type. Because of the CREATE TABLE permission, everything on the table is attributed to the creator. You do not need to also specify ALTER TABLE and DROP TABLE permissions, which are available by default.
And the Create any table this permission indicates that the user can create tables for other users.
Example: Wangwu Creating a table for Lisi temp
Create tablelisi.temp (id int);//There is a possibility that the "space quota exceeded tablespace ' users" error is indicated because the Lisi user may not have the Tablespace permission to perform the assigned Lisi user Unlimited Tablespace permissions, the problem can be
Solve.
Note: The table belongs to a user. The role is not part of a user.
Oracle Three login verification mechanisms
Operating system validation
Password file validation
Database validation
For a large number of ordinary users, the database started, the user logged in using the database authentication.
and the corresponding SYS user, its permissions are the largest. It even includes the ability to start and close the database. It connects to the Oracle database and starts when the Oracle database is not started. It is not difficult to understand that Sys's authentication is not possible with database validation because the database has not yet started. So SYS's authentication uses the operating system authentication and password file verification (which is not very strict, should be logged in both SYSDBA and Sysoper connection authentication in both ways).
When a user connects to the database, the client first connects to the listening service, the listener sends the request to the database, and if the validation passes, it does not need to be monitored, and the client communicates directly with the DB instance.
Early Oracle running on Linux and UNIX, it has a strict boot sequence: Start the listener first (just a command, no permissions required), and then start the DB instance (requires permission)
Sequence of commands executed: Lsnrctl start//Start monitoring service
Sqlplus Sys/oracle as sysdba//the request to start the DB instance and discovers that it is connected as SYSDBA, so the database is not validated, but the operating system and password file authentication are used. If validation passes, run the start DB instance
Startup//Start DB instance
Earlier versions of the command had to be written like this:
Lsnrctl start
Sqlplus/nolog
Conn Sys/oracle as Sysdba
Startup
In the startup process of Oracle under Windows, a fool-type package was made:
Lsnrctl start
Oradim-starup-sid ORCL
Add: When connecting to the database, you can write: Conn/as sysdba can also be repeatedly, or even randomly specify the user name and password, such as: Conn ABC/ABC as SYSDBA can log in. This is because the connection is in the SYSDBA identity, first with the operating system authentication. When we install the database, the account of the current system is added to the System administrators group of Oracle. Connected as above, it is passed by default based on the current account verification of the system. After you delete the system account in the Administrators group, he will use the password authentication mechanism, you must specify the user name and password.
Question: What if I lose my password?
We know that if an ordinary user's password is forgotten, we can modify the user's password as an administrator (unable to view it because the password is secret and can only be modified)
Modifications can be made directly under the graphical tool. You can also do this in the form of commands:
Alter user Scott identified by Tiger;
In the actual development, we need to verify the operating system to cancel out. After that, the password verification will be used. But if we forget the password, how to solve it?
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 refers to the database SID, may not be the same
After deleting the password file, regenerate it into one. Use the ORAPWD command, as follows:
orapwdfile=< the full path of the password file, the name of the password file to follow the previous > password=< specified password > entries=< the maximum number of DBA saved in the password file > force= only force overwrite file operations
Example: Orapwd file=e:\oracle\ora92\database\pwdora9i.ora password=sys entries=10;
Use the following statement to see how many privileged users are placed in the password file:
SELECT * from V$pwfile_users;
To create a user:
Create user username
Identified by password
Default Tablespace table Space
Temporary tablespace table Space
Quota integer k| m|unlimited on table space
Example:
Create user ABC
Identified by ABC
Default tablespace users//user has the defaults tablespace, where users can create tables under this table space
Temporary tablespace temp//user temp table space for indexing, sorting, and other temporary locations that are equivalent to temporary folders under Windows
Quota 50M on users//Specify the quota size for the users table space
Quota Unlimited on temp; Specify a quota size for a temporary table space
Restrict users
User Lock
Alter user Username account lock
User Unlocked
Alter user username account unlock
User Password immediately expires
Alter USER username password expire
To delete a user:
Drop user username [cascade]
Cascade is used to force cascading deletions when the deleted user has an object that is not deleted (such as some tables). It represents the deletion of all objects of the user.
