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;