8. CuatroCuadras (Roles).ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "SQL",
            "display_name": "SQL",
            "language": "sql"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "# Cuatro Cuadras #\r\n",
                "## ROLES ##"
            ],
            "metadata": {
                "azdata_cell_guid": "495784ab-f922-4253-9e25-00a13ae41c79"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE CuatroCuadras\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "dadd3a96-d380-4c37-b0ed-d845043e9ba7"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.013"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 1
        },
        {
            "cell_type": "code",
            "source": [
                "CREATE ROLE Administrador\r\n",
                "GO\r\n",
                "\r\n",
                "CREATE ROLE Usuario\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "fcac3fb8-77c7-4788-b11b-b92b816e5ecb"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.316"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 2
        },
        {
            "cell_type": "markdown",
            "source": [
                "## USUARIOS Y LOGIN ##"
            ],
            "metadata": {
                "azdata_cell_guid": "cb225283-5f0d-4a90-a2f3-6af94755def8"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "CREATE LOGIN Admin1 WITH PASSWORD = 'A123'\r\n",
                "GO\r\n",
                "\r\n",
                "CREATE LOGIN User1 WITH PASSWORD = 'U123'\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "60541803-8349-40d5-9f0d-0194012d632c"
            },
            "outputs": [
                {
                    "output_type": "error",
                    "evalue": "Msg 15025, Level 16, State 1, Line 1\r\nThe server principal 'Admin1' already exists.",
                    "ename": "",
                    "traceback": []
                },
                {
                    "output_type": "error",
                    "evalue": "Msg 15025, Level 16, State 1, Line 4\r\nThe server principal 'User1' already exists.",
                    "ename": "",
                    "traceback": []
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.044"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 4
        },
        {
            "cell_type": "code",
            "source": [
                "CREATE USER Administrador1 FOR LOGIN Admin1\n",
                "GO\n",
                "\n",
                "CREATE USER Usuario1 FOR LOGIN User1\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "488772d3-c0a0-4c75-b67f-81ef8ceb0707"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:02.462"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 5
        },
        {
            "cell_type": "code",
            "source": [
                "ALTER ROLE Administrador ADD MEMBER Administrador1\r\n",
                "GO\r\n",
                "\r\n",
                "ALTER ROLE Usuario ADD MEMBER Usuario1\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "882942ba-0211-426c-ab80-8076074e414d"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.094"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 6
        },
        {
            "cell_type": "markdown",
            "source": [
                "## AREAS ##"
            ],
            "metadata": {
                "azdata_cell_guid": "70eb2931-3048-4c60-9999-0d2f57520a90"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "CREATE SCHEMA General\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "622c19ee-5cb2-4adb-867d-917227eed628"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.099"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 7
        },
        {
            "cell_type": "code",
            "source": [
                "ALTER SCHEMA General TRANSFER dbo.DatosBasicosUsuario\r\n",
                "GO\r\n",
                "ALTER SCHEMA General TRANSFER dbo.DatosBasicosLugares\r\n",
                "GO\r\n",
                "ALTER SCHEMA General TRANSFER dbo.VisitasMensuales\r\n",
                "GO\r\n",
                "ALTER SCHEMA General TRANSFER dbo.PromediosLugaresDestacables\r\n",
                "GO\r\n",
                "ALTER SCHEMA General TRANSFER dbo.UsuariosPorCiudad\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "017ca226-f3f9-48e2-9285-ece275a2a653"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.182"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 8
        },
        {
            "cell_type": "markdown",
            "source": [
                "## PERMISOS ##"
            ],
            "metadata": {
                "azdata_cell_guid": "e7019ccf-193c-4434-973b-5c4a3f32d8b0"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "--Usuario\r\n",
                "Grant Select on Schema::General to Usuario\r\n",
                "Deny Insert, Delete, Update on Schema::General to Usuario\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "d1cf629c-f19f-4692-a1be-c8a90be3eeda"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.149"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 9
        },
        {
            "cell_type": "code",
            "source": [
                "--Administrador\r\n",
                "GRANT ALTER, CONTROL, DELETE, EXECUTE, INSERT, REFERENCES, SELECT, UPDATE, VIEW CHANGE TRACKING, VIEW DEFINITION ON Schema::dbo TO Administrador\r\n",
                "GO\r\n",
                "GRANT ALTER, CONTROL, DELETE, EXECUTE, INSERT, REFERENCES, SELECT, UPDATE, VIEW CHANGE TRACKING, VIEW DEFINITION ON Schema::General TO Administrador\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "c8d9aec6-4364-4360-987e-46573d86aa47"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.020"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 10
        }
    ]
}