In: Computer Science
For the following commands you must be logged in as user “system”. You will need to do some research on the commands CREATE USER; GRANT CREATE SESSION; GRANT CREATE…..; GRANT ALTER …., GRANT SELECT….; REVOKE ……; and EXECUTE …..
5. Create two database users: The first is a concatenation of your first and last name (e.g. johndoe). The second is a concatenation of your instructors first and last name (e.g. sallysmith)
6. Assign the two users privileges to connect to the database.
7. Assign the user with your first and last name the privilege to select data from the employees table.
8. Assign the user with your instructors first and last name all privileges to the Departments table.
9. Assign the user with your first and last name the privilege to execute any procedure.
10. Take away the instructors privilege to execute any SQL commands on the Departments table
5. Create User command is:
CREATE USER '<user-name>'@'<sql-server-address>' IDENTIFIED BY '<password>'
Example: Assuming SQL server is installed on your own computer i.e. local host otherwise use server IP address.
CREATE USER 'johndoe'@'localhost' IDENTIFIED BY 'abc1234'
CREATE USER 'sallysmith'@'localhost' IDENTIFIED BY 'xyz4321'
6.
GRANT CONNECT ON DATABASE <db_name> TO user;
Example: (You must knows the database name. This you can done by typing select db_name(). Exact command may vary depending upon the database you are using.)
GRANT CONNECT ON DATABASE test TO johndoe;
GRANT CONNECT ON DATABASE test TO sallysmith;
7.
GRANT SELECT ON TABLE <table-name> TO <user-name>
Example:
GRANT SELECT ON TABLE employees TO johndoe
8.
GRANT ALL PRIVILEGES ON TABLE <table-name> TO <user-name>
Example:
GRANT ALL PRIVILEGES ON TABLE Departments TO sallysmith
9.
GRANT EXECUTE ON PROCEDURE *.* TO <user-name>
Example:
GRANT EXECUTE ON PROCEDURE *.* TO johndoe
10.
REVOKE ALL PRIVILEGES ON TABLE <table-name> FROM <user-name>
Example:
REVOKE ALL PRIVILEGES ON TABLE Departments FROM sallysmith