In the Users Management category, you will find queries to identify the status of accounts, profiles, roles, and assigned privileges.
Definition of a User
When creating a user without options, a default tablespace, profile, temporary tablespace, and UNDO tablespace are set. Additionally, when creating a user, an object SCHEMA is created, initially empty.
User = Schema
For this reason, a USER is called a SCHEMA because a user is composed of an object schema. Moreover, to be able to write or modify database data, the correct authorizations are required.
Roles and Privileges
These authorizations occur through the assignment of ROLES, system PRIVILEGES, and PRIVILEGES on objects. Through these operations, each user can manage only the objects they are allowed to use.
Do you see an account in a locked state?
No problem. In the User Management category, there are statements to quickly identify state changes, such as locked users or expired passwords. Furthermore, you will find dynamic queries to replicate the ROLES and PRIVILEGES of a user with the creation of assignment commands to another USER.
Finally, there are queries to retrieve the tablespace quotas assigned to a user.
Are you having problems with the queries?
Do not hesitate to contact us!
Query to generate the DDL for User Creation, Roles, and Privileges With the article “DDL for User Creation Roles and Privileges”, we provide a series of statements with which we can extract the creation code of a USER. Additionally, it is possible to extract the roles, system privileges, object privileges, Read more…
With the article Query USER PRIVILEGES replicated, we can retrieve all ROLES, system PRIVILEGES, and object PRIVILEGES of a USER and construct the GRANT command assigning them to another USER. When a user is created in the Oracle database, if no roles or privileges are assigned, the user will not Read more…
With the article “Query for Oracle User Object Privileges,” we look at the object privileges assigned to a specific user in the Oracle database. In the Oracle database, there are two types of privileges: system privileges and object privileges. System privileges allow performing certain operations in the database, such as Read more…
The query in the article “Query for Oracle User Roles”, allows us to identify the ROLES assigned to a specific user in the Oracle database by selecting GRANTEE, which is the user receiving the role, and GRANTED_ROLE, which identifies the name of the role. A ROLE is a container for Read more…
The query in the “Assignment of DEFAULT_TABLESPACE Quota” article aims to create the command, through a dynamic query, to assign a user’s quota on their default_tablespace, considering the quota assigned to an existing user in the database. Every user created in the database has their own default tablespace, which is Read more…
The article “Query for Oracle User Password Status” allows you to view the password status of an account in the Oracle database. When an Oracle account is created, if a specific profile is omitted from the CREATE command, Oracle assigns the DEFAULT PROFILE to the created user. The DEFAULT PROFILE Read more…