Oracle Database Rights Management

Rights Management: Oracle 9 has the default three username and Password:sys Change_on_install//The most privileged Administrator System Manager//General Administrator Scott Tiger//general user in Oracl E 10, these three users are still used as default users.

However, SYS and system user password no longer default.

Rights Management:

The default three username and password 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, SYS and system user password no longer default. When you install the database. Can be specified by the user.

From a security standpoint, the Scott user is locked out by default, so to use that user,

Need 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; Created a user named Lisi, password also for Lisi

Although the user was created. But the user now has no authority whatsoever.

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, there is no permission to create a database table now. Still need to be specified.

The statement is: Grant CREATE table to Lisi;

Using the Lisi account, create a database table: Creating table MyTable (id int);

Error after running: No permissions on tablespace ' USERS '. Each database table has its own table space, which is the equivalent of a file that must be located in a directory.

Although the Lisi user has permission to create the table, but does not have permission to use the tablespace, the table is finally created.

It's like you have the key to my room. But without the key to my house, you will not be able to enter 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.

can also delete tables: drop table mytable;

Some people may have doubts, since the database has such strict rights management. Above, we are simply granting Lisi users permission to create tables. And does not grant it the right to insert, delete, etc. Here we can understand that: the current user has created 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.

Above is grant permission. So how do you revoke a user's permission?

Use the following statement, for example, to revoke the permissions of the Lisi creation table: Revoke create table from Lisi;

Then use the Lisi account to create the table. An error message appears: Insufficient permissions.

In most cases. Suppose we often change the permissions of the user, 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 view the user's detailed permissions.

Use the following statement, for example, 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 access to the table? This is known as Object Rights Management.

We are creating a user Wangwu. Password also for Wangwu. Under the user, create a table mytab. Suppose the Lisi user visits the table Mytab, will it succeed?

Under the Lisi form. Input: SELECT * from Mytab; error: 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 to find the table. Obviously the current user Lisi does not have a table mytab.

So the hint table or view does not exist.

Then we specify the entire list of people. Query again: SELECT * from Wangwu.mytab; form displays "Insufficient permissions" error prompt.

Can be seen Although the Mytab table was found, there was no access to the interview.

Only the owner of the table has the ability to grant the table the relevant permissions to other users.

Use the Action form for user Wangwu. The query statement is granted Lisi using the following statement, for example;

Grant SELECT on Mytab to Lisi;

After you run this statement. Lisi will be able to query the user Wangwu Mytab table.

Suppose you want to get other 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;

Suppose you want to give all the permissions of the table to the user Lisi. Be able to write like this;

Grantallon Mytab to Lisi;

In the Wangwu form, insert a few data into the mytab. and then query. "Unselected Rows" is displayed. Indicates that the insertion was not synchronized to the database.

Under Oracle. The default is to manually commit the SQL statement. So after a few insert statements, you can run commit; Query again, the table has data.

Assume that you want to grant a permission to all users. Ability to use Publickeyword:

Grant Create session Topublic;

View object permissions for the current user. Use the following statements, for example:

SELECT * from User_tab_privs;

Oracle's permissions control granularity is very granular, even to a specific column of permissions.

Grant update (name) on Mytab to Lisi;

The effect of this operation 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, run: Update wangwu.mytab set name= ' FDSFA ', id= "Dfs" where id=1;

Insufficient display permissions after running.

Update Wangwu.mytab set name= "FSA" where id=1;

So that's it.

Same run: INSERT INTO Wangwu.mytab values (4, "ASF"), and also show insufficient permissions after running.

The change statement is: Inset into Wangwu.mytab (ID) VALUES (4), running successfully.

Only the update and insert settings are accurate to a certain column of permissions control, can not 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 change the operation. Only DML is required for the commit operation.

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 run the following statement: Grant alter any table to WANGWU; Run report "Insufficient permissions".

To Lisi, you can also pass permissions, which can be added with the option of with admin option when the SYS user authorizes, and this option illustrates 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 use the admin option if you want WANGWU to be able to pass this permission.

View the system permissions for the Lisi. His alter any table permission on the same line of the ADM field 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, except that the following options are changed:

