Oracle_ Advanced Features (5) Users, roles, permissions

One, user (mode)
1. Definition
User: Access to the database requires authentication with the appropriate user, and has the relevant permissions to complete a series of actions
Schema: is a collection of objects owned by a user.
A user with the Create Object permission and created object is called owning a pattern
Attention:
Any user who creates a database object (view, table, and so on) has a pattern that is the same as the user name.
and is treated as a modal user.

2. User classification:
Users are divided into: System pre-defined users, custom users.
System predefined users include:
SYS user, which is always created by default and is not locked, has a data dictionary and all objects associated with it
System user, is always created by default, is not locked, can access all objects within the database
Scott users, and so on.
Custom User:
user created by the consumer himself.

3. Create user
Condition: Requires permission to create user, such as Sys,system
Syntax:
Create user user_name
identified by password
[ Default tablespace tablespace_name]
[temporary tablespace tablespace_name]
[Quota {n {[k|m] | unlimited} on tables Pace_name,
Quota {n {[k|m] | unlimited} on tablespace_name ...]
[Password expire]
[Account {lock | unlock}]
Example:
Create user find identified by find default Tablespace ts_f Ind
Description:
User_name: Is the user name created
identified by password: Set user's password
[default tablespace tablespace_name]: User defaults tablespace, can be omitted.
[Temporary tablespace tablespace_name]: The user's default temporary tablespace can be omitted.
[Quota. : User table space limit, can be omitted.
[Password expire]: Sets the expiration date of the password, which can be omitted.
[Account {lock | unlock}]: Accounts locked and unlocked, can be omitted.

To view basic user information:
SELECT * from dba_users where username= ' FIND ';
To view the user tablespace quota (dba_ts_quotas):
Select username,tablespace_name,max_bytes/1024/1024 "Max MB"
From Dba_ts_quotas where username= ' FIND ';

4. Modify the user
Modifying the user's syntax is the same as creating the user, simply replacing the keyword create with the alter.
Alter user can modify any property other than the user name.
--alter user find rename to Find1;

4.1. Change the password
DBAs can create users and modify passwords
User can change password using ALTER USER statement
sql> alter user find identified by 123;

4.2 User Locking and unlocking
Alter user find account lock;
Alter user find account unlock;

4.3 Changing the user's quota on the tablespace:
Alter user find quota 8M on Ts_find;

To view the user tablespace quota (dba_ts_quotas):
Select username,tablespace_name,max_bytes/1024/1024 "Max MB"
From Dba_ts_quotas where username= ' FIND ';
Alter user find quota unlimited on ts_find;

5. Login, use User
5.1 Login
Connect find/123;
ora-01045
Connect Sys/[email protected] as SYSDBA;
Grant create session to find;
=
Grant connect to find;
Connect Find/[email protected];

Grant resource to find;

5.2 Copying tables
CREATE TABLE EMP as SELECT * from Scott.emp;
ORA-00942: Table or view does not exist
Grant Create any table,alter any table,drop any table to find;
Grant Select any Table,insert any table,update any table,delete any table to find;
CREATE TABLE EMP as SELECT * from Scott.emp;
SELECT * from EMP;

5.3 Viewing the objects owned by the user
SELECT * from User_objects;
SYS users can view the objects owned by each user using the Dba_objects view
Select Owner,object_name,object_type from Dba_objects where lower (owner) = ' Find ';

6. Delete the User:
Drop user username [cascade]; --Cascade
Example:
Drop user Find;
ORA-01940: Unable to delete the currently connected user
ORA-01922: Must specify CASCADE to remove ' FIND '
Drop user find cascade;
Description
Casecade: Delete user when deleted with user-created object.
If the user has previously created an object, it must be cascade when deleted, otherwise it will not be deleted.
You cannot delete a user who is currently connected to an Oracle server.

7. Role: A set of permissions, called a role.
--Role View
SELECT * from Dba_roles;
--Role Permissions View
SELECT * from Role_sys_privs ORDER by role

SELECT * from Role_sys_privs where role= ' CONNECT ';
SELECT * from Role_sys_privs where role= ' RESOURCE ';
SELECT * from Role_sys_privs where role= ' DBA ';

--User-owned roles
--sys
SELECT * from Dba_role_privs where grantee= ' SCOTT ';
--scott
SELECT * from User_role_privs;

--User-owned role permissions
SELECT * from Role_sys_privs rp,user_role_privs ur
where Rp.role=ur.granted_role ORDER by role

Second, Oracle Permissions
System permissions: Allows users to perform specific database actions, such as creating tables, creating indexes, connecting to instances, etc.
Object permissions: Allows the user to manipulate certain objects, such as reading a view, to update certain columns, execute stored procedures, and so on

1. System permissions
There are 208 system permissions:
SELECT * FROM System_privilege_map
Such as:
Create Class 62:
Select name from System_privilege_map where lower (name) like '%create% ' ORDER BY privilege DESC
Drop class of 35:
Select name from System_privilege_map where lower (name) like '%drop% ' ORDER BY privilege DESC
Alter class of 33:
Select name from System_privilege_map where lower (name) like '%alter% ' ORDER BY privilege DESC
Select Class of 7:
Select name from System_privilege_map where lower (name) like '%select% ' ORDER BY privilege DESC
Insert Class 3:
Select name from System_privilege_map where lower (name) like '%insert% ' ORDER BY privilege DESC
Update Class 4:
Select name from System_privilege_map where lower (name) like '%update% ' ORDER BY privilege DESC
Delete Class 3:
Select name from System_privilege_map where lower (name) like '%delete% ' ORDER BY privilege DESC
Execute class 12:
Select name from System_privilege_map where lower (name) like '%execute% ' ORDER BY privilege DESC
49 Other Categories:
Select name from System_privilege_map
where lower (name) not like '%create% '
and lower (name) not like '%alter% '
and lower (name) not like '%drop% '
and lower (name) not like '%select% '
and lower (name) not like '%insert% '
and lower (name) not like '%update% '
and lower (name) not like '%delete% '
and lower (name) not like '%execute% '
Order BY name

1.1 Common system permissions:
Create Session Creation Sessions
Create sequence creating a sequence
Create synonym creating an object with the same name
CREATE table creates tables in user mode
Create any table in any mode
drop table deletes tables in user mode
Drop any table to delete tables in any mode
CREATE procedure creating a stored procedure
Execute any procedure stored procedure that executes any pattern
The Create user creates users
Drop User Delete users
Create View creating views

1.2 Granting User system permissions
Grant privilege to [User|role|public] [with admin option];
Description
Public All Users
With admin option The user also has the right to assign permissions to grant this permission to others
Example 1:
Sql> Grant create user to Scott;
Sql> Conn scott/123;
Create user find02 identified by find default Tablespace ts_find;
--Verification
Select Grantee,privilege,admin_option from Dba_sys_privs
where lower (grantee) = ' Scott ' ORDER by grantee
--all system privileges owned by the user
Select privilege from User_sys_privs
UNION ALL
Select privilege from Role_sys_privs rp,user_role_privs ur
where Rp.role=ur.granted_role
==>
Select privilege from User_sys_privs
Union
Select privilege from Role_sys_privs;

Example 2:
Sql> Grant execute any procedure-to-Scott with admin option;
Sql> Conn scott/123; --scott has with admin option, so execute any procedure can be granted to find
Sql> Grant execute any procedure to find;
Sql> Grant execute any procedure to public; --Grant execute any procedure to all users
--Verification
Select Grantee,privilege,admin_option from Dba_sys_privs
where lower (grantee) in (' Scott ', ' find ', ' find02 ') Order by grantee

Example 3:
Sql> Grant create any table to find02;
Select Grantee,privilege,admin_option from Dba_sys_privs
where lower (grantee) in (' Scott ', ' find ', ' find02 ') Order by grantee

1.3 Using System permissions
--Use Find to create a session, create a table
Sql> CREATE TABLE TB1 as SELECT * from User_tables;
--The following prompt does not have permission to create objects in the users table space
Sql> Conn Sys as SYSDBA; --You can create a table after logging in with the SYS account and specifying quotas for find in the Users table space tb1
sql> alter user find quota 10m on users;
Sql> Conn Find/find;
Sql> CREATE TABLE TB1 as SELECT * from User_tables;

1.4 Viewing System permissions
Dba_sys_privs--system permissions granted to all users
SELECT * from Dba_sys_privs;
User_sys_privs--system permissions granted to the current logged-on user
SELECT * from User_sys_privs;
--
Select Grantee,privilege,admin_option from Dba_sys_privs
where lower (grantee) in (' Scott ', ' find ') order by grantee;

1.5 Reclaim System permissions
Revoke {Privilege|role} from {user_name|role_name|public}

Example 1: Reclaim Permissions
Revoke execute any procedure from Scott;
Select Grantee,privilege,admin_option from Dba_sys_privs
where lower (grantee) in (' Scott ', ' find ') order by grantee;
Note: For use with admin option to grant system permissions to a user,
For other users who are granted the same permissions by this user,
Canceling the user's system permissions does not cascade the other same permissions.

Example 2: Recycling roles
Revoke connect from Find;
SELECT * from Dba_role_privs where grantee= ' FIND ';
Grant connect to find;

2. Object permissions
Different objects have different object permissions.
The owner of the object has all permissions.
The owner of the object can assign permissions outside.

Revoke insert any table from find;
Revoke update any table from find;
Revoke delete any table from find;
Revoke select any table from find;

--Object Authorization Example
Grant SELECT on EMP to find;
Grant Update (SAL,COMM) on the EMP to find;

Oracle has a kind of object permissions

Object Permission Table View sequence procedure
Select (select) √√√
Inserting (insert) √√
Updated (update) √√
Remove (delete) √√
Creating (Create) √√√√
Modify (Alter) √√
Drop (drop) √√√√
Index (indexed) Yes
Association (references) √√
Execute (Execute) Yes

2.1 Object Authorization
Grant Object_priv|all [(columns)] on object to {user|role|public} [with GRANT option];
Description
All: All rights to the limit
Public: granted to all users
With GRANT OPTION: Allow the user to authorize another user again

2.2 Syntax differences between granting system permissions and granting object permissions:
When granting object permissions, you need to specify the keyword on, which enables you to determine which object the permission applies to.
For tables and views, you can specify a specific column authorization.

--Create a new user, John, to grant update scott.emp (SAL,MGR) permissions using the Find account
--create user John identified by John;
--grant create session to John;
--conn find/lion
Grant Update (SAL,MGR) on scott.emp to FIND02; --Grant Scott.emp (sal,mgr) the update permission

Update scott.emp Set sal = Sal + where ename = ' Scott '; --Successful update
--Assign permissions to all users in the database
Grant SELECT on Dept to Public;

2.3 Query Permission Assignment status
Data dictionary View description
Role_sys_privs role-owned system permissions
Role_tab_privs role-owned object permissions

User_tab_privs_made Query the granted object permissions (usually owned by the owner)
USER_TAB_PRIVS_RECD user-owned object permissions

User_col_privs_made object permissions for user-assigned columns
USER_COL_PRIVS_RECD user-owned object permissions on columns

User_sys_privs user-owned system permissions
User_tab_privs user-owned object permissions
User_col_privs user-owned Object column permissions
User_role_privs User-owned roles
SELECT * from Dba_tab_privs where grantee= ' public ' and owner= ' SCOTT ';

--Query granted object permissions (that is, which tables a user has permission to object to which users)
SELECT * from User_tab_privs_made; --The following are the open object permissions for Scott users
--Open Object permissions on the query column
SELECT * from User_col_privs_made;
--Query the Accepted object privileges (that is, which objects privileges on which tables a user has been granted)
SELECT * from USER_TAB_PRIVS_RECD;
--Query object permissions for user accepted columns
SELECT * from USER_COL_PRIVS_RECD;

2.4 Revoke object permissions
Using the REVOKE statement to reclaim permissions
The permissions assigned by using the WITH GRANT OPTION clause are also retracted

Revoke Privilege|all on object from {user|role|public} [cascade constraints];
Description
Cascade constraints is required to handle referential integrity

Revoke update (SAL,MGR) on the EMP from find;
Ora-01750:update/references can only be REVOKE from the entire table and not by column
--
Revoke update on the EMP from find;

--Revoke Permission example
Conn Scott/tiger;
Revoke select on the EMP from find;
--Reclaim public object permissions
Revoke select on Dept from Public;

-Note that the hint here revoke the entire table, not the column
--revoke Update (SAL,MGR) on the EMP from find;
Revoke update on the EMP from find;

--The update permission of the user find is revoke, and the permission granted to John by the Cascade is also retracted.
--The following hint table, view does not exist, no record in USER_COL_PRIVS_RECD
Conn John/john;
--update scott.emp Set sal = sal-100 where ename = ' Scott ';
SELECT * from USER_COL_PRIVS_RECD;

Connect scott/123;
Grant Select on Scott.emp to find with GRANT option;
Connect Find/find;
Grant SELECT on Scott.emp to FIND02;
Connect scott/123;
Revoke select on Scott.emp from Find;
Note: If you cancel the object permissions for a user, for that user granting the same permissions to other users using with GRANT option,
To remove these user rights by cascading

2.5 Other
Users who check for DBA authority
SELECT * from Dba_role_privs where granted_role= ' DBA ';

To view the system permissions that a user has:
SELECT * from Session_privs;

Iv. Summary
1. Use the Create USER statement for creating users, ALTER USER statement to modify users, with roughly the same syntax
Drop user username [cascade] will delete all objects and data owned by users
2. System permissions allow the user to perform specific operations in the database, such as executing DDL statements.
With admin option gives the user the ability to grant himself access to other users;
However, when system permissions are retracted, the same permissions that were granted are not canceled from the other account cascade.

3. Object permissions allow users to perform specific operations on database objects, such as executing DML statements.
With GRANT option gives the user the ability to grant their own object permissions to other users;
But when object permissions are retracted, the same permissions that were granted are canceled from the other account cascade.
4. Syntax differences when system permissions and object permissions are granted use the ON OBJECT_NAME clause for object permissions
5. Public for all users
6. All: Permissions for all objects in object permissions
7. Practical examples
Connect System/[email protected];
--Delete user--
Drop user find cascade;
--Delete Table space--
Drop tablespace ts_find including contents;
--Create table space and data files--
Create tablespace ts_find datafile ' D:\app\oradata\orcl\find. DBF ' size 100M reuse autoextend on next 10M;
--Create user and authorize--
Create user find identified by Find_password default Tablespace ts_find;
Grant Resource,connect to find;
Grant Select any sequence to find;
Grant Create any table,alter any table,drop any table to find;
Grant Select any Table,insert any table,update any table,delete any table to find;
Grant Create any trigger,alter any trigger,drop any trigger to find;
Grant Create any procedure,alter any procedure,drop any procedure,execute any procedure to find;
Grant Create any View,drop any view to find;
Grant create any synonym to find;

Oracle_ Advanced Features (5) Users, roles, permissions