Oracle users, roles, permissions, etc.

Oracle users, roles, permissions, etc.

Privilege Management is the essence of Oracle systems, where different users log in to the same database, may see different numbers of tables, and have different permissions. Oracle's permissions are divided into system permissions and data object permissions, a total of more than 100 kinds, if the user authorization, very embarrassing, some users need the same permissions, the user into the same class-a role, through the establishment of a number of predefined roles to simplify and clear authorization operations, The motive for a role's appearance is to simplify rights management, which is a collection of permissions. The general practice is: The system assigns permissions to the role, then assigns the role to the user, of course, can also directly assign a permission to the user. Oracle provides fine-grained permissions to set permissions on a single column of a table, which automatically adds a where constraint to a user querying a table.

Permissions allow users to access objects or execute programs that belong to other users, and the Oracle system provides three permissions: Object-level, system-level, role-level. These permissions can be granted to a user, to a special user, to public, or to a role, and if a privilege is granted to a special user "public" (the user public is predefined by Oracle and each user has the permissions that the user has), it means that the permission is granted to all users of that database. For administrative permissions, a role is a tool, and permissions can be granted to a role, and a role can be granted to another role or user. The user can inherit permissions through the role, except for the role service, which has no other purpose than administrative permissions. Permissions can be granted, or they can be revoked in the same way.

The role of Oracle resides in table Dba_roles, where the system rights contained in a role are stored in Dba_sys_privs and the object permissions contained in the Dba_tab_privs are stored.
The following are the cross-checks of roles, users, and permissions:

1. User

Cases:

@>conn system/888888connected.system@userdata>  Createuser by User50; User created. SYSTEM@userdata>Grant to User50; Grant succeeded.

View all roles and system permissions owned by the current logged-on user

USER50@userdata>Select *  fromSession_privs; PRIVILEGE------------------------------------------------------------------------------------------------------------- -----------CREATEsessionunlimited tablespaceCREATE TABLECREATECLUSTERCREATESEQUENCECREATE PROCEDURECREATE TRIGGERCREATETYPECREATEOPERATORCREATEIndextypeTenrows selected. USER50@userdata>Select *  fromRole_sys_privs; ROLE PRIVILEGE admin_opt---------------- ------------------------ ---------RESOURCECREATESEQUENCE NoresourceCREATE TRIGGERNoresourceCREATECLUSTER NoresourceCREATE PROCEDURENoresourceCREATETYPE NoconnectCREATESESSION NoresourceCREATEOPERATOR NoresourceCREATE TABLENoresourceCREATEIndextype NO9Rows selected.

View the roles and system permissions that a user has

SYSTEM@userdata>Select *  fromDba_role_privswhereGRANTEE='USER50'; GRANTEE granted_role admin_opt Default_r------------------------------ -------------------- --------- ---------USER50 RESOURCE no YESUSER50 CONNECT No Yessystem@userdata>Select *  fromDba_sys_privswhereGRANTEE='USER50'; GRANTEE PRIVILEGE admin_opt------------------------------ ------------------------------ ---------USER50 UNLIMITED tablespace NO

View object permissions owned by a user

SYSTEM@userdata>Grant Select  onScott.emp toUser50; SYSTEM@userdata>Select *  fromDba_tab_privswhereGRANTEE='USER50'; GRANTEE OWNER table_name Grantor PRIVILEGE grantable HIERARCHY-------------------- --------------- --------------- --------------- --------------- --------- ---------USER50 Scott EMP ScottSELECTNo no

Permissions classification

• System permissions: The system specifies the user's permission to use the database. (System permissions are for users).
• Object permissions: Access to other users ' tables or views by some privileged user. (For a table or view).

1. System Rights Management 1.1 system permissions classification

• DBA: Has full privileges, is the highest system privilege, and only the DBA can create the database structure.
• RESOURCE: A user with RESOURCE permission can only create entities and cannot create a database structure.
• Connect: A user with connect permission can only log on to Oracle, not create an entity, and cannot create a database structure.

For normal users: Grant Connect, resource permissions.
For DBA administration users: Grant Connect,resource, dba authority.

1.2 System Permission Authorization command

[System permissions can only be granted by DBA User: sys, system]
Authorization Command:sql> Grant Connect, resource, DBA to username 1 [, user name 2] ...;

[An ordinary user with authorization can have the same user rights as the system, but never the same permissions as the SYS user, and the permissions of the system user can also be recycled.] ]

Oracle users, roles, permissions, etc.