Add SYS to create a table. Select permission granted to Lisi:

Grant SELECT on A to Lisi;

Suppose you want Lisi to have the ability to allocate the SELECT permission for Table A, just change it to:

Grant SELECT on A to Lisiwithgrantoption;

Think: Assuming that the SYS administrator revoked Lisi permissions, is the WANGW permission also revoked?

Managing Permissions through Roles

Assume that you follow the permissions management method above. Assign permissions to each user individually. Must be very confusing, leading to difficulties in management. 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, for example unlimited tablespace.

For example: Run 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. The default is there.

The Create any table this permission indicates that the user is capable of creating tables for other users.

Demo Sample: Wangwu Create a table for Lisi temp

Create tablelisi.temp (id int), or//may be reported as "exceeding tablespace ' space limit" error prompt, that is because the Lisi user may not have tablespace permissions, run to give Lisi user unlimited The permissions of the tablespace. 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 starts. Database authentication is used when the user logs on.

and the corresponding SYS user. Its permissions are the largest. Its permissions even include starting and shutting down the database. It connects to the Oracle database and starts when the Oracle database is not started.

It is not difficult for us to understand. SYS authentication is not possible with database validation because the database was not started at the time. So SYS's authentication uses the operating system validation and password file validation (which is not very strict, it should be logged in both SYSDBA and Sysoper connection authentication in both ways).

When a user connects to a database. The client first connects to the listening service, and the listener sends the request to the database. Assuming the validation passes, there is no need to listen later. The client communicates directly with the DB instance.

Early Oracle on Linux and UNIX, it has a strict boot sequence: Start the listener first (you just need to hit a command, you don't need permission), and then start the command sequence for the DB instance (permissions required)

to run: lsnrctl Start//Start the Listener service

Sqlplus sys/oracle as sysdba//the request to start the DB instance. The discovery is connected as SYSDBA, so the database is not validated, but the operating system and password files are validated. Assume that validation passes. Execute Start DB instance

Startup//Start DB instance

Previous version number command to write this:

lsnrctl start

Sqlplus/nolog

Conn Sy S/oracle as SYSDBA

Startup

In the startup process of Oracle under Windows, a fool-type package:

lsnrctl start

Oradim-staru P-sid ORCL

Supplement: When connecting to a database. Can write this: Conn/as Sysdba can also be repeatedly on. Even randomly specify username and password, such as: Conn ABC/ABC as SYSDBA can be landed. This is because the connection is based on the SYSDBA identity, first adopted by the operating system authentication.

When we install the database. The current system's account will be added to the Oracle's System Admins group. Connect as above. It is passed by default based on the current account validation of the system. After deleting the system account in the Administrators group. He will use the password authentication mechanism. You will need to specify username and password.

Question: What should I do if I lose password?

We know that assuming the password of the ordinary user forgets, we can the Administrator's identity to the user's password to make the change (cannot see, because password all is dense, can only change)

Can be changed directly under the graphical tool.

can also be ordered in the form of:

Alter user Scott identified by Tiger;

In the actual development. We need to verify the operating system to cancel it out. After that, it will be verified with password.

But if we forget the password, how can we 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 SID of the database. May not be the same

After deleting the password file, regenerate it into one. Use the ORAPWD command, details such as the following:

The full path of the Orapwdfile=<password file, the password file is named according to the previous > password=< specified password> entries=< The maximum number of DBAs saved by the password file > force= only enforces overwrite file operations

Demo Sample: Orapwd File=e:\oracle\ora92\database\pwdora9i.ora Password=sys entries=10;

Use the following statement to see how many privileged users were 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

Demo Sample:

Create user ABC

Identified by ABC

The default tablespace for users//user is tablespace. Under this table space, users can create tables

Temporary tablespace temp//user's temporary table space, used for indexing, sorting and other temporary places of work. Equivalent to a temporary directory under Windows

Quota 50M on users//Specify the quota size for the users table space

Quota Unlimited on temp; Specify the quota size for a temporary tablespace

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 when there are non-deleted objects (such as some tables) under the deleted user. enforces cascading deletions. It represents the deletion of all user objects.

Oracle Database Rights Management