Managing Privileges- Users and Basic Security
A database user must be granted privileges before the user can perform any tasks in the database. In Oracle, you assign privileges either by granting a specific privilege to a user or by granting the privilege to a role and then granting the role that contains the privilege to a user. There are different types of privileges: system privileges, schema, and object privileges.
Assigning Database System Privileges
Database system privileges allow you to do tasks such as connecting to the database and creating and modifying objects. There are hundreds of different system privileges. You can view system privileges by querying the DBA_SYS_PRIVS view:
System privileges are the same in CDB and PDBs. You can grant privileges to other users or roles. To be able to grant privileges, a user needs the GRANT ANY PRIVILEGE privilege or must have been granted a system privilege with ADMIN OPTION.
Use the GRANT statement to assign a system privilege to a user. For instance, minimally a user needs CREATE SESSION to be able to connect to the database. You grant this system privilege as shown here:
Usually, a user needs to do more than just connect to the database. For instance, a user may need to create tables and other types of database objects. This example grants a user the CREATE TABLE and CREATE DATABASE LINK system privileges:
It’s the same for the schema-only account:
With all of the individual privileges, it is easier to use roles. Whether or not they are provided roles we have discussed such as DBA and DB_DEVELOPER_ROLE, or you can create your own role, but more on the roles in a moment.
If you need to take away privileges, use the REVOKE statement:
Oracle has a feature that allows you to grant a system privilege to a user and also gives that user the ability to administer a privilege. You do this with the WITH ADMIN OPTION clause:
Granting WITH ADMIN OPTION can get quickly out of hand with managing and monitoring privileges. It is recommended to limit the use of this in a production environment and have security controls around roles and granting privileges.
