Schema Privileges- Users and Basic Security
New with 23c, you can now grant a schema to a user. This means all of the objects in the schema will be granted. This simplifies if new objects are added or modified, grants are based on a schema. New tables will be accessible without specifically granting access to that table.
It is still recommended to create a role and grant the privileges to the role, but you can grant to individual users too.
Schema-level privileges granted will show up in the audit trail. Just as privileges can be granted at the schema, they can also be revoked.
There are privileges that are excluded from being able to grant for a schema, which makes sense since they are administrative- and system-level privileges. Here are the privileges excluded:
• SYSDBA
• SYSOPER
• SYSASM
• SYSBACKUP
• SYSDG
• SYSKM
You can grant privileges such as create:
Being able to grant permission at the schema level simplifies granting privileges as objects change in the schema. It still makes sense to create roles with these privileges to manage the permissions for users.
PL/SQL and Roles
If you work with PL/SQL, sometimes you get this error when attempting to compile a procedure or a function:
What is confusing is that you can describe the table:
Why doesn’t the PL/SQL seem to be able to recognize the table? It is because PL/ SQL requires that the owner of the package, procedure, or function be explicitly granted privileges to any objects referenced in the code. The owner of the PL/SQL code can’t have obtained the grants through a role.
When confronted with this issue, try this as the owner of the PL/SQL code:
Now, try to run a SQL statement that accesses the table in question:
If you can no longer access the table, then you have been granted access through a role. To resolve the issue, explicitly grant access to any tables to the owner of the PL/SQL code (as the owner of the table):
You should be able to connect as the owner of the PL/SQL code and successfully compile your code.
Roles are going to provide a way to grant the needed privileges for a function or tasks for the user to perform. As the user maps to security groups, the roles are the best way to manage the privileges. Role-based access to the different objects and system privileges is going to allow simple auditing to know who has a role and verify that there are not individual privileges being granted.
