When my postgres function is tries to create a new sequence, it returns a permission denied error. It looks like the only way to make it work is to give Create permission on schema using the below statement. GRANT CREATE ON SCHEMA public to "myuser" But, this will allow the user to create any other type of object as well, including tables. How can we control this? I want my user to be able to create a sequence, but not tables. Note: Create is not a valid grant on Sequences.

I wonder what the result of the following grant/revokes is according to the SQL standard. Suppose A created a table tab and there are four users (i.e., A, B, C and D). The SQL commands are: grant select on tab to B with grant option; (by A) grant select on tab to C with grant option; (by B) grant select on tab to B with grant option; (by C) grant select on tab to D with grant option; (by A) grant select on tab to C with grant option; (by D) revoke select on tab from B; (by A) In Posgresql, it complains that it is not allowed to grant a privilege to grantor. In MySQL, it is allowed and the privilege of B is revoked although there is a path system->A->D->C->B in the authorization graph. Therefore, I wonder what the standard say about this situation. Does it define how these commands are handled?

**Scenario** I would now like to implement a permission system so that a user is able to restrict access on his contact properties to his connections, e.g.: - A wants to allow B to access his name, birthday and work mail; - all other properties are private to B; and - A wants to allow C to access his name, phone number and private mail, etc. **Current considerations** I have a database scheme as shown in the picture below for an app that manages contact information for users: The red table people contains basic information of the user, the yellow tables are arrays so that every user can have multiple email addresses, phone numbers etc. You may expose your contact data to other people by connecting to them as managed by the connection table. **The question** So my question now is, how can I design an efficient permission system that achieves the described goal?

The account used in a linked external SQL Server was disabled, by the external server, temporaly. After the restoration of access, various job routines that used this account continued working, as normal, except one. I'm getting the following error message when the job routine is executed:

Executed as user: \svc_aservice. 
Login failed for user ''. Reason: The account is disabled. [SQLSTATE 42000] (Error 18470).

However, when executed manually, it works perfectly:

The job succeeded.  The Job was invoked by User .  
The last step to run was step 1 ().

Solutions I've tryied so far are: - Creating a new job, inserting the same step:

[].[];

- Changing the name of the old job And also, searching up the error message, but it's all related to the logon still being disabled - which is not the case - since it works when executed manually and other jobs continue to run without any errors. Edit: 1. The SP, executed by the job, is the following:

DECLARE @STATUS INT
    SET @STATUS = ( 
        SELECT COUNT(1) 
        FROM [].[database].[dbo].[table] 
        WITH (nolock)
        WHERE 
        )
    IF ( @STATUS > 1 )
    BEGIN
        TRUNCATE TABLE [dbo].[table]

        INSERT INTO relatorios.dbo.media_du_gerals (
            
            )  
        SELECT 
        FROM
        [LinkedServerName].[database].[dbo].[table] 
        WITH (NOLOCK)
        WHERE 
    END

2. The linked server configuration don't have mapped users. Connection is made via **hostname.** I will be checking if connection source via hostname, over IP adress, can be the cause of the problem. If it is the case, I will post an update.

I can create a database using a specific role for my "user1". I have tested and a user "test1" can not create a database in the mode authorization enabled. How works the creation of the database? I created a user "user1" and it can access this database "db1" only. Can I have the same user "user1" with privileges to create a new database as exists in SQL Server? I might be (completeley) wrong, but I would like an explanation concrete about this case. Thank you for any help and instruction. :)

I'm developing an analytics application that provides multiple levels of visualization of a data warehouse in a Postgres DB. One of its requirements is that different users should have different levels of access to the data. For example, some users should only extract metrics (counts, avgs, sums) from a particular table, while others could drill down the data to the level of columns. Example:

employee table

 id    | name   | salary
-------+--------+--------+
     1 | josé   |  20000 
     2 | joão   |  80000 
     3 | tiago  |  60000 

user 1 (can drill down)
------
=> select name from employee where id = 1 
=> josé

user 2 (can read only aggregate data)
------
=> select avg(salary) from employee
=> 53333.3333333
=> select name from employee where id = 1 
=> ERROR

I don't think that any RDBMS would provide that natively. But I wondered if there is any tool that could help me accomplish this authorization level without having to hard code it at the application level.

I'm reading *Database System Concept*, in a chapter about **authorization**, the book first says that: > A user who creates a view does not necessarily receive all privileges > on that view. She receives only those privileges that provide no > additional authorization beyond those that she already had." but in a section immediately follows it, it says that: >The creator of an object (relation/view/role) holds all privileges on the object, including the privilege to grant privileges to others. I don't know whether the creator of a view must holds all privileges on the view or not, because from first section I blockquoted, I think we **don't necessarily receive all privileges** on a view which we create, but from second section, it indicates the creator indeed **holds all privileges** on the view.

I'm very new into db development and currently working on my first production app. I learned that I would need a business logic layer (BLL) to authenticate and authorize users, for example: John can only query the database while Andrew can insert new records. Following are my questions that required clarification: - Does it mean the BLL would have to connect to the database with greatest privilege necessary, instead of least privilege needed for each user? - Will the BLL need INSERT permission to provide service to Andrew, which is more than enough for John? - Can we solve this potential flaw (except by securing BLL better, which I would of course do)? For example, implement authorization in database layer (as described here)?

Is it possible to grant an AD group in PG 13 without sorting to creating the group in the database? Similar to how AD groups can be granted directly in SQL Server. The PG server is on a Windows server that is already joined to the AD domain. Note: I asking about authorization and not authentication against AD.

I am looking for a solution to the problem described below. There are two DB2 instances with two authorization IDs on the same server and can be accessed using the same user User1. **Example:** To access Inst1DB, I use the following commands: [User1 ~]$ . /db2/authID1db2/home/authID1db2/sqllib/db2profile [User1 ~]$ db2 connect to Inst1DB To access Inst2DB, run the following commands: [User1 ~]$ . /db2/auth2db2/home/auth2db2/sqllib/db2profile [User1 ~]$ db2 connect to Inst2DB Note that one needs to terminate the current DB2 session before connecting to another DB instance. To terminate DB session, run the following command: [User1 ~]$ db2 terminate **Issue:** I want to join two tables, one of which belongs to INST1DB and the other to INST2DB, while connected to INST1DB. [User1 ~]$ . /db2/authID1db2/home/authID1db2/sqllib/db2profile [User1 ~]$ db2 connect to Inst1DB [User1 ~]$ db2 "Select * from Inst1DB.schema1.table1 T1 , Inst2DB.schema2.table2 T2 where T1.column1 = T2.column1. Curerntly I am getting below error: >SQL0204N "Inst2DB.schema2.table2" is an undefined name. SQLSTATE=42704. **Query:** Can anyone suggest to me how to connect the two instances and perform a join on these tables?

I have a stored procedure that runs fine when executed manually but fails in scheduled job. The error I receive is: >Executed as user: \USER-a. Login failed for user '\USER-a'. [SQLSTATE 28000] (Error 18456). The step failed. My other stored procedures runs fine when scheduled. The only stored procedures that fails are the ones that are written in this form. SET @QUERY = 'SELECT * from abc' EXEC(@QUERY)

I'm getting the below errors while installing SQL Server on a Windows machine. I'm using my admin account which has permissions (I’m in Administrations group. Verified here -> Computer management -> Groups) Does it ring any bells based on the below errors to anyone? Are there any known workarounds. Spent a day without any good solutions. This is a fresh installation btw. The worst part is I can't even uninstall it as it complains about the same unauthorized error.

Following the the info in posts like [these](https://dba.stackexchange.com/questions/25357/choice-of-authentication-approach-for-financial-app-on-postgresql) i would like to use the role system and policies, i.e. each user in my system would have a db role. I want to model the following properties but i can't come up with a role hierarchy. Consider the following tables:

companies(id, name)
users(id, name)
projects(id, name, company_id)
users_companies(user_id, company_id, type)
users_projects(user_id, project_id)

each user can have a different type within a company (admin/employee/customer) and depending on that the policy should change. So when a user is authenticated, we know his user_id and company_id Those can be provided id needed in policies with things like

set local user_vars.user_id = 10
set local user_vars.company_id = 20

What would be the roles and policies needed in order to get this - you can see only your company row in the companies table - admins can see all the projects for their company - employees can see only the projects they are assigned to - no one can see/alter entries in users_companies (that is done somehow on signup and with triggers) - admins can see the users_projects entries but only related to the users and project in their company - employees can see users_projects entries only for projects that they are assigned to - admins can see all the users in their company - employees can see the users in their company that participate in the same projects as they do I am looking for a base case that i can build on. My main problem is the fact that a user can have different roles/types depending on the current company

I have a doubt about superusers and single-user mode. I want to restrict the access to a certain database, in a way that only those users which I gave privileges can see the structure of my database. To achieve that, I had to erase the superuser, since it bypasses all permission checks. But then I found that if I have access to the computer, I can enter in "single-user mode" and grant superuser privileges or even creating new users. ### My question Can I disallow permanently the use of superusers and single-user mode?

I tried to follow this official documentation to install unix_socket plugin using mysql_plugin utility but getting an error that > Cannot read plugin config file unix_socket. Bad format in plugin > configuration file. FROM alpine:edge RUN set -ex \ && apk add mariadb mariadb-client \ && mysql_install_db --user=mysql --skip-name-resolve \ && echo 'auth_socket' >> /usr/lib/mariadb/plugin/unix_socket.ini \ && mysql_plugin --basedir=/usr --datadir=/var/lib/mysql --plugin_dir=/usr/lib/mariadb/plugin unix_socket ENABLE CMD ["mysqld"] I cross checked the ini file. cat /usr/lib/mariadb/plugin/unix_socket.ini auth_socket

I am trying to run a command that looks like this: Create ASSEMBLY [AssemblyName] AUTHORIZATION [dbo] From ... When this runs, it returns an error message: Could not obtain information about Windows NT group/user 'oldMachineName\username', error code 0x534. In this case oldMachineName is the old machine name for my computer. I changed the computer's machine name to newMachineName before every installing Sql Server, but there are obviously some vestiges of it hanging around. When I log into Sql Server using SSMS, it correctly lists the logged-in user as newMachineName\username. Yet when running this command with Authorization dbo it is trying to use oldMachineName. So, how can I convince Sql Server that oldMachineName doesn't exist anymore and teach it the correct group/user of the current dbo (ie: my logged in user)?

Is there a way to prevent a login from listing the tables and columns in a schema? I have to give access to a remote login to query on a single view; however, I also must make sure that such login cannot list every object on that schema. Is there something like MS SQL SERVER's DENY VIEW ANY DEFINITION TO public; on PostgreSQL?

I want to extract the structure of a 9.7 DB2 database to use to clone it but some of the authorization statements are missing afterwards: I tried something like this: db2look -d dbName -a -x -o db2look.sql what I noticed, is that the *Authorization Statements on User Defined Functions* and *Stored Procedures* are missing. I expected to see something like this: https://www-01.ibm.com/support/docview.wss?uid=swg1IC65658 EDIT: A try indicated, that this command correctly extracted the GRANTstatements on the productive system.

I will to made a database diagram in my SQL Server Management Studio. But I have this warning that said: > Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects. Can you help me whit this error? How can I give myself (hein_) the promision to continue? @JeffersonBElias suggested me to execute this code: select suser_sname(owner_sid) as usersname from sys.databases where name = 'FreeTimeDBSSA' What me gives this: +-----+-----------------------+ | | usersname | +-----+-----------------------+ | 1 | DESKTOP-3BMCMPC\hein_ | +-----+-----------------------+ Here you have my Object Explorer:

Is it possible to create a schema that is owned or accessible by more than one user in the same database? I was reading postgres create schema documentation and postgres alter schema documentation and there is no syntax to provide more than a single user. I was also googling for such a solution, either in simple or hard way and found nothing, not even a single point to any workaround. Could you, please, point me to a solution or possible ways to workaround that, or make me certain, that such a thing is not possible in any way?