Questions tagged [permissions]
Privileges granted to an account or role through the security mechanism of an operating system, database manager or other system.
1,317
questions
0
votes
1answer
18 views
Cannot drop the user because it does not exist or you do not have permission
I am getting this message for some users when executing sp_revokedbaccess procedure for database users. Users I am trying to remove are selected from sys.database_principals with type = S, W or G (sql ...
0
votes
0answers
6 views
Non-Super user permissions required to allow a user to create a schema within a database that is owned newly created user
I'm able to run the following as postgres or any other user which has SUPERUSER ("dba1" has SUPERUSER in this case)
psql -U dba1 dev << SQL
create user udev1;
create schema udev1 authorization ...
0
votes
1answer
13 views
What permissions are required for complex cross-schema materialized view in Oracle?
I have a complex cross-schema query that I'd like to create as a materialized view in Oracle 19c (although the database has been ported forward from numerous historical versions of Oracle). This ...
0
votes
2answers
31 views
Permission to select sys tables in stored procedure
I have implemented the stored procedure to check if a specific [schema].[procedure] exists.
CREATE PROCEDURE [BlobCheckExisting].[usp_DoesStoredProcedureExist]
@schemaName varchar(128),
@...
0
votes
1answer
22 views
Analyst MSDB Access SQL Server 2017
i'm an accidental DBA for a company and having trouble figuring out this permissions situation.
We have a new 2017 SQL server that I would like to get our report writer / analysts access to a few of ...
0
votes
1answer
18 views
Alter default privileges in postgres for other roles
Let's say I have a role called user1, user2 and postgres.
Is it possible to alter the default privileges while logged in as postgres for objects created by user1 so that user2 can see them?
This is ...
0
votes
0answers
31 views
Granting privileges on databases using wildcards
I'm using SQL Server and there are multiple databases on the instance. I'm wondering if there is any possibility to set up access rights with wildcards.
I would like a team to be able to create/drop/...
1
vote
2answers
59 views
How to grant permissions to allow anything inside a SQL Server schema?
I have a schema called training. I have a new role called training_modify. Here are the desired permissions for users placed in that role:
Select, update, insert, and delete permissions on any object ...
1
vote
1answer
31 views
SQL Server Grant CRUD to Users on One Schema
I have a schema called training. I want users who will be trained to have CRUD permissions on any object on that schema. I do not want them to have any other permissions on any other object outside ...
2
votes
1answer
52 views
Microsoft SQL Server - Permission to create a Snapshot
I've developed a set of SSIS packages to load a data warehouse.
In order to manage any issue that may arise during package execution, each package starts by creating a snapshot of the data warehouse. ...
9
votes
2answers
559 views
Break the link between a Database User and SQL Server Login
When restoring database from once instance to another one sometimes has to relink SQL Server Logins to the Database User.
This is normally achieved with one of the following procedures.
Deprecated ...
0
votes
2answers
46 views
Deny certain user from updating certain rows [duplicate]
Say I have a table like this:
canModify | name | age
------------------------
1 | John | 24
0 | Nick | 21
I want to grant update access to a specific user only where canModify=1
I ...
0
votes
0answers
40 views
Permission denied to create new tablespace for PostgreSQL data
I can't set my PostgreSQL data folder to a drive other than the C drive (Windows 8 machine).
1) If I try to do so when installing PostgreSQL, the installation fails. Therefore I need to choose the C ...
1
vote
1answer
21 views
Postgres role does not inherit permissions of superuser role via membership in that role
In the snippet below, the role writer is a member of a superuser role named admin. The admin role can insert rows into a table but the writer role cannot insert rows into the same table. Why does the ...
0
votes
1answer
45 views
relation “table” does not exist
i created the following schema , inside that schema i created a table and inserted a some values.
After that i created a function that get a value in the table based on an ID.
Here is all the ...
1
vote
1answer
32 views
Allows to select on view, while hiding its definition
I need to create a user in PostgreSQL with the least privileges possible. So, the user would only be allowed to do queries on specific tables and views. That user should not be allowed to get any ...
0
votes
0answers
9 views
Adding an additional permission to my SQL user on every database they already have some permissions
Our corporate hosting server has had restricted SQL DB permissions for a very long time, but one of our end-user systems has started requiring a new DB permission, INDEX, which none of them have. I ...
0
votes
1answer
15 views
How to list all functions with arguments that a role is allowed to execute in Postgres?
I found how to list tables a role has permissions on. I found how to list all functions with arguments.
My question is: How to list all functions with arguments a role is allowed to execute?
1
vote
1answer
16 views
Grant privileges to all users on a single table
Is it possible to grant privileges (such as SELECT, INSERT, UPDATE, etc) to all the users for a specific table at the same time?
Here is what I tried but it didn't work out...
GRANT SELECT ON ...
2
votes
1answer
76 views
SQL Server - What is the difference between BULKADMIN and ADMINISTER BULK OPERATIONS
To grant user ability to run Bulk Insert T-SQL command, login/user needs to be granted:
BULKADMIN server role - or - ADMINISTER BULK OPERATIONS server-level permission
connect on target database
...
0
votes
0answers
20 views
Drop Privilege Issue
We are using mysql 5.7.22 and I have created a user with drop privilege because this user using temp tables and temp tables needs drop privilege if table already exist. Due to this privilege, user can ...
0
votes
0answers
31 views
PostgresSQL — Restrict listing access when using dbeaver or pgadmin
I have a schema in Postgres named -- public
When I login via dbeaver or pgadmin, I can go to database > schema > public > tables and it shows me the list of all tables present in public ...
0
votes
0answers
11 views
cannot create trigger without super privilege even though bin_log is off
I'm trying to import a magento 2 database dump to a MariaDB 10.1 server however it's asking me to provide SUPER privilege in order to finish the import.
It fails with the message
ERROR 1227 (42000) ...
0
votes
0answers
11 views
How to create a better Role-Based Access Control with customizable permissions?
I am trying to layout the tables I will need for my user authorization setup, but my case is a bit different than just assigning roles or permissions to a user. I currently have a companies table and ...
0
votes
1answer
26 views
Generate GRANT for all tables reference by a procedure
I have a couple of stored procedures in my DB in which each one select from a couple of tables. I need to construct a statement where I can give it a stored procedure and get the grant select ...
4
votes
1answer
427 views
How to prevent sysadmin server role from executing a stored procedure
I have a SQL Server default instance using SQL Server Authentication and two users, User1 and User2.
User1 has the public server role while User2 has the public server role + sysadmin server role.
...
0
votes
1answer
25 views
Read only privileges
I have created a user and want to grant the user read only privileges on all database schemas. Can I do that using a single grant statement for all 20 schemas.
0
votes
0answers
197 views
DBeaver: Access denied for user 'root'@'localhost' (using password: YES)
Context: I am creating a new Schema and looking to import .sql file generated from mysql dump. I have server running: MySQL 8.0.19.
I am using DBeaver client Version 7.0.2 and have setup a new MySQL ...
1
vote
1answer
43 views
Grant rights to create a table with an identity column on another schema
I have a user in charge of deploying DDL on other schemas than its own.
This user can create a table on those schemas, it can also create sequences on those schemas.
But when I try to create a table ...
2
votes
1answer
92 views
Grant Permissions to all databases using server role
I know this question has been asked many times but they are all 3+ years old so I'm going to try my luck by asking again.
I need to grant Read permissions to all databases on MSSQL server. I would ...
0
votes
1answer
28 views
Grant privileges to tables in all (dynamic) schemas
We've setup an AWS Database Migration Service which migrates schema and tables from multiple sources.
The rule for migrating from source can be a wildcard on schema, meaning a schema could 'pop up' ...
1
vote
1answer
42 views
Database model with Users, Resources, Permissions
I am fairly new to the DB design. I need to handle what a user can do on a specific resource.
One of the main queries I need to drive is:
Get all the cars a user has write permission to.
I think ...
0
votes
1answer
16 views
Error when assigning a role to user
I have created a role called "role1" with these steps :
postgres=# CREATE ROLE role1;
CREATE ROLE
postgres=# GRANT CONNECT ON DATABASE paybills TO role1;
GRANT
postgres=# \c paybills
You are now ...
1
vote
1answer
44 views
SQL Server List a Database's Users' Permissions
I have an SQL Server and it has 60+ databases. Each database has 3-10 users. I need to list database users' permissions for all databases. I can't find any clear solution on the internet. I need to ...
1
vote
1answer
26 views
How to grant SQL Agent proxy to a public role in MSSQL 2014
I want to grant SQL Agent proxy to a public role in msdb database in MSSQL 2014.
I have the following query and I want this to return me some valid output.
USE [msdb]
SELECT
sp.name AS proxyname
...
0
votes
1answer
10 views
No permissions to the certificate file when trying to restore a DB from a third party
I am trying to restore a Db in SQL server 16. It is a developer version installed on windows 10 laptop. We have a backup file from a SaaS instance. We have moved to a new instance of the SaaS and this ...
0
votes
0answers
58 views
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MariaDB server
My Users table crashed and I had to do a repair on it, now I'm getting the error:
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MariaDB server
No matter how I try to ...
0
votes
1answer
37 views
How to deny viewing definition of server level trigger?
I have several server level triggers, that I want users to deny viewing definition of. I control server level permissions, users aren't sysadmins etc.
I've tried 2 options:
DENY VIEW ANY DEFINITION ...
0
votes
0answers
7 views
Storing table(s) outside data-dir symbolic-links/tablespace
Because my only internal harddisk is running low on space, I decided that I want to store the tables for a specific database large-database on an external SSD drive. Trying to setup the configuration ...
0
votes
1answer
22 views
Postgresql: Permission denied to schema to user created based on master with rights
I'm trying to write a function that eventually will rotate users. Currently I have the code below which works and creates a new user using the prior user which ultimately has the same rights as the ...
-1
votes
1answer
9 views
Query Oracle Database On Who Has Create Database Link Privs (private)?
Anybody know a good way to query the entire Oracle database on what users have privs to make private database links? Thank you!!
0
votes
1answer
18 views
Can Postgres restrict usage of the SET and SHOW commands?
Is it possible to restrict a role from being able to set arbitrary session variables, or from being able to view them?
select current_setting('x');
show x;
Even after I've switched roles to a one ...
0
votes
0answers
16 views
Grant select on all the tables in HSQLDB
I want to grant select privilege to all the tables in my HSQLDB.
I have gone the HSQLDB documentation there I found to grant select on only one table.
How can I grant select on all the tables.
0
votes
2answers
45 views
Cannot login as new user on MariaDB
When I create a new user in MariaDB, I cannot login as that user.
First I login to the database as root doing mariadb in a bash terminal.
I then run the following commands to create a database and ...
0
votes
1answer
27 views
What permissions does the SQL Agent's user need to execute Powershell scripts?
I'm trying to create a job with a Powershell step. To recreate this:
create new job, give it a name
add a new job step, name it, type powershell, run as the SQL Agent, script: $test="test"
set the ...
0
votes
0answers
16 views
PostgreSQL trigger for user access on creation of new schema or table
I'm looking to create a trigger which will grant group access to both a schema or a table when created within a database.
Admittedly I might have this wrong and it may be done through a different ...
0
votes
2answers
38 views
Why can't I create a MySQL user even if i have 'all priviliges'?
I am trying to resolve issues created when my ISP moved my database from a shared server to dedicated cloud storage. I am reaching it remotely via SQLyog. They say they have given all privileges to ...
0
votes
0answers
18 views
Cannot delete from MariaDB federated table
In our web application, there are some federated tables which point to a remote server, it is used so that when writing log entries to the federated table they get actually stored on this remote ...
0
votes
1answer
35 views
Grant usage to a schema from another database
In PostgreSQL, how can I GRANT USAGE to a certain schema for a certain user in a certain database while I am in another database?
I am connected to a database (let's say database first) as a user ...
0
votes
1answer
15 views
Creator of a MySQL table automatically granted all table permissions?
I recently read it in SilberSchatz book that in MySQL the creator of a relation/view is the owner and granted all permissions on the relation/element including the power to transfer these grants to ...
