Oracle(16)Managing Privileges

Table of Contents

1. Basic knowledge

1. Managing Privileges management permissions

2. System Privileges System privileges

3. System Privileges: Example System Privileges: Example

4. Who Can Grant or Revoke? Who can grant or revoke permissions?

5.The PUBLIC

6.SYSDBA and SYSOPER

7. Revoke with ADMIN OPTION Use ADMIN OPTION to revoke

8. Some Points Some system permission points

9. Revoke with GRANT OPTION Use GRANT OPTION to revoke

10. Some Points Some object permission points

2. Basic operations

1. Granting System Privileges Granting system privileges

2. Revoking System Privileges revoke system permissions

3. Granting Object Privileges Granting object permissions

4. Revoking Object Privileges Revoking object permissions

Managing Privileges

Target:

  • Identify system and object permissions
  • Grant and revoke privileges
  • Get permission information

1. Basic knowledge

1. Managing PrivilegesManagement rights

There are two types of Oracle user privileges:

  • System system: enables users to perform specific operations in the database
  • Object object: enables users to access and operate specific objects

2. System Privileges System Privileges

  • There are over 100 different system permissions
  • The ANY keyword in privileges indicates that the user has permissions in any architecture (be careful when granting ANY privileges)
  • GRANT command adds privileges to a user or group of users
  • The REVOKE command removes privileges.

3. System Privileges: ExampleSystem Privileges: Example

There is no ordinary CREATE INDEX permission in INDEX, because the permission to create indexes is granted when CREATE TABLE creates the table permission.

4. Who Can Grant or Revoke? Who can grant or revoke permissions?

There are only two types of users who can grant system privileges to other users or revoke those privileges:

  • Use the ADMIN option to grant specific system privileges to users
  • Users with system permissions grant any permissions

Grant these privileges only to trusted users.

5.The PUBLIC

PUBLIC is a user group defined in the database; it is not a database user or role. Every user in the database belongs to this group. Therefore, if you grant privileges to PUBLIC, they are available to all users of the database.

6. SYSDBA and SYSOPER

7. Revoke with ADMIN OPTION Use ADMIN OPTION to undo

When we revoke permissions, we only revoke the user’s own permissions and will not cascade revoke the permissions that the user has granted.

8. Some Points Some System Permissions Points

  • To connect to the database you need CREATESESSION privilege
  • To drop a table belonging to another schema, you need DROP ANY TABLE privilege.
  • The CREATE ANY PROCEDURE (or EXECUTE ANY PROCED) privilege allows users to create, replace, or delete (or execute) procedures, packages, and functions, including Java classes. (Pay special attention to this, as system bugs may be used to create dba permissions)
  • CREATETABLE privilege enables you to create, alter, drop, and query tables in the schema.
  • SELECT, INSERT, UPDATE, and DELETE are object privileges, SELECTANYINSERTANY, UPDATEANY, and DELETEANY are system privileges (in other words, they do not apply to specific objects)

9. Revoke with GRANT OPTION Use GRANT OPTION to undo

10. Some Points of Some Points of Object Permissions

  • Object privileges can be granted to users, roles, or PUBLIC.
  • If a view references another user’s table or view, you must have WITH GRANT OPTION permission on the view’s underlying table before you can grant any permissions on the view to the other user. For example, JOHN has a view that references a table in JAMES. To grant SELECT permission on the view to another user, JOHN should have received SELECT permission on the table WITH GRANT OPTION.
  • Any object privilege received on a table provides the grantee with the privilege to lock the table.
  • SELECT permission cannot be specified on a column; to grant column-level SELECT permission, create a view with the required columns and grant SELECT permission on the view.
  • You can specify all or all privileges to grant all available privileges on the object (for example, grant privileges to all customers to James).
  • Even if you have DBA authority, to grant permissions on an object owned by another user, you must have been granted the appropriate permissions WITHGRANTOPTION on that object.
  • Multiple privileges can be granted to multiple users and/or roles in a single statement. For example, GRANTINSERT, UPDATE, SELECT CUSTOMER TO ADMINISTRATIVE ROLE, JULIE, SCOTT;

2. Basic operations

1. Granting System Privileges Granting System Privileges

  • Use the GRANT command to grant system privileges.
GRANT CREATE permission name (for example: SESSION) TO user name;
  • The grantee can further grant system privileges through the ADMIN option.
GRANT CREATE permission name (for example: SESSION) TO user name WITH ADMIN OPTION;

2. Revoking System Privileges Revoke System Privileges

  • Use the REVOKE command to remove a user’s system privileges.
  • Users with ADMIN OPTION system permissions can revoke system permissions
  • Only privileges granted via the GRANT command can be revoked.
REVOKE permission name (for example: CREATE TABLE) FROM user name;

3. Granting Object Privileges Granting Object Privileges

  • Grant object permissions using the GRANT command
  • The grant must be in the grantor’s mode or the grantor must have a GRANT OPTION.
GRANT EXECUTE ON object name (for example, a table: write the table name here) To the user name to be authorized;
This operation can authorize one's own object (in this example, a table) to other users, and other users can query this table 
GRANT UPDATE ON object TO authorized user name WITH GRANT OPTION:

4. Revoking Object Privileges revoke object permissions

  • Use the REVOKE command to revoke object permissions.
  • The user who revokes the privilege must be the original grantor of the privilege on the object being revoked.
REVOKE SELECT ON object FROM username;