Oracle users, privileges, and roles

Oracle users, privileges, and roles

This article mainly includes:

·Introduction to User Creation

·Learn how to use privileges to ensure that users can execute tasks in the database

·Two types of privileges are introduced: SYSTEM privileges and object privileges.

·Describes how SYSTEM privileges allow operations, such as executing DDL statements.

·Describes how object privileges allow operations, such as executing DML statements.

·Describes how to combine privileges to form roles.

I. Users

The database uses tablespaces to store objects, including tables, types, and PL/SQL code.

Tablespace is stored in datafile

1. Create a user

Syntax:

Create user user_name identified by password

[Default tablespace default_tablespace]

[Temporary tablespace temporary_tablespace];

You can query the default tablespace and default temporary tablespace from user_users.

2. Change the User Password

Alter user user_name identified by password;

PASSWORD can be used to modify the PASSWORD of the current Login User

3. delete a user

Drop user user_name;

Note: If the USER mode to be deleted contains any table or other items, you must

Statement, add the keyword CASCADE after the user name to be deleted

Ii. SYSTEM privileges

System privilege allows users to perform specific operations in the database, such as executing DDL statements.

Common system privileges are as follows:

Permissions are combined to form a role. Two useful roles are CONNECT and RESOURCE. <喎?http: www.bkjia.com kf ware vc " target="_blank" class="keylink"> VcD4KPHA + release/4 rbUz/release + MS4gz/LTw7unytrT6M + release/release + My4gyrnTw8 + release + zb /users + i1xM + 1zbPM2Mioo6yy2df3u + users/users + CjxwPr/users + Cgo8cD7I/aGiILbUz/users/4 rbUz/users =" http://www.2cto.com/uploadfile/Collfiles/20141201/20141201092221106.png "Alt =" \ ">

1. Grant object privileges to users

You can use the GRANT statement to GRANT object privileges to users.

Example: connect to the database as a store user and grant the following privileges to the steve User:

SELECT, INSERT, and UPDATE object privileges for the products table and SELECT privileges for the table employees

CONN store/store_password

Grant select, INSERT, update on store_products TO steve;

Grant select on store. employees TO steve;

Note: You can use the GRANT option to GRANT this privilege to other users.

Grant select on store. customers TO steve with grant option;

2. Check the granted object privileges

By querying user_tab_privs_made, you can check which tables a user has granted the object privileges to other users.

Example: retrieve rows whose table_name is PRODUCTS

SELECT grantee, table_name, grantor, privilege, grantable, hierarchy

FROM user_tab_privs_made

WHERE table_name = "PRODUCTS ';

By querying user_col_privs_made, you can check which columns and objects a user has opened.

Example: Query user_col_privs_made

SELECT grantee, table_name, column_name, grantor, privilege, grantable

FROM user_col_privs_made

Order by column_name;

Note:

·Use the with admin option to pass system privileges

·Use with grant option to pass object privileges

3. Check accepted object privileges

By querying the user_tab_privs_recd table, you can check which tables a user has granted which object privileges

Example: connect to the database as a steve user and query user_tab_privs_recd

SELECT owner, table_name, grantor, privilege, grantable, hierarchy

FROM user_tab_privs_recd

Order by table_name, privilege;

By querying user_col_privs_recd, you can check which columns a user has granted the object privileges.

Example: Query user_col_privs_recd

SELECT owner, table_name, column_name, grantor, privilege, grantable

FROM user_col_privs_recd;

4. Use object privileges

After a user is granted the Object Privilege, the user can use this privilege to execute a specific task,

For example, steve has the SELECT privilege for store. MERs.

CONNECT steve/button

SELECT * FROM store. MERs MERS;

NOTE: If steve tries to search the purchases table (steve does not have any permission for this table), the database reports an error.

SELECT * FROM store. purchases;

5. Create a synonym

Example: connect to the database as a system user and grant the create synonym system privilege to the steve user

CONN system/oracle

Grant create synonym to steve;

Example: connect to the database as steve and execute a create synonym statement to CREATE a SYNONYM for the store. MERs table.

CONN steve/button

Create synonym MERs FOR store. MERs MERS;

6. Create a public Synonym

You can create public synonym for a table. After creating a public synonym, all users can

When you see this synonym, the following four statements execute the following tasks:

·Connect to the database as a system user

·Grant the create public synonym system privilege to the store user

·Connect to the database as a store user

·Create a public synonym for store. products

CONNECT system/oracle

Grant create public synonym to store;

CONN store/store_password

Create public synnonym products FOR store. products;

7. revoke the user's object privileges

You can use the REVOKE statement to REVOKE the object privileges of a user.

Example: connect to the database as a store user and revoke the INSERT privilege of steve on the products table

CONN store/store_password

Revoke inser on products FROM steve;

Note:

·When the system privilege is revoked, the privilege granted to other users by the user will not disappear.

·When the object is revoked, the privileges granted to other users by the user will also disappear.

Iv. Roles

A role is a set of privileges that can be assigned to users or other roles. The advantages of a role can be summarized as follows:

·Instead of granting privileges to users one by one, you can create roles and grant them some privileges at a time,

Then, assign the role to multiple users and roles.

·When a role is added or deleted, all users and roles granted to this role will automatically obtain

New or automatically lost privileges

·You can assign multiple roles to users or roles.

·You can set a password for a role.

1. Create a role

To CREATE a ROLE, you must have the create role system privilege.

2. Authorize a role

3. Grant the role to the user

4. Check the role granted to the user

By querying user_role_privs, you can check which roles have been granted to users.

Note:

·The password-protected role is disabled. You must enter a password to use this role.

·The user who created the role will also be assigned this role.

5. Check the system privileges granted to the role

By querying role_sys_privs, you can check which system privileges have been granted to the role.

6. Check the object privileges granted to the role

By querying role_tab_privs, you can check which object privileges have been granted to the role.

7. Use the privileges granted to the role

For a role without password protection, after the role grants the user the privileges, the user connects to the database

You can use this privilege immediately;

For a password-protected role, you must enter the role password to use this role.

Set role role_name identified by role_password;

8. Enable and disable roles

The ROLE can be disabled. The alter role statement can be used to modify the ROLE and change it to a non-default ROLE.

Example: connect to the database as a system user and modify john so that hr_manager is no longer the default role

CONN system/oracle

Alter user johnDEFAULT role all roles t hr_manager;

Example: use the set role command to enable the hr_manager ROLE

Set role hr_manager;

Example: Set the hr_manager role as the default role, which is retained after logging out.

CONN system/oracle

Alter user johnDEFAULT ROLE hr_manager;

Example: You can set the role to NONE, which indicates that there is no role

CONN john/brown

Set role none;

Example: Set a role to any role except hr_manager

Set role all roles t hr_manager;

9. revoke a role

The REVOKE statement can be used to REVOKE a role.

REVOKE role_nameFROM user_name;

10. Revoke privileges from a role

A revoke statement can be used to REVOKE a certain privilege from a role.

Revoke all on table_name FROM role_name;

11. delete a role

The drop role statement can be used to delete a ROLE.

Drop role role_name;