Database Reference
In-Depth Information
CONNECT SYSTEM/password@OLTP;
Now we give the MINIDBA role three system privileges that you wish
to delegate to an assistant DBA.
GRANT CREATE USER, CREATE SESSION, CREATE ROLE
TO MINIDBA;
Connect to the MUSIC user to grant some object privileges to the other
role.
CONNECT MUSIC/MUSIC@OLTP;
Let's say that you are the designer for the MUSIC schema's application
and you know that all users need to be able to change and query some
tables and only query other tables.
GRANT SELECT ON ARTIST TO MUSIC_ACCESS;
GRANT SELECT ON SONG TO MUSIC_ACCESS;
GRANT SELECT ON MUSICCD TO MUSIC_ACCESS;
GRANT SELECT, INSERT, UPDATE, DELETE
ON STUDIOTIME TO MUSIC_ACCESS;
GRANT SELECT, INSERT, UPDATE, DELETE
ON GUESTAPPEARANCE TO MUSIC_ACCESS;
Now that roles are configured, we should now grant the roles to users.
Granting a role to a user uses the same syntax as granting a system privilege.
Refer to Figures 23.5 and 23.9 again. Notice that you can grant a system
privilege, a role, or ALL PRIVILEGES. A role can even be granted to
another role! This can be useful when you have subsets of privileges that can
be logically grouped together under a single role.
So we have added privileges to both roles and now wish to grant roles to
users. The MUSIC user did not create any roles and does not have the
GRANT ANY ROLE system privilege. We have to connect to SYSTEM
again.
CONNECT SYSTEM/password@OLTP;
