Please enable JavaScript.
Coggle requires JavaScript to display documents.
System Privileges, ADMIN OPTION, PUBLIC, ANY, ALL PRIVILEGES, REVOKE -…
System Privileges
System privileges are the right to perform some task in the database. For example, tolog in to the database, a user account is granted the system privilege CREATE SESSION.
As an analogy, consider the concept of a driver’s license. A driver’s license is sortof like a system privilege; it’s the right to drive a car in a general sense. Once you have a driver’s license, if you get a car, you can drive it. But you don’t have the right todrive anyone’s car in particular unless the owner specifically authorizes you to do so.
Note that you grant TO and you revoke FROM.
Once a system privilege is revoked from a user, the effect is immediate. However,any actions taken prior to the revocation stand.
In other words, if a user account hasbeen granted the system privilege CREATE TABLE and then creates some tables bu then has the CREATE TABLE system privilege revoked, the created tables already inexistence remain in place. They do not disappear. But the owning user may not create additional tables while the CREATE TABLE system privilege is revoked.
ADMIN OPTION
When any system privilege is granted with the WITH ADMIN OPTION option,then the recipient receives the system privilege itself, along with the right to grant the system privilege to another user
The REVOKE statement does not use the WITH ADMIN OPTION clause.Whenever a system privilege is revoked, the entire system privilege is revoked.
In other words, the REVOKE statement for system privileges does not “cascade.” It applies only to the user to whom the revocation is applied
PUBLIC
The PUBLIC account is a built-in user account in the Oracle database that representsall users. Any objects owned by PUBLIC are treated as though they are owned by all the users in the database, present and future.
This statement grants the CREATE ANY TABLE privilege to every user in thedatabase. The CREATE ANY TABLE privilege gives every user the ability to create any table in any other user account
Note that if you want to grant all privileges, you use the keywords ALLPRIVILEGES. But if you want to grant certain privileges to all users, you do not use the keyword ALL. Instead, you grant to PUBLIC
ANY
Some system privileges include the keyword ANY in the title. For example, there is asystem privilege CREATE ANY TABLE, which is the ability to create a table in any user account anywhere in the database
When a system privilege includes the keyword ANY in its title, it means that theprivilege will authorize a user to perform the task as though they were any use account.
ALL PRIVILEGES
As an alternative to granting specific system privileges, a qualified user account, suchas SYSTEM or some other DBA qualified account, can issue the following statement:
GRANT ALL PRIVILEGES TO user;
REVOKE ALL PRIVILEGES FROM user;
This statement will reverse all system privileges granted to the user, assuming thatall system privileges have been granted to the user. If not, an error message will result
REVOKE
If user LISA Revokes privileges from HENRY, then HENRY and HAROLD lose all privileges, as does anyone to whom they extended privileges with their WITH GRANT OPTION option. In other words, the revocation of object privileges “cascades.” Note that this isdifferent from system privilege revocation, which does not cascade, as we stated earlier.
REVOKE statement does not require the WITH GRANT OPTIONclause. REVOKE doesn’t care whether that option had been included; it just revokes all specified privileges and cascades the change throughout all user accounts asrequired.