Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
37 Cards in this Set
- Front
- Back
|
Create a user in the database |
CREATE USER user_name IDENTIFIED BY password[DEFAULT TABLESPACE default_tablespace][TEMPORARY TABLESPACE temporary_tablespace]; user_name is the name of the database user.password is the password for the database user default_tablespace is the default tablespace where database objects are stored. Ifyou omit a default tablespace, the default SYSTEM tablespace, which always exists in adatabase, is used temporary_tablespace is the default tablespace where temporary objects are stored.These objects include temporary tables that you’ll learn about in the next chapter. If youomit a temporary tablespace, the default SYSTEM tablespace is used SQL> CREATE USER henry IDENTIFIED BY hoorayDEFAULT TABLESPACE usersTEMPORARY TABLESPACE temp; |
|
|
Change a user's password |
ALTER USER user_name IDENTIFIED BY new_password; You can also change the password for the user you’re currently logged in as using thePASSWORD command Example using ALTER USER: SQL> ALTER USER jason IDENTIFIED BY marcus; Example using PASSWORD: SQL> CONNECT jason/marcus SQL> PASSWORD >>Changing password for JASON >> Old password: SQL> ****** >> New password: SQL> ****** >> Retype new password: SQL> ****** >> Password changed |
|
|
Delete a user or role |
DROP USER {user_name | role_name}; SQL> DROP USER jason; |
|
|
Grant a user or role a system privilege |
GRANT privilege_name TO {user_name | role_name}; SQL> GRANT CREATE SESSION TO jason; |
|
|
Grant a user or role access to connect to a database |
CREATE SESSION GRANT CREATE SESSION TO {user_name | role_name}; CREATE SESSION is a system privilege |
|
|
Grant a user or role access to create a sequence |
CREATE SEQUENCE GRANT CREATE SEQUENCE TO {user_name | role_name}; CREATE SEQUENCE is a system privilege A sequence is a series of numbers that aretypically used to automatically populate a primary keycolumn |
|
|
Grant a user or role access to create a table in the user’s schema |
CREATE TABLE GRANT CREATE TABLE TO {user_name | role_name}; CREATE TABLE is a system privilege |
|
|
Grant a user or role access to create a table in any schema |
CREATE ANY TABLE GRANT CREATE ANY TABLE TO {user_name | role_name}; CREATE ANY TABLE is a system privilege |
|
|
Grant a user or role access to drop a table from the user’s schema |
DROP TABLE GRANT DROP TABLE TO {user_name | role_name}; DROP TABLE is a system privilege |
|
|
Grant a user or role access to drop a table from any schema |
DROP ANY TABLE GRANT DROP ANY TABLE TO {user_name | role_name}; DROP ANY TABLE is a system privilege |
|
|
Grant a user or role access to create a stored procedure |
CREATE PROCEDURE GRANT CREATE PROCEDURE TO {user_name | role_name}; CREATE PROCEDURE is a system privilege |
|
|
Grant a user or role access to execute a procedure in any schema |
EXECUTE ANY PROCEDURE GRANT EXECUTE ANY PROCEDURE TO {user_name | role_name}; EXECUTE ANY PROCEDURE is a system privilege |
|
|
Grant a user or role access to create a user |
CREATE USER GRANT CREATE USER TO {user_name | role_name}; CREATE USER is a system privilege |
|
|
Grant a user or role access to drop a user |
DROP USER GRANT DROP USER TO {user_name | role_name}; DROP USER is a system privilege |
|
|
Grant a user or role access to create a view |
CREATE VIEW GRANT CREATE VIEW TO {user_name | role_name}; CREATE VIEW is a system privilege A view is a stored query that allows you toaccess multiple tables and columns. You may then querythe view as you would a table |
|
|
Grant a user or role access to create a synonym |
CREATE SYNONYM GRANT CREATE SYNONYM TO {user_name | role_name}; Synonyms allow you to query a table without specifying the schema that it is in |
|
|
Grant a user or role access to create a public synonym |
CREATE PUBLIC SYNONYM GRANT CREATE PUBLIC SYNONYM TO {user_name | role_name}; A public synonym is a synonym which can be seen by all users |
|
|
Is any user able to query a table that has a public synonym? |
No, a user must have object privilege(s) for the underlying tables to be able to query or perform any actions with a table via its synonyms |
|
|
Grant a user or role privilege to grant a privilege to another user |
WITH ADMIN OPTION GRANT privilege_name TO {user_name | role_name} WITH ADMIN OPTION |
|
|
How do you check the system privileges granted to the current user? |
Table user_sys_privs user_sys_privs fields: username - name of the current user privilege - the system privilege the user has admin_option - whether the user is able to grant the privilege toanother user SQL> CONNECT gail/seymour SQL> SELECT *FROM user_sys_privsORDER BY privilege; >> USERNAME PRIVILEGE ADM -------------------- ---------------------------------- --- GAIL CREATE SESSION NO GAIL EXECUTE ANY PROCEDURE NO PUBLIC EXECUTE ANY PROCEDURE NO |
|
|
Revoke a system privilage from a user or role |
REVOKE privilege_name FROM {user_name | role_name} SQL> CONNECT system/manager SQL> REVOKE CREATE TABLE FROM steve; |
|
|
When you revoke a system privilege from a user that had WITH ADMIN OPTION and has granted that privilege to other users do those users lose the privilege as well? |
No, revoking privileges does not cascade. |
|
|
Grant an object privilege to a user or role |
GRANT privilege_name [columns] ON object_name TO {user_name | role_name} columns is a comma separated list of columns to grant the privilege to SQL> GRANT SELECT, INSERT, UPDATE (last_name, salary) ON store.employees TO steve |
|
|
List the 5 most common object privileges |
SELECT, INSERT, UPDATE, DELETE, EXECUTE (stored procedure) |
|
|
How do you check the object privileges a user has granted to other users? |
The table user_tab_privs_made and the table user_col_privs_made user_tab_privs_made fields: grantee - user to whom the privilege was granted table_name - name of the object (such as a table) onwhich the privilege was granted grantor - user who granted the privilege privilege - privilege on the object grantable - whether the grantee can grant the privilegeto another (YES or NO) hierarchy - whether the privilege forms part of ahierarchy (YES or NO) SQL> SELECT *FROM user_tab_privs_madeWHERE table_name = 'PRODUCTS'; >> GRANTEE TABLE_NAME ---------------------- ------------------------------ GRANTOR PRIVILEGE GRA HIE ---------------------- ---------------------------- --- --- STEVE PRODUCTS STORE INSERT NO NO STEVE PRODUCTS STORE SELECT NO NO STEVE PRODUCTS STORE UPDATE NO NO user_col_privs_made fields: grantee - user to whom the privilege was granted table_name - name of the object on which the privilege was granted column_name - name of the object on which the privilege was granted grantor - user who granted the privilege privilege - privilege on the object grantable - whether the grantee can grant the privilege toanother (YES or NO) SQL> SELECT *FROM user_col_privs_made; >> GRANTEE TABLE_NAME ------------------------------ ------------- COLUMN_NAME GRANTOR ------------------------------ ------------- PRIVILEGE GRA ---------------------------------------- --- STEVE EMPLOYEES LAST_NAME STORE UPDATE NO STEVE EMPLOYEES SALARY STORE UPDATE NO |
|
|
How do you check the object privileges a user has been granted? |
The table user_tab_privs_recd and the table user_col_privs_recd user_tab_privs_recd fields: owner - user who owns the object table_name - name of the object on which the privilegewas granted grantor - user who granted the privilege privilege - privilege on the object grantable - whether the grantee can grant the privilegeto another (YES or NO) hierarchy - whether the privilege forms part of ahierarchy (YES or NO) SQL> SELECT *FROM user_tab_privs_recdORDER BY privilege; >> OWNER TABLE_NAME ------------------------------ ------------------------------ GRANTOR PRIVILEGE GRA HIE ------------------------------ ---------------------------------------- --- --- STORE PRODUCTS STORE INSERT NO NO STORE CUSTOMERS STORE SELECT YES NO STORE EMPLOYEES STORE SELECT NO NO STORE PRODUCTS STORE SELECT NO NO STORE PRODUCTS STORE UPDATE NO NO user_col_privs_recd fields: owner - user who owns the object table_name - name of the table on which the privilegewas granted column_name - name of the column on which the privilegewas granted grantor - user who granted the privilege privilege - privilege on the object grantable - whether the grantee can grant the privilegeto another (YES or NO) SQL> SELECT *FROM user_col_privs_recd; >> OWNER TABLE_NAME ------------------------------ ------------- COLUMN_NAME GRANTOR ------------------------------ ------------- PRIVILEGE GRA ---------------------------------------- --- STORE EMPLOYEES LAST_NAME STORE UPDATE NO STORE EMPLOYEES SALARY STORE UPDATE NO |
|
|
How do you revoke object privileges from a user or role? |
REVOKE privilege_name ON object_name FROM {user_name | role_name} SQL> CONNECT system/manager SQL> REVOKE INSERT ON store.products FROM steve; |
|
|
Grant a user the privilege to create roles |
GRANT CREATE ROLE TO {user_name | role_name}; SQL> CONNECT system/manager SQL> GRANT CREATE ROLE TO store; |
|
|
Create a role |
CREATE ROLE role_name; SQL> CONNECT store/store_password SQL> CREATE ROLE product_manager; |
|
|
Grant a role to a user |
GRANT role_name TO user_name; SQL> GRANT overall_manager TO steve; |
|
|
How do you check the roles granted to a user? |
Table user_role_privs username - name of the user to whom the role hasbeen granted granted_role - name of the role granted to the user admin_option - whether the user is able to grant the roleto another user or role (YES or NO) default_role - whether the role is enabled by defaultwhen the user connects to the database(YES or NO) os_granted - whether the role was granted by theoperating system (YES or NO) SQL> CONNECT steve/button SQL> SELECT *FROM user_role_privs; >> USERNAME GRANTED_ROLE ADM DEF OS_ ------------------ -------------------------- --- --- --- STEVE OVERALL_MANAGER NO YES NO |
|
|
How do you check the privileges granted to a role? |
Tables role_sys_privs and role_tab_privs role_sys_privs fields: role - name of the role privilege - system privilege granted to the role admin_option - whether the privilege was granted with theADMIN option (YES or NO) role_tab_privs fields: role - user to whom the privilege was granted owner - user who owns the object table_name - name of the object on which the privilege was granted column_name - name of the column (if applicable) privilege - privilege on the object grantable - whether the privilege was granted with the GRANToption (YES or NO) |
|
|
Are a user's roles enabled by default when a user connects? How do you enable/disable the role from being enabled/disabled by default? |
A user's roles are enabled by default. You can ALTER the USER so that the role A role that is enabled by default is called a "DEFAULT ROLE". Enable all roles to be default except for specific ones via the following syntax: ALTER USER user_name DEFAULT ROLE ALL EXCEPT role_name; SQL> CONNECT system/manager SQL> ALTER USER steve DEFAULT ROLE ALL EXCEPT overall_manager; |
|
|
How do you enable a disabled role once you have connected? |
SET ROLE role_name IDENTIFIED BY role_password; You can also enable all roles via: SET ROLE ALL [EXCEPT role_to_not_enable]; SQL> CONNECT steve/button SQL> SET ROLE overall_manager IDENTIFIED BY manager_password; |
|
|
How do you revoke a role from a user or role? |
REVOKE role_name FROM {user_name | role_name}; SQL> CONNECT store/store_password SQL> REVOKE overall_manager FROM steve; |
|
|
How do you revoke a privilege from a role? |
REVOKE privilege_name ON object_name FROM role_name; You can also revoke all privileges on an object from a role: REVOKE ALL ON object_name FROM role_name; SQL> REVOKE ALL ON products FROM product_manager; |
|
|
How do you drop a role? |
DROP ROLE role_name; SQL> DROP ROLE overall_manager; |